先日教えていただいたエクセル関数ですが応用して試してみましたができませんでした。
以前質問した表は
グループ 名前 作成月
A 浅井 1
A 伊藤 3
A 小林 1
B 久野 3
B 酒井 1
C 服部 3
C 林 1
ですが・・・
グループ 日付 名前 作成月
A 9/10 浅井 1
A 9/25 伊藤 3
A 6/8 小林 1
B 2/7 久野 3
B 2/28 酒井 1
C 4/10 服部 3
C 4/15 林 1
と間に日付の列がある状態でも
グループAかつ作成月1を抽出
浅井
小林
は可能でしょうか?(※この場合日付は抽出しません。実際の表は抽出したい列以外の内容の列がたくさんあります)
また、
グループ 名前 作成月
A 浅井 1
A 伊藤
A 小林 1
B 久野 3
B 酒井 1
C 服部 3
C 林
のように作成月など空欄がある場合でも数式はかわりないでしょうか?
質問の意図が分かりにくいかもしれませんがよろしくお願いします。
No.6ベストアンサー
- 回答日時:
No.5の件について・・・
INDEX関数を省いてはいけません。
INDEX関数で範囲指定した列(C列)の何行目を返すか?という数式なので
そのままの数式で大丈夫のはずです。
(数式そのものは日付列を一切参照していませんので。)
こちらでお礼欄に記載されている数式をそのままコピー&ペーストし配列数式にしてみたら
ちゃんとNo.2の画像のように表示されました。
一つ気になるのですが、
>元の表でC列(日付)のような余分なものがない場合は・・・
ん?日付列はB列なのでは?
(No.2の画像の配置での数式にしています)
要するに
INDEX関数の範囲に指定するのは「名前」列です。
そして必要条件は「グループ」列と「作成月」の列だけですので、
その二つの列の配列数式にする必要があります。
※ SUMPRODUCT関数で「グループ」×「作成月」が存在しない場合のエラー処理をしてるので
「#NUM!」のエラーが出るコト自体考えられないのですが、
もしかして数式を入れた2行目以降に出ていませんか?
その場合は配列数式になっていない可能性があります。
もう一度配列数式になっているかどうか確認してみてください。m(_ _)m
回答ありがとうございます。日付はB列でした。
配列数式が成立しました!
何度もありがとうございました。
またご相談させていただくことがあるかもしれませんがその際はよろしくお願いします。
No.5
- 回答日時:
>「#NAME?」が出てきてしまいました。
もしかしてお使いのバージョンはExcel2003以前なのでしょうか?
もしそうであればIFERROR関数は使えませんのでそのような表示になるコトがあります。
Sheet2のB3セルの数式を
=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2))<ROW(A1),"",INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))))
やはり配列数式なのでCtrl+Shift+Enterで確定してください。
今度はどうでしょうか?
※ Excel2007以降のバージョンで同じエラーが表示される場合
関数のアルファベットが違ったりすると同じ表示になる事があります。m(_ _)m
何度も回答いただきありがとうございます。
Excelのバージョンが2003でした。
上記の配列数式でやってみたのですが・・・「#NUM!」が出ました。
ちなみに、元の表でC列(日付)のような余分なものがない場合は
=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2))<ROW(A1),"",INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))))
の中でINDEX(Sheet1!$C$1:$C$1000,を省けばいいのでしょうか?
No.4
- 回答日時:
何度もごめんなさい。
もう一度投稿文を読み返してみて・・・
なかなか内容が理解できないと思います。
↓の画像でもう一度説明します。
結局E~I列の操作を一気にやっているのが前回の配列数式です。
E列 → E2セルに
=A2=E$1
として下へフィル&コピーしています。
これでA列が「A」の場合の行が「TRUE」、それ以外は「FALSE」になります。
F列 → F2セルに
=C2=F$1
とし下へフィル&コピー
これでC列が「1」の行が「TRUE」それ以外は「FALSE」となります。
G列 → G2セルに
=E2*F2
としフィル&コピー
これでA列が「A」なおかつC列が「1」の行が「1」となります。
H列 → H2セルに
=IF(G2=1,ROW(),"")
とし、フィル&コピー!
これで両条件が一致する行番号が表示されます。
=ROW() とどこか使っていないセルに入れて下へフィル&コピーしてみてください
その行の番号が表示されます。
最後にI2セルに
=INDEX(B:B,SMALL(H:H,ROW(A1)))
とし下へフィル&コピー!
これで2・4行目のデータが表示され後はエラーになります。
このエラーはIFERRO関数で表示しないようにしていました。
前回の質問にあった
=ROW(A1)
の部分はSMALL関数の「順位」の部分に当たりますので、
=ROW(A1) は「1番目」 → =ROW(A2)は「2番目」・・・という意味です。
※ このA1に関しては =ROW(B1) とか =ROW(Z1) のようにどこの列にしても必ず「1」となります。
以上長々と書きましたがご理解いただけたでしょうか?
とりあえず
この画面から前回の数式をドラッグ → コピー → Sheet2のB3セルを選択 → 数式バー内に貼り付け そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定してみてください。
数式の前後に{ }マークが入り配列数式になります。m(_ _)m
丁寧な説明ありがとうございます。
数式の意味合いは大分理解はできましたm(_ _)m
実際にやってみたのですが・・・「#NAME?」が出てきてしまいました。
どうしたらいいでしょうか?((+_+))
No.3
- 回答日時:
続けてお邪魔します。
少し前回の数式の説明を!
>=B$1の「B$1」はSheet2の「A」の解釈でよいでしょうか?
はい!画像ではB1セルを参照していますので、「A」です。
余談として、次の
>Sheet1!$D$1:$D$1000=B$2
の「B$2」は「1」であるコトは判りますよね?
すなわち両条件を掛け合わせていますので、「AND条件」になり、
「A列でTRUE」×「D列でTRUE」の行だけを表示させたいのです。
Sheet1のA1~A1000で「A」の場合が「TRUE」それ以外は「FALSE」となります。
Excel的にTRUE=1 FALSE=0 ですので、両方の条件に合致する行が「TRUE」となります。
前回の画像ではSheet1の「浅井」と「小林」の行だけが「TRUE」=1となり、他の行はすべて「FALSE」=0 となります。
かつ、その次の
>ROW($A$1:$A$1000)
で行番号を掛け合わせたことになるので、Sheet1の2・4だけがIF関数の「真の場合」になります。
最後に「真の場合」の小さい順に表示させるためにSMALL関数を使い
ROW(A1) としています。
これをフィルハンドルで下へコピーすると
ROW(A1)=1 → ROW(A2)=2 → ROW(A3)=3 ・・・
と変化するので
TRUEの場合のINDEX関数で範囲指定した「真の場合」の1番目の行 → 2番目の行 → 3番目の行・・・
と表示されます。
画像では3番目がないのでエラーになるのですが、
IFERROR関数を使ってエラーの場合は空白にするようにしています。
以上が前回の数式の説明になりますが
ご理解いただけたでしょうか?
一気に理解するのは難しいかもしれませんが、
前回の配列数式の意味はこんな内容です。
※ 配列数式はPCにかなりの負担を掛けます。
極端に広い範囲(列全体など)すると計算速度が非常に落ちるので
とりあえず範囲を1000行程度にしています。m(_ _)m
No.2
- 回答日時:
No.1です。
結局一覧表のような形で表示したい!というコトでしょうか?
そうなると作業列を票の数だけ作るのも大変なので、やっぱり配列数式ですかね。
↓の画像ではSheet2のB2セルに
=IF(COUNTBLANK(B$1:B$2),"",IFERROR(INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))),""))
配列数式です。
としてフィルハンドルで列・行方向にコピーしています。m(_ _)m
ありがとうございます。
配列数式の確認ですが・・・・
SMALL・・・・=B$1の「B$1」はSheet2の「A」の解釈でよいでしょうか?
また、ROW(A1)・・・の「A1」はSheet2の「グループ」と解釈してよいでしょうか?
何度もすみませんがよろしくお願いします!
No.1
- 回答日時:
こんにちは!
https://oshiete.goo.ne.jp/qa/9954043.html
の関連質問ですね。
あちらの方法は最初から配列数式を使うようにしていましたが、条件がどんどん増えてきていますので
一気に!となると配列数式そのものがやたらと長くなり、数式の修正も難しくなります。
そこで作業用の列を設ける方法をおススメします。
↓の画像のような配置でSheet2の1行目に表示したい項目名を入れておき、そのデータのみを抽出するとします。
Sheet1のF列に作業列を設けます。
作業列F2セルに
=IF(AND(COUNTIF(Sheet2!E:E,A2),COUNTIF(Sheet2!F:F,D2)),ROW(),"")
という数式を入れこれ以上データは増えない!という位まで下へフィル&コピーしておきます。
そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!$A:$D,SMALL(Sheet1!$F:$F,ROW(A1)),MATCH(A$1,Sheet1!$A$1:$D$1,0)),"")
という数式を入れ、フィルハンドルで列・行方向にコピー!
これで画像のような感じになります。
※ 今回、配列数式は一切ありません。
※ Sheet2の1行目項目数が増えても対応できます。
※ 「グループ」・「作成月」の検索条件が増えても大丈夫です。
※ 作業列が目障りであれば
遠く離れた列にするか、非表示にしてください。m(_ _)m
何度も回答いただきありがとうございます。
まず以下のように抽出したいと考えています。
グループ A B C A B C
作成月 1 1 1 3 3 3
浅井 酒井 林 伊藤 久野 服部
小林
その後上記のようなこともやりたいのですが・・・・
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- サッカー・フットサル ワールドカップのメンバーとして森安監督が確実に選びそうなのが原口元気、浅野拓磨、柴崎岳、遠藤航、長友 1 2022/05/29 22:07
- 野球 高校野球の話 1 2022/08/18 09:54
- その他(プログラミング・Web制作) Python pandasについての質問です。 日付 名前 ◯月◯日 佐藤 ・ 伊藤 ・ 山田 ・ 2 2022/06/13 17:16
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- サッカー・フットサル 2026年W杯、日本代表メンバーは? 1 2022/12/07 13:39
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける (再質問) 4 2022/09/14 22:51
- サッカー・フットサル 待ちに待った2022サッカーW杯・日本代表メンバー発表がありました。 GK 川島永嗣(ストラスブール 5 2022/11/01 14:33
- 歴史学 酒井 忠次 と 小五郎 という名乗り の関係 について 1 2022/05/02 12:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのデーターが2か月前の...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
エクセル②
-
(マクロ)データをAブックからB...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
-
指定文字の間に
-
Microsoft 365の Excel を使用...
-
エクセルの計算
-
エクセルでの作業計算方法について
-
Excelで全角を半角にしたいので...
-
エクセル関数に詳しい方教えて...
-
Googleスプレッドシートでファ...
-
エクセル 文字を増やしたい。
-
はがきについて。
-
エクセルの暗号化なしのバーの...
-
【マクロ】必要な項目(列)の...
-
Excel
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報