エクセルの関数について

質問

エクセルの関数について

【Sheet1】
Ｂ列・・・Ｊ列
売上日　　商品名
9/12 　　りんご
9/12 　　みかん
9/12 　　りんご
9/14　　 りんご

という売上表があったとして、Sheet2にＪ列の項目ごとに売上日を表示できる数式を教えていただきたいです。こんな↓イメージです。
【Sheet2】
Ａ列・・Ｂ列・・・Ｃ列・・・・
商品名　売上日1　売上日2・・・売上日31
りんご　9/12　　9/14

【Sheet1】の売上表は、売上が立つごとに入力していく表なので、
一日に同じ商品名（Ｊ列）が何度もでてきますが、同一売上日は、1回だけの表示にしたいです。
売上表は月でしめるので、毎日売れたとして、売上日は最大31日です。

【Sheet1】の売上表は、パソコンの苦手なパートの方が作成しているので、いじりたくありません。

当方あまりパソコンが得意ではないため、説明が分かりにくくて申し訳ないのですが、よろしくお願いします。

回答No.6

KURUMITO · ベストアンサー

パソコンはあまり得意ではないということですから、私的な意見を含めて次のようにしてみてはいかがでしょう。
シート1は手を触れたくないとのことですのでそのままＢ列に日付をＪ列に品名を入力することでよいのですが、ひと月ごとにシートを変えるのでしょうか？　そうではなく月が変わっても入力を続けて使用するのがよいと思いますね。
シート2ではお望みの月の売り上げを表示するようにしてＡ1セルには例えば9月の売り上げを表示したければ9/1とその月の初日を入力します。勿論、2011年になって2010年の12月の売り上げを表示したければＡ1セルに2010/12/1と入力します。
Ａ1セルの表示は9/1と入力した場合には９月1日のように表示されますが次のようにして９月と月だけの表示に変えることができます。
それにはＡ1セルを右クリックして「セルの書式設定」から「セルの表示形式」を選択し、分類では「ユーザー定義」を選び、種類の窓には　m"月" と入力してＯＫします。
次にＢ1セルには売上表とでも入力します。
Ａ2セルには商品名と入力してＡ3セル以降にはりんご、みかんなどの商品名を入力します。
Ｂ2セルには売上日数とでも入力します。
Ｃ2セルには次の式を入力し、ＡＦ2セルまでオートフィルドラッグします。

=IF(MONTH($A1+COLUMN(A1)-1)<>MONTH($A1),"",$A1+COLUMN(A1)-1)

これでＣ1セルからＡＦ2セルにかけて日付が表示されますが、その最大の日付は月によって変わり、月末の日付までが表示されることになります。
Ｂ3セルには次の式を入力し下方にオートフィルドラッグします。

=IF(A3="","",COUNTIF(C3:AF3,"○"))

Ｂ列に表示される数値はＡ列に表示されている品名がその月に何日売れたかを表しています。
そしてその商品が売れた日付のセルには〇印を付けて表すことにして、その〇印をＣ列からＡＦ列の間に表示させることにします。
そのためにはＣ3セルには次の式を入力してＡＦ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A3="","",IF(SUMPRODUCT((Sheet1!$J$1:$J$5000=$A3)*(Sheet1!$B$1:$B$5000=C$2))>0,"○",""))

このようにすることで何日に売れているのかがグラフのような形でわかりますし、月のうちで何日売れているのかはＢ列の数値でわかりますので、ご質問のように1日目、2日目などのようなデータの表し方よりも理解しやすいのではないでしょうか？　また、そのような表示をさせようとすれば数式も複雑になります。式も単純なほうが計算速度も早くなります。以上長々と述べましたが参考になりましたら幸いです。

お礼

お礼が遅くなりましてすみません。
フォーマットに関して決まった考えを持っていたので、別のフォーマットを提案していただき
より分かりやすい表を作成することができました。
ご協力ありがとうございました。
大変助かりました！！

回答No.7

sekkii

ピボットテーブルを使うと
商品ごとの日付だけでなく
どのくらい売れたかとか
いろんな分析ができますよ。

行のフィールドに「商品名」
列のフィールドに「売上日」
データは「売上金」とか「売上個数」とか
をドラッグすればOKです。

もっと詳しく知りたい場合には
補足欄にそう書いていただくか
ネットで調べてください。（いっぱい見つかりますよ。）

お礼

お礼が遅くなりましてすみません。
ピポットテーブルも勉強してみたいと思います。
ご協力ありがとうございました。

回答No.5

kagakusuki

適当な列（ここでは仮にSheet3のA列とします）を作業列（処理を行う際の補助処理用の列）として使用する方法です。
　尚、Excelの数式はセルの削除・切り取り・挿入等を行うと、エラーになる場合が多いのですが、本件ではSheet1をパソコンが苦手な方も触れられるという事ですから、以下の方法では、Sheet1に関しては、セルの行番号がずれても処理が可能な様になっております。（そのために、数式が長くなっております）

　まず、Sheet3のA2セルに次の数式を入力して下さい。

=IF(SUMPRODUCT((INDIRECT("Sheet1!B1:B"&ROWS($1:1))=INDIRECT("Sheet1!B"&ROWS($1:2)))*(INDIRECT("Sheet1!J1:J"&ROWS($1:1))=INDIRECT("Sheet1!J"&ROWS($1:2))))=0,SUMPRODUCT((INDIRECT("Sheet1!B1:B"&ROWS($1:1))<INDIRECT("Sheet1!B"&ROWS($1:2)))*(INDIRECT("Sheet1!J1:J"&ROWS($1:1))=INDIRECT("Sheet1!J"&ROWS($1:2)))*ISNUMBER($A$1:$A1))+1,"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に、Sheet1の予定行数を上回るのに充分な行数になるまで、貼り付けて下さい。

　次に、Sheet2のA1セルに「商品名」と入力して下さい。
　次に、Sheet2のB2セルに次の数式を入力して下さい。（もし、Sheet1の行数が999行を超える可能性がある場合には、数式中の999の箇所をより大きな数に変更して下さい。）

=IF(COLUMNS($A:A)>SUMPRODUCT((INDIRECT("Sheet1!J2:J999")=$A2)*ISNUMBER(Sheet3!$A$2:$A$999)),"",SUMPRODUCT(INDIRECT("Sheet1!B2:B999")*(INDIRECT("Sheet1!J2:J999")=$A2)*(Sheet3!$A$2:$A$999=COLUMNS($A:A))))

　次に、Sheet2のB2セルをコピーして、Sheet2のC2～AF2の範囲に貼り付けて下さい。
　続いて、Sheet2のC2～AF2の範囲をコピーして、同じ列範囲の3行目以下に、商品の種類数を上回るのに充分な行数になるまで、貼り付けて下さい。

　そして、Sheet2のA2以下の各セルに、全ての商品名を入力して下さい。

　これで準備は完了で、後はSheet1のB2以下に売上日、J2以下に売れた商品名を入力して行くだけで、自動的にSheet2に商品毎の売上日が表示されます。

　それから、商品名の入力ミスがあった場合には、当然その日付けはSheet2には表示されません。
　ですから、御質問の回答からは外れますが、商品名の入力ミスを防ぐために、以下の操作を行って、Sheet1のJ列の各セルに、ドロップダウンリストを設定されておく事を、御勧めします。（これを設定せずともSheet2に結果は表示されます）

Sheet1のJ2セルを選択
　　↓
メニューの[データ]をクリック
　　↓
選択肢の[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中の「リスト」をクリック
　　↓
現れた「元の値」欄の中に以下の数式を入力

=INDIRECT("Sheet2!A2:A"&COUNTIF(INDIRECT("Sheet2!A:A"),"><"))

　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック
　　↓
Sheet1のJ2セルをコピーする
　　↓
Sheet1のJ3以下の範囲を選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある、[値]と記されている箇所を、クリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック

　こうする事により、Sheet1のJ列のセルを選択すると現れる▼印のボタンを使用して、Sheet2のA列に入力した商品名の中の1つを間違いなく入力出来る様になります。

お礼

お礼が遅くなりましてすみません。
今回みなさんの提案してくださった方法の中で、分からないなりにも(笑)試行錯誤した結果、
KURUMITOさんの方法で売上表を作成することにしました。
パソコンが得意でない人にも処理しやすいよう、
式を組み立てていただいてありがとうございました。
勉強になりました。

回答No.4

imogasi

こういう表の組み換え問題は関数では複雑になる。一見して既出回答の式の意味など質問者にはわからないではないかな。
VBA向けの問題です。と言っても質問者には出来ないのだと思うが。
関数でやってみるが、１例として、imogasi方式（Googleで参照）の変形版の考えかたでやってみる。
例データ　A2:D１３
9月12日 りんご りんご40433 りんご1
9月12日 みかん みかん40433 みかん1
9月12日 りんご りんご40433
9月14日 りんご りんご40435 りんご2
9月14日 りんご りんご40435
9月14日 りんご りんご40435
9月14日 りんご りんご40435
9月15日 みかん みかん40436 みかん2
9月16日 なし なし40437 なし1
9月17日 みかん みかん40438 みかん3
9月16日 なし なし40437
9月18日 みかん みかん40439 みかん4
C列の式　ｃ２に　=B2&A2　下方向に式を複写　（２つの情報を１つに結合していることになる）
D列の式　D2に　=IF(COUNTIF($C$2:C2,C2)=1,B2&(COUNTIF($D$1:D1,B2&"*")+1),"")
下方向に式を複写　（品目＋日図家で、違うものに連番を振っていることになる）
ーーー
F2:F4にB列について、フィルターフィルタオプションの設定ー重複するレコードは無視するで品目一覧を出す（重複せず、漏れのない品目を出せる）
ーー
G2に　=INDEX($A$1:$A$13,MATCH($F2&COLUMN()-6,$D$1:$D$13,0))
と入れてK2まで式複写
G2:K2の式をG4:K4まで式を複写。
結果
みかん 9月12日 9月15日 9月17日 9月18日 #N/A
りんご 9月12日 9月14日 #N/A #N/A #N/A
なし 9月16日 #N/A #N/A #N/A #N/A
もう一押し、上記の式でもし#N/A（ISNA関数で判別）なら空白にするIF関数をかぶせて、エラーを見えなくする。

お礼

お礼が遅くなりましてすみません。
今回みなさんの提案してくださった方法の中で、分からないなりにも(笑)試行錯誤した結果、
KURUMITOさんの方法で売上表を作成することにしました。
ご協力ありがとうございました。
勉強になりました。

回答No.3

MackyNo1

例示のレイアウトなら、まず重複のない商品名を表示するためにSheet2のA2セルに以下の式を入力して下方向にオートフィルします（商品名が決まっているなら数式を使う必要はありません）。

=INDEX(Sheet1!J:J,SMALL(INDEX((MATCH(Sheet1!J$1:J$100&"",Sheet1!J$1:J$100&"",)<>ROW(J$1:J$100))*100+ROW(J$1:J$100),),ROW(A2)))&""

B2セルには以下の式を入力し、Ctrl+Shift+Enterで確定し下方向にオートフィルします。

=MIN(IF(Sheet1!$J$2:$J$100=$A2,Sheet1!$B$2:$B$100))

C1セルに以下の式を入力し、Ctrl+Shift+Enterで確定し右方向および下方向にオートフィルします。

=MIN(IF((Sheet1!$J$2:$J$100=$A2)*(Sheet1!$B$2:$B$100>B2),Sheet1!$B$2:$B$100,""))

最後に、日付データ範囲を選択し、右クリック「セルの書式設定」で表示形式を「ユーザー定義」にして種類の欄に「m/d;;;」と入力します。

お礼

お礼が遅くなりましてすみません。
今回みなさんの提案してくださった方法の中で、分からないなりにも(笑)試行錯誤した結果、
KURUMITOさんの方法で売上表を作成することにしました。
ご協力ありがとうございました。
勉強になりました。

回答No.2

tom04

こんばんは！
一例です。

↓の画像で説明します。
Sheet1に作業用の列を3列使っています。

質問内にSheet1はあまりいじりたくないということなので、
データに関係ない離れたところに作業列を作成してもかまいません。
（その場合は数式の列番号が変わりますので、適当に調整してみてください。）

作業列K2セルに
=IF(COUNTIF($J$2:J2,J2)=1,ROW(A1),"")
L2セルに
=B2&J2
M2セルに
=IF(L2="","",IF(COUNTIF($L$2:L2,L2)=1,ROW(A1),""))
として、K2～M2セルを範囲指定し、M2セルのフィルハンドルでオートフィルで下へずぃ～～～！っとコピーしておきます。
Sheet2の数式が1000行目まで対応できるようにしていますので、そのくらいまでコピーしておいてもかまいません。

そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$K$2:$K$1000)<ROW(A1),"",INDEX(Sheet1!$J$2:$J$1000,SMALL(Sheet1!$K$2:$K$1000,ROW(A1))))
B2セルに
=IF(OR($A2="",SUMPRODUCT((Sheet1!$J$2:$J$1000=$A2)*(Sheet1!$M$2:$M$1000<>""))<COLUMN(A1)),"",INDEX(Sheet1!$B$2:$B$1000,SMALL(IF((Sheet1!$J$2:$J$1000=$A2)*(Sheet1!$M$2:$M$1000<>""),ROW($A$1:$A$999)),COLUMN(A1))))
このB2セルの数式は配列数式になってしまいますので、
この画面からコピー＆ペーストしただけでは2列目以降がエラーになると思います。
B2セルに貼り付け後、F2キーを押す、またはB2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。
尚、B2セルにはシリアル値が表示されますので、セルの表示形式から「日付」を選択しておきます。

このB2セルを最終列までオートフィルでコピーします。
最後に、A2～今コピーした最終列を範囲指定し、最終列のフィルハンドルで下へオートフィルでコピーすると
画像のような感じになります。

以上、長々と書きましたが
参考になれば良いのですが・・・m(__)m

お礼

お礼が遅くなりましてすみません。
今回みなさんの提案してくださった方法の中で、分からないなりにも(笑)試行錯誤した結果、
KURUMITOさんの方法で売上表を作成することにしました。
画像も添付していただいて、丁寧に説明していただいてありがとうございました。

回答No.1

mu2011

次のような表では駄目でしょうか。
sheet2の１行目の売上日はsheet1の売上日と同様の書式で仮に９月は9/1～9/30で31日目は空白とします。
売上日付が重複している場合には売上件数を加算表示します。

sheet2のB2に=IF(AND($A2<>"",B$1<>""),SUMPRODUCT((Sheet1!$B$2:$B$100=$A2)*(Sheet1!$A$2:$A$100=B$1)),"")を設定、縦横にコピー

お礼

お礼が遅くなりましてすみません。
今回みなさんの提案してくださった方法の中で、分からないなりにも(笑)試行錯誤した結果、
KURUMITOさんの方法で売上表を作成することにしました。
ご協力ありがとうございました。
勉強になりました。