お世話になります。
別シートで合計値を出した数値を参照し、指定のセルにその数値を桁ごとに分解して表示させたいのですが、うまくいきません。おそらく、IF関数、ISERROR関数、MATCH関数あたりを組み合わせるのだろうと思いますが、教えていただけないでしょうか
目的は、金種内訳票を作りたいのですが、票の規格により、各桁ごとに個別の升目(セル)に入力しなくてはならないためです。
【基礎データとなるシート】
ROUNDDOWN関数を使って金種別の金額を出し、SUM関数を使って合計値を出しています。その合計欄を参照したいと思っています。
シート名-------------差込データ
(総合計について)
合計値のセル---------差込データ!の列Gの下のほうの行にありますが、最終行ではありません。同じ行の列Bに「合計」と項目見出しが入っています。
合計値の桁数---------4桁から8桁の間で毎回変動します。
(各金種について)
列Qの金種別合計額が入る行に、項目見出しとして「金種別合計額」と入っています。
1万円の金種別合計額------列R
5千円の金種別合計額------列S
1千円の金種別合計額------列T (※2千円は求めていません)
5百円の金種別合計額------列U
1百円の金種別合計額------列V
5十円の金種別合計額------列W
1十円の金種別合計額------列X
5円の金種別合計額--------列Y
1円の金種別合計額--------列Z
【差込先(表示させる)シート】
シート名-------------金種連絡票
各列の金額桁数
列Hは億の単位で、列Iは1千万円の単位です。順次、セルが右に移るごとに一桁下がります。
例えば、123,456,789円(1億2,345万6,789円)の場合
・HIJKLMNOP(列)
・123456789 と、各セルに表示させたいのです。
また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
例えば、56,789円(5万6,789円)の場合
・HIJKLMNOP(列)
・ 56789 このように、桁数に対応する各セルに表示させたいのです。
(総合計額を表示させるセル)
セル範囲 セルH9からセルP9までのセルに右詰で表示させたいのです。
1億円の桁がセルH9で、順次桁が下がり、1円の桁がP9です。
(各金種額表示させるセル)
1万円はセルH10からセルL10までが計算により表示させる範囲で、ゼロ下4桁はシートに入力しておきます。
小額の金種は、各一行下に同じように表示します。
※合計額の変動部分だけ計算により表示させたいです。
例えば、1万円の場合は、下のゼロ4つは計算でなく、シートにあらかじめ0を入れておきたいのです。これは、規定の様式でもあり、書体を変えて見やすくするという意味もあります。(金種の枚数ではなく、金額です)
1万円の金種別合計額
セルH10からセルL10までのセルに1万円単位の金額のみ右詰で表示させたいのです。下4桁(セルM10からセルP10までのセル)にはゼロが入っています。
5千円の金種別合計額
セルH11からセルM11までのセルに5千円単位の金額のみ右詰で表示させたいのです。下3桁(セルN11からセルP11までのセル)にはゼロが入っています。
1千円の金種別合計額
セルH13からセルM13までのセルに1千円単位の金額のみ右詰で表示させたいのです。下3桁(セルN13からセルP13までのセル)にはゼロが入っています。
(※本当は行12に2千円の項目があるのですが、2千円札は使わないので省略します)
5百円の金種別合計額
セルH14からセルN14までのセルに5百円単位の金額のみ右詰で表示させたいのです。下2桁(セルN14からセルP14までのセル)にはゼロが入っています。
1百円の金種別合計額
セルH15からセルN15までのセルに1百円単位の金額のみ右詰で表示させたいのです。下2桁(セルN15からセルP15までのセル)にはゼロが入っています。
5十円の金種別合計額
セルH16からセルO16までのセルに5十円単位の金額のみ右詰で表示させたいのです。下1桁(セルP16のセル)にはゼロが入っています。
1十円の金種別合計額
セルH17からセルO17までのセルに1十円単位の金額のみ右詰で表示させたいのです。下1桁(セルP17のセル)にはゼロが入っています。
5円の金種別合計額
セルH18からセルP18までのセルに5円単位の金額のみ右詰で表示させたいのです。
1円の金種別合計額
セルH19からセルP19までのセルに1円単位の金額のみ右詰で表示させたいのです。
すみません。もちろん、全部の桁について教えていただこうなどとは思っていません。総合計の欄の表示方法が分かれば、後は当てはめて出来ると思います。
念のため、これからやりたいことを列記させていただきました。
どうか、よろしくお願いします。
No.2ベストアンサー
- 回答日時:
>例えば、123,456,789円(1億2,345万6,789円)の場合
>・HIJKLMNOP(列)
>・123456789 と、各セルに表示させたいのです。
>
>また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
>例えば、56,789円(5万6,789円)の場合
>・HIJKLMNOP(列)
>・ 56789 このように、桁数に対応する各セルに表示させたいのです。
数値の入ったセルを元に、特定の1桁だけ抜き出す式
=(MID(RIGHT("_________"&数値の入ったセル,下何桁目),1,1))
(注:「_」は「半角の空白」の意味。実際に使う場合は「_」を「半角の空白」にする事)
これは「桁が無いなら空白」にする為「文字列」になる式。その為、標準の表示形式は左詰めになるので、セルの書式の「配置」で「横位置」を「右揃え」にしておく事。
なお、50000円の場合は
・HIJKLMNOP(列)
・ 50000
になる。「0の場合も空欄」にして
・HIJKLMNOP(列)
・ 5
としたい場合は、少し工夫が必要。
>1万円の金種別合計額
前述の式の「下何桁目」を順に「9~5」にすればOK。
>5千円の金種別合計額
>1千円の金種別合計額
>5百円の金種別合計額
>1百円の金種別合計額
>5十円の金種別合計額
>1十円の金種別合計額
>5円の金種別合計額
>1円の金種別合計額
これって「最適に両替されている」として計算して良いのか?
つまり「千の単位が5~9の時だけ、5千円札が1枚だけになる。千の単位が4~0の時は、5千円札が無い」と考えて良いのかどうか。
「最適に両替されている」と言う場合は、1円から順に
=MOD(合計金額,5) …1円
=QUOTIENT(MOD(合計金額,10),5)*5 …5円
=QUOTIENT(MOD(合計金額,50),10) …10円
=QUOTIENT(MOD(合計金額,100),50)*5 …50円
=QUOTIENT(MOD(合計金額,500),100) …100円
=QUOTIENT(MOD(合計金額,1000),500)*5 …500円
=QUOTIENT(MOD(合計金額,5000),1000) …1000円
=QUOTIENT(MOD(合計金額,10000),5000)*5 …5000円
で求まる。で、それぞれの結果は「0~4」か「5」しか有り得ない。つまり、セルは1つしか使わない筈。
あと「0枚なら空白」にしたいなら
=IF(MOD(合計金額,5)=0,"",MOD(合計金額,5)) …1円
=IF(MOD(合計金額,10)<5,"",QUOTIENT(MOD(合計金額,10),5)*5) …5円
=IF(MOD(合計金額,50)=0,"",QUOTIENT(MOD(合計金額,50),10)) …10円
=IF(MOD(合計金額,100)<50,"",QUOTIENT(MOD(合計金額,100),50)*5) …50円
など、IF文を使用する。
もし、千の桁とかが「6千円だけど、五千円札は無くて、千円札が6枚」とか「実は5千円札は何枚もあって、5枚あるなら、25000と表示」とか、そういうケースがある場合は
=(MID(RIGHT("_________"&(5千円札の枚数*5000),下何桁目),1,1))
の式で、最初と同じ方式で各セルを計算する必要があるし、表のどこかに「5千円札の枚数」が入力されてる必要がある。
この回答への補足
すごくスマートな式ですね。
例えば、合計額についてですが、
100万円の桁ならば、=(MID(RIGHT("_________"&差込データ!$G$152,7),1,1))
として表示させ、他の桁も思いどおりな結果を求めることができました。
大変ありがとうございました。
各金種についても、金種別の金額も算出しているので、これを当てはめ、
例えば1万円の金種の10万円の桁ならば、
=(MID(RIGHT("_________"&差込データ!$R$154,6),1,1))
として求めることができました。
大変スマートな式を覚えられて、大満足です。
ここで追加の質問で恐縮なのですが、
・シート名「差込データ」にある合計額は、その件数の増減により行が毎回変わるのですが、自動で判別させるにはどのようにしたらよいでしょうか?
MATCH関数でどうかと調べているのですが、どうにもうまく行きません。
スミマセンが、教えていただけないでしょうか。
よろしくお願いします。
No.4
- 回答日時:
合計金額ですのでその範囲内では最大値となっているという前提ですが。
単純に MAX関数ではだめでしょうか。
=MAX(合計欄を含めた合計を求めた範囲以上の範囲)
回答ありがとうございます。
MAX関数を調べたのですが、別シートを参照するところでつまづいているのでしょうか、うまく行きません。
現在、セル番地を指定するとうまく行くように式ができています。
=(MID(RIGHT("*********"&差込データ!$G$152,8),1,1))
*はスペースの代用です。
このシート名「差込データ」の列Gの最大値を当てはめるには、どのような式にすればよいかを教えていただけないでしょうか。
よろしくお願いします。
No.1
- 回答日時:
=QUOTIENT()
この関数は割り算の商を求める関数です。
99999を10000で割ったら商は9です。
他の桁も同様に行えば簡単だと思います。
この回答への補足
早速のご回答ありがとうございます。
=QUOTIENT()を使い、合計額についてはセルごとに分けることが出来ました。
素人考えで、次のようにしました。
1億円の桁のセル(絶対にこの桁は使わないのですが、様式としてあります)
=QUOTIENT(差込データ!$G$152,100000000)
1千万円の桁のセル
=QUOTIENT(差込データ!$G$152,10000000)
1百万円の桁のセル
=QUOTIENT(差込データ!$G$152,1000000)-I9*10
1十万円の桁のセル
=QUOTIENT(差込データ!$G$152,100000)-(I9*100+J9*10)
以降、桁が下がるごとに同じように上の桁の部分を引いて算出しています。
ここで追加の質問で恐縮なのですが、
・通常表示しないゼロ値は表示したくない。(例えば、100,0000の場合、000,100,000とはしたくないのですが、良い方法はあるでしょうか。
IFとISBLANKを使うと、一の桁とかもブランクになりそうです。)
・シート名「差込データ」にある合計額は、その件数の増減により行が毎回変わるのですが、自動で判別させるにはどのようにしたらよいでしょうか?
MATCH関数でどうかと調べているのですが、どうにもうまく行きません。
スミマセンが、教えていただけないでしょうか。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) 文字列を数式として変換する事はできますか? 6 2022/06/23 10:38
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) SUBTOTAL SUMIF?? 2 2023/03/16 11:25
- Excel(エクセル) エクセルシートの合計の変動 5 2022/04/05 15:56
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Excel(エクセル) エクセルの関数 3 2022/04/12 11:28
- Excel(エクセル) 数字にカンマを入れる方法 2 2022/04/12 11:52
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで指定したセルのどれ...
-
対象セル内(複数)が埋まった...
-
【エクセル】IF関数 Aまたは...
-
Excelでのコメント表示位置
-
Excelで数式内の文字色を一部だ...
-
貼り付けで複数セルに貼り付けたい
-
エクセルの一つのセルに複数の...
-
エクセルのセルの枠を超えて文...
-
EXCEL VBA セルに既に入...
-
excelのCOUNTIF関数で、『範囲=...
-
(Excel)数字記入セルの数値の後...
-
セルをクリック⇒そのセルに入力...
-
【Excel】 セルの色での判断は...
-
エクセル オートフィルタで絞...
-
エクセル 足して割る
-
Excel2003 の『コメント』の編...
-
エクセル “13ヶ月”を“1年1ヶ月...
-
エクセルでオブジェクトを常に...
-
複数のセルのいずれかに数字が...
-
枠に収まらない文字を非表示に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで指定したセルのどれ...
-
【エクセル】IF関数 Aまたは...
-
対象セル内(複数)が埋まった...
-
エクセル 足して割る
-
Excelで数式内の文字色を一部だ...
-
貼り付けで複数セルに貼り付けたい
-
Excelでのコメント表示位置
-
セルをクリック⇒そのセルに入力...
-
EXCEL VBA セルに既に入...
-
excelのCOUNTIF関数で、『範囲=...
-
【Excel】 セルの色での判断は...
-
エクセル オートフィルタで絞...
-
エクセルのセルの枠を超えて文...
-
(Excel)数字記入セルの数値の後...
-
Excelで、「特定のセル」に入力...
-
エクセルの一つのセルに複数の...
-
複数のセルのいずれかに数字が...
-
excelの特定のセルの隣のセル指...
-
数式を残したまま、別のセルに...
-
ハイパーリンクの参照セルのズ...
おすすめ情報