エクセルでこれはやはり無理なことでしょうか?（二回目の質問）

質問

先日、同様の質問をした者ですが、仕事での用途を考え、下記のような変則表示形式を取っています。（合計セル-項目セル）
　常連の回答者様から『一般的でない、アクセスでやること』との指摘を頂戴しました。
　私はアクセスは分かりません。
　私はＣ列の＊合計数量から下段の等しい文字列を含んだセルの数量を引き、残数表示をＥ列に表示したいのですが、解決できず、今も困っています。
　（Ｂ３合計５００－１５０－１５０－２００＝０）
　Ａ　　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　Ｅ
期日　　型式　　コード　　　数量　　残数
３／１　　　　　　Ａ１合計　　１００
３／１　天１　　　Ａ１　　　　１００　　　０
３／１　天３　　　Ｂ３合計　　５００
３／１　天７　　　Ｂ３　　　　１５０　　３５０
３／１　天３　　　Ｂ３　　　　１５０　　２００
３／１　天８　　　Ｂ３　　　　２００　　　０
３／１　　　　　　Ｃ２合計　　６００
３／１　壁２　　　Ｃ２　　　　３００　　３００
３／１　壁５　　　Ｃ２　　　　１５０　　１５０
３／１　壁３　　　Ｃ２　　　　１５０　　　０
　　一般的でないのは承知しています。単純にこの表示形式を関数等で残数値を導くのは不可能なのでしょうか?
　データーが多く、日々変動もあり手入力では無理です。IF,SUMIF,AND,ORを使って何度もトライしていますが、正しく表示できません。（条件設定、セル参照が分からない）エキスパートの方、助けて下さい。

回答No.7

shinkun0114 · ベストアンサー

#2です。補足読みました。
後にGとHの2列を付け加えます。見えるとまずいのであれば、最後にこの列を「表示しない」にしてください。

　　　Ａ　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　Ｅ　　　　Ｆ　　　　　Ｇ　　　　Ｈ
[ 1]　期日　順　　型式　　コード　　　数量　　残数　　　仮セル　　仮セル　　
[ 2]３／１　　　　　　　　　Ａ１合計　　１００　　　　　　　　100　　　FALSE
[ 3]３／１　８　　天１　　　Ａ１　　　　１００　　　０　　　　100　　　TRUE
[ 4]３／１　　　　天３　　　Ｂ３合計　　５００　　　　　　　500　　　FALSE
[ 5]３／１　９　　天７　　　Ｂ３　　　　１５０　　　０　　　　500　　　TRUE
[ 6]３／１　８　　天３　　　Ｂ３　　　　１５０　　１５０　　　500　　　TRUE
[ 7]３／１　７　　天８　　　Ｂ３　　　　２００　　３００　　　500　　　TRUE
[ 8]３／１　　　　　　　　　Ｃ２合計　　６００　　　０　　　600　　　FALSE
[ 9]３／１　５　　壁２　　　Ｃ２　　　　３００　　　０　　　　600　　　TRUE
[10]３／１　４　　壁５　　　Ｃ２　　　　１５０　　３００　　　600　　　TRUE
[11]３／１　３　　壁３　　　Ｃ２　　　　１５０　　４５０　　　600　　 TRUE

このような形式で可能になります。

入力する式ですが、

F2セル　=IF(H2,IF(H3,F3-E2,G2-E2),"")
G2セル　=IF(H2,G1,E2)
H2セル　=ISERROR(SEARCH("合計",D2,1))

これを下にコピーしていってください。

お礼

何度も回答有難うございます。
度々、お手数をおかけして感謝申し上げます。
　変な表展開ですが、これが解決すると助かります。
表数値と数式の関係を検証して表示させて見ます。
　スキルの低い質問者に重ね重ねのご回答、本当に有難うございました。

回答No.6

noname#204879

E2: =IF(ISERROR(FIND("合計",C2)),E1-D2,D2)
　　条件付書式
　　数式が　　=NOT(ISERROR(FIND("合計",C2)))
　　書式　　　フォント色が白

お礼

回答有難うございました。
アドバイスいただいた数式で残数表示できましたが、質問の表に欠陥がありまして（質問者のエラーです）
　＃１さんへのお礼に記したような表にするべきだったのです。

　その表になると、セル番地、条件表記が変り、IF関数で出せるでしょうか?
　質問者に落ち度があり、大変、申し訳ないです。
本当に有難うございました。

回答No.5

hinta00

こんにちは。
Ｃ列の「コード」項目のデータの並びが必ず「合計」から始まり以下明細行というかたまりでデータが出来ているのを前提にしますと以下のような数式をＥ列へ入れれは良いと思います。
=IF(RIGHT(C2,2)="合計","",IF(E1="",D1-D2,E1-D2))

別にAccessでやるほどの事ではないように思います。表計算ソフトなのですからこのくらいの表は当たりまえにつくりますし、ぜんぜん「一般的でない」ことはないと思いますよ。そんなに悲観なさらないでください。

お礼

詳細な解説を添えた回答を戴き、有難うございました。
　hinta00さんの数式で当初質問した表の残数表記は出来ましたが、使用順位というものが抜けていて（同じコードが連続する中で、下から使い、それに対しての残数表示ということを説明できていませんでした。

　修正、順追記形式の表では一つの関数式では出せないでしょうか?肝心なこと忘れていたダメ質問者に願わくば、再度、アドバイスをいただけたら幸いです。

　本当に有難うございました。申し訳ありませんでした。

回答No.4

mshr1962

集計のキーがＣ列のコードだけなら
E2=IF(RIGHT($C2,1)="計","",SUMIF($C:$C,$C2,$D:$D)-SUMIF($C$2:$C2,$C2,$D$2:$D2))
これを下方にコピーする。

お礼

早々の回答有難うございました。
mshr1962さんの数式でも可能となったのですが、質問させていただいた表に重大なミスがあったことに気付き、＃１さんへのお礼にも書いた通りです。

　私の過失で申し訳ありません。修正した表形式で使用順に応じた残数表記となると無理でしょうか?
　変則的な表記の仕方で分かりにくいものを、回答賜り、有難うございました。

回答No.3

shinkun0114

#2です。

補足しておきますと、#2の回答は、コードが連続していることが原則です。
　B3合計
　B3
　C3合計
　C3
　B3
　C3
　：
このようにC3とB3が混ざると処理できません。

　ただ、コード混在があっても、Excelで処理できないこともありません。
Excelで処理しようとする場合に大事なことは、
　★違う意味を持つデータを同じ列で処理しない★
ということです。
　質問者さんの事例では、D列がこれに相当します。合計値であったり
違う商品の数量であったりします。これがデータを処理する上で、
芳しくないのです。
　データの並びを変えたくないのであれば、そこは表示するだけにして、
F列以降にデータを分けてしまうといいでしょう。
F列にコードA1、G列にコードA2、J列にコードC3といった具合です。
　計算はF列以降で行い、E列は出てきた答をまとめるだけのセルにします。

　このように行えば、書式を崩さずとも、意外と簡単に処理はできます。

回答No.2

shinkun0114

まず、わかりやすいように行番号を振ってみました。

　　　Ａ　　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　Ｅ
[ 1]　期日　　型式　　コード　　　数量　　残数
[ 2]３／１　　　　　　Ａ１合計　　１００
[ 3]３／１　天１　　　Ａ１　　　　１００　　　０
[ 4]３／１　天３　　　Ｂ３合計　　５００
[ 5]３／１　天７　　　Ｂ３　　　　１５０　　３５０
[ 6]３／１　天３　　　Ｂ３　　　　１５０　　２００
[ 7]３／１　天８　　　Ｂ３　　　　２００　　　０
[ 8]３／１　　　　　　Ｃ２合計　　６００
[ 9]３／１　壁２　　　Ｃ２　　　　３００　　３００
[10]３／１　壁５　　　Ｃ２　　　　１５０　　１５０
[11]３／１　壁３　　　Ｃ２　　　　１５０　　　０

E列だけ計算すればいいのでしょうか？
たとえば、E2に
=IF(ISERROR(SEARCH("合計",C2,1)),E1-D2,D2)
この式を入れて、下にコピーしてみてください。

お礼

早速の回答有難うございました。色んな方の回答をいただいてから気付いたのですが、私の説明不足で申し訳ありません。　質問させていただいた形式の表で、使用順位があり、降順で使用するため、実はE列の残数表示を以下のようにしたかったのです。
　　　Ａ　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　Ｅ
[ 1]　期日　順　　型式　　コード　　　数量　　残数
[ 2]３／１　　　　　　　　　Ａ１合計　　１００
[ 3]３／１　８　　天１　　　Ａ１　　　　１００　　　０
[ 4]３／１　　　　天３　　　Ｂ３合計　　５００
[ 5]３／１　９　　天７　　　Ｂ３　　　　１５０　　　０
[ 6]３／１　８　　天３　　　Ｂ３　　　　１５０　　１５０
[ 7]３／１　７　　天８　　　Ｂ３　　　　２００　　３００
[ 8]３／１　　　　　　　　　Ｃ２合計　　６００　　　０
[ 9]３／１　５　　壁２　　　Ｃ２　　　　３００　　　０
[10]３／１　４　　壁５　　　Ｃ２　　　　１５０　　３００　
[11]３／１　３　　壁３　　　Ｃ２　　　　１５０　　４５０
　としたかったのです。
何故、こういう表示形式にするのは＊合計セルの下段の等しい文字列を含んだセル項目がありますが、わざと仕事の都合上、下から使うとルールで順位おいて、若い番号の型式が終わったあと、合計に対し、残りの使用対象順のコードがどれだけの残量があるか表示したいのです。
　折角、時間を掛けていただいて回答を戴いたのに私の重大な表記ミス、説明ミスで誠に申し訳ありません。
　当初の表記形式では回答いただいた数式で表示できました。戻り値を見てから意図したように表を作れていなかったことがわかり、すみませんでした。
　本当に有難うございました。

回答No.1

noname#25230

エクセルやWord、Accessなどは、VBA(Visual Basic for Application)という、プログラミング言語が使えるようになっています。セルに書く関数などでは実現できないことでも、複雑な条件に基づいた処理を行わせることが可能です。

...という時点で「へぇ、やってみようかな」と思われないのであれば、無理です（嫌味とかではなく、きちんと勉強しないといけませんし、時間も手間もかかります。覚えてしまえば将来にわたって使えるかもしれませんが）。

というわけで、VBAを知っている人であれば、たぶんVBAを使って実現可能だと思います。

ただし、それが「本来のエクセルの使い方かどうか？」ついては疑問です。何でもかんでもエクセル、という考え方はあまりよろしくありません。適切なツールを適切なタイミングで利用してこそ「仕事」です。趣味でチャレンジするのを誰も止めませんが、仕事としてやるなら、もっとも効率的な方法を検討すべきです。あなたが知っているかどうか、ではなく。