Q&A
Excelのバージョンはいずれでしょうか？
vbaでユーザー定義のセル関数を作成する方法はありでしょうか？
それとも標準のセル関数のみで実現したいのでしょうか？
その場合作業用の列を追加することは可能でしょうか？
エクセルの同じシート上に、商品の販売日ごとの販売個数のデータ（表の左側）と、そのデータを利用せずに算出した、商品ごとの販売個数合計のデータ（表の右側）があります。ここで左表には、右表に無い商品が複数あります。
左表にしかない商品を抽出するには、
COUNTIF(右表の商品名列,左表の商品名)を利用して出現件数をそれぞれ表示し、フィルタをかけて出現件数を０のものを選べばできます。
その方法以外で、左表にしかない商品を抽出するのに、☆列に左表にしかない商品を上から順番に表示していくことはできないでしょうか。配列数式{=IF(右表商品名列=左表商品名,"",左表商品名)}としましたがうまくいきませんでした。よろしくお願いします。
|商品名|販売日|販売個数|☆|商品名|販売個数合計|
|:--|:--:|--:|
|商品A|1/4|6||商品A|6|
|商品B|1/4|2||商品B|7|
|商品B|1/6|5||特価セール品A|5|
|特価セール品A|1/4|1||在庫一掃品A|1|
|特価セール品A|1/5|4|
|在庫一掃品A|1/4|1|
|左表にしかない商品A|1/6|4|
|左表にしかない商品B|1/6|1|
↑この表に、↓のように☆列に追加したいです。
|商品名|販売日|販売個数|☆|商品名|販売個数合計|
|:--|:--:|--:|
|商品A|1/4|6|左表にしかない商品A|商品A|6|
|商品B|1/4|2|左表にしかない商品B|商品B|7|
|商品B|1/6|5||特価セール品A|5|
|特価セール品A|1/4|1||在庫一掃品A|1|
|特価セール品A|1/5|4|
|在庫一掃品A|1/4|1|
|左表にしかない商品A|1/6|4|
|左表にしかない商品B|1/6|1|
（追記）
Excelのバージョンは2013です。
vbaでユーザー定義のセル関数を作成する方法でもいいです。
作業用の列も追加できます。
よろしくお願いします。
（追記）
難しい問題にご回答いただきありがとうございました。
システム上ベストアンサーは一人ですが、
皆様にベストアンサーを。
回答3件
0
ベストアンサー
VBAでユーザ定義関数を作る場合
Excel
1[D2] =matching($A$2:$A$9,$E$2:$E$5,ROW()-1)
vba
1Function matching(a, b, c) 2 Dim i 3 For Each matching In a.Value 4 If WorksheetFunction.CountIf(b, matching) = 0 Then i = i + 1 5 If i = c Then Exit Function 6 Next 7 matching = "" 8End Function
ワーク列を用いる場合
Excel
1[G2] =IF(AND(COUNTIF($E:$E,$A2)=0,COUNTIF(OFFSET($A:$A,0,0,ROW($A2)-1),$A2)=0),ROW(),"") 2[H2] =MIN(OFFSET($G:$G,N(H1),0,COUNTA($A:$A)-N(H1))) 3[D2] =IFERROR(INDEX($A:$A,H2),"")
最新版のExcelでスピル関数が使える場合
Excel
1[D2] =IFERROR(UNIQUE(INDEX($A:$A,SORT(IF(COUNTIF($E:$E,$A$2:$A$9)=0,ROW($A$2:$A$9),NA)))),"")
投稿2021/11/19 12:54
編集2021/11/20 01:31
総合スコア4585
＞VBAでユーザ定義関数を作る場合
＞For Each matching In a.Value
セル範囲aから値を取り出す方法、戻り値を返す処理をFor each分の中に組み込む方法、変数iとrow()-1を利用して、i番目の右表に無い商品を表示する方法など、大変参考になりました。ありがとうございました。
＞ワーク列を用いる場合
関数で処理する方法を教えていただきありがとうございます。
=MIN(OFFSET($G:$G,N(H1),0,COUNTA($A:$A)-N(H1)))
の部分の意味を理解するのが難しいです。
もし良ければこの部分の意味を教えていただけないでしょうか。
＞最新版のExcelでスピル関数が使える場合
ありがとうございます。使える環境になった場合に利用します。
はい、とても分かりづらいですよね。
うまく説明できるかわかりませんが、書いてみます。
例として、商品B,左表にしかない商品A,左表にしかない商品Bが対象だとすると
G列に仕込んだ式により、G3セル=3,G8セル=8,G9セル=9 となります。
このとき、H2セル＝3,H3セル=8,H4セル=9としたいわけです。
OFFSETの部分がややこしいので一旦おいておくと、
まずH2セルは単純に =MIN(G1:G9) で3を取得、
次のH3セルでは、4行目以降での最小値を探す、つまり=MIN(G4:G9)で8を取得、
といった感じになっています。
つまり、H3セルでは、H2セルの値である3を手掛かりに、G4:G9という範囲を作り出したいのです。
そこで、OFFSET関数を使っています。
OFFSET関数は、第１引数の範囲を何行（何列）かずらした範囲を取得するものなので、
H3セルの数式の中の、 OFFSET($G:$G, N(H2) , 0 , COUNTA($A:$A) - N(H2) )の部分は、
$G:$G（＝G1:G1048576）を、３行ずらす、０列ずらす、高さを９－３＝６行分にする、ことによって
G4:G9という範囲を導き出しています。
ありがとうございます。
OFFSET関数の説明分かりました。
３つも方法を教えていただきありがとうございました。
0
一番左上の 商品名 と入力されたセルをA1セルとして説明します。
まずワーク列としてD,E,F列を追加します
D2セルに=IF(COUNTIF(H:H,A2) + COUNTIF(A$1:A1,A2)=0,ROW() & " ","") を入力し、必要な行数コピー
E2セルに=D2 & E3 を入力し、必要な行数コピー
F1セルに=" " & E2 を入力
F2セルに=MID(F1,FIND(" ",F1)+1,9999) を入力し、必要な行数コピー
G2セルに=IFERROR(INDIRECT("A" & LEFT(F2,FIND(" ",F2)-1)),"") を入力し、必要な行数コピー
実行結果はこのような感じになります。
追記 2021/11/22
SMALL()関数というものを使うと、もっとスマートに実現できました。
ワーク列を新たに挿入せずにG列をワーク列として
G2セルに=IF(COUNTIF(E:E,A2) + COUNTIF(A$1:A1,A2)=0,ROW(),"")
結果を求めたいD列に対して
D2セルに=IFERROR(INDIRECT("A" & SMALL(G:G,ROW(1:1))),"")
投稿2021/11/19 12:12
編集2021/11/22 11:11総合スコア358
D,E,F列を挿入しましたが、新たな列を挿入せず、G,H,I列をワーク列として使用した方が良かったような気がします。
＞+ COUNTIF(A$1:A1,A2)
の部分は、右表に無い商品のうち、左表で同じ商品が二度目以降出現する際の処理に必要なのですね。その他の関数も、スペースを使って値を表現して処理に利用する方法など、大変参考になりました。
ありがとうございました。
もっとスマートに実現できるSAMLL()関数というものがありました。
回答に追記しておきます。
0
マクロを使えばできそうですが、関数のみだとパッと思いつくのはかなり力技に頼ったものでした。
左上の "商品名" を A1 セルとして D2 セルに以下を記述
=SORT(IF(COUNTIF(E2:E5,UNIQUE(A2:A9,FALSE,FALSE)),"",UNIQUE(A2:A9,FALSE,FALSE)),1,-1)
とすれば一応☆の下に "左表にしかない商品B"、"左表にしかない商品A" の順で表示されます。
ただ、このやり方は左表の重複を除いたデータが右表に現れる出現回数を見て0回のもののみを表示（1回以上出現は空白）とし、ソートで空白を無理やり下に追いやっているだけです。
もっとスマートな回答が出るまでの繋ぎとして活用していただければと...
投稿2021/11/19 07:35
総合スコア140
ありがとうございます。
とても大変なのですね。
頑張って内容を理解します。
応用して以下ではどうでしょう。
=IFERROR(INDEX($A$1:$A$9,SORT(IF(COUNTIF($E$2:$E$5,$A$2:$A$9)=0,ROW($A$2:$A$9),999999)),),"")
Excel2013ではスピル関数のSORTは使えないのでは？
あなたの回答
tips
プレビュー
