関数で出来ますか～？ 第21問 表の抜き出し 解答例

今回は正直なところ、自分であまり良い手法が思いつかなかったので、誰かにカッコ良い
ソリューションを見せていただきたくて出題したようなところがあります。
なんか、盛り上がらなくて申し訳ありませんでしたm(＿＿)m

で、私は配列数式でSMALLまたはLARGEを使用する方法しか思いつきませんでした。
いずれも配列数式で、G４をH4にドラッグ、その後28行までドラッグと言うことになります。

LARGEを使用した68
{=INDEX(A:A,29-LARGE(($C$4:$C$28=$E$3)*(26-ROW($1:$25)),ROW()-3),1)}

SMALLに切り替えた61
{=INDEX(A:A,SMALL(9^($C$4:$C$28<>$E$3)*ROW($4:$28),ROW(A1)))}

上の61を無理やりダイエットした58でH列の方では式が崩れており、結果オーライになっている
にすぎません。
{=INDEX(A:A,SMALL(9^($C$4:C28<>$E$3)*ROW($4:28),ROW(A1)))}

どなたか、全く別の手法を見せて下さるとうれしいのですが＾＾

                                                                                          （y sakudaさん談)

****************************************************************************************

★ますたあさんから頂いたファイルをアップしました。（2009/6/7 9:17追記)

Q21_master_san.zipをダウンロード

Comments

[投稿情報: む印]

考え方は同じですｄ

６０文字
G4=OFFSET(A$3,SMALL(IF(C$4:C28=E$3,ROW($1:25),30),ROW(A1)),)
Ctrl+Shift+Enterし、G4をG28までフィルコピー

５６文字
G4=INDEX(A:A,SMALL(IF(C4:C28=E3,ROW(4:28),50),ROW(A:A)))
G4:G28を選択してCtrl+Shift+Enter

５５文字は姑息な一文字減
G4=INDEX(A:A,LARGE(IF(C1:C28=E3,ROW(1:28),1),ROW(A:A)))
G4:G28を選択してCtrl+Shift+Enter
・・・表示が逆並びでゴメンなさいm(__)m

[投稿情報: kir]

Ctrl+Shift+Enterなし
65：=INDEX(A:A,30-LARGE(INDEX((C$4:C28=E$3)*30-ROW($4:28),),ROW(A1)))
普通のやつ
58：{=INDEX(A:A,SMALL(IF(C$4:C28=E$3,ROW($4:28),99),ROW(A1)))}
２文字減るけど重くなる。
56：{=INDEX(A:A,SMALL(IF($4:28=E$3,ROW($4:28),99),ROW(A1)))}

遊びで、
=IF(H4,INDEX(A:A,MOD(H4,1)*1000))
{=INDEX(B$4:B99+ROW($4:99)/1000,SMALL(IF(C$4:C28=E$3,ROW($4:28)-3,99),ROW(A1)))}

[投稿情報: minmax]

私も含めて、みなさんで結構苦しい式ありですね～(^^;)

[投稿情報: ますたあ]

考え方としては皆さんとほとんど同じですが。
私のはエラー処理もしっかりと組み込んであります。

G4=｛＝IF(COUNTIF(C:C,E$3) ,INDEX(A$4:A$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(A1))))｝

H4=｛=IF(COUNTIF(C:C,E$3) .INDEX(B$4:B$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(B1))))｝

97文字ですが、エラー処理を外せば
{=INDEX(B$4:B$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(B1)))}
65文字です。

[投稿情報: ますたあ]

短縮バージョン
G4=
｛=INDEX(A$4:A$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(B1)))｝

H4＝
｛=INDEX(B$4:B$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(B1)))｝

[投稿情報: minmax]

ますたあさん
どのような入力＆書式設定？？
私がやるとエラー表示？？

[投稿情報: からくち]

この問題は
＞この問題は配列数式を理解（解る）する上での
＞ある意味、基本中の基本のような・・・
と言うことで似たような式になってしまうと思います

：{=INDEX(A:A,1/MAX(1/30,LARGE((C$4:C$28=E$3)/ROW($4:$28),ROW(A1))))}

：{=INDEX(A:A,SMALL(FIND(C$4:C$28,E$3&C$4:C$28)*ROW($75:$99),ROW(A1))-71)}

：{=INDEX(A:A,SMALL((2-COUNTIF(E$3,C$4:C$28))*ROW($75:$99),ROW(A1))-71)}

：{=INDEX(A:A,SMALL((2-(C$4:C$28=E$3))*ROW($75:$99),ROW(A1))-71)}

：{=INDEX(A:A,2/LARGE((2-(C$4:C$28<>E$3))/ROW($75:$99),ROW(A1))-71)}

：{=INDEX(A:A,SMALL(TEXT(SUBSTITUTE(C$4:C$28&ROW($4:$28),E$3,),"0;;;!9!9")*1,ROW(A1)))}

：{=INDEX(A:A,SMALL(IF(C$4:C$28=E$3,ROW($4:$28),30),ROW(A1)))}

＞なんか数量合計バージョンの
＞解答がでてきそう・・・

Ｇ列
：{=INDEX(A:A,1/MAX(1/30,LARGE((FREQUENCY(IF(C$4:C$28=E$3,
MATCH(A$4:A$28,A$4:A$28,)),ROW($1:$24))>0)/ROW($4:$28),ROW(A1))))}

：{=INDEX(A:A,SMALL(IF(C$4:C$28=E$3,IF(MATCH(A$4:A$28,
IF(C$4:C$28=E$3,A$4:A$28),)=ROW($1:$25),ROW($4:$28),30),30),ROW(A1)))}

Ｈ列
：{=SUM((A$4:A$28=G4)*(C$4:C$28=E$3)*B$4:B$28)}

あそこをこう直せばもう少し短くなる等の突っ込みを入れないで下さい
この２式、同じ文字数に合わせてあります（お遊び序に）

この様に書き綴って改めて見ると
どの式にもＩＮＤＥＸとＬＡＲＧＥかＳＭＡＬＬが使われている

それならこの３関数を使用禁止にしてもう１つ作ってみよう

：=LOOKUP(ROW()-4,COUNTIF(OFFSET(C$1,,,ROW($1:$28)),E$3),A$2:A$29)

[投稿情報: y sakuda]

少し前に帰ってきて眺めてました。
基本的には同じようなものになりましたね・・・・
でも、kirさんの配列数式じゃないの、良く理解できてませんがやはりこう言うのできるんですね。感心しました。

ますたーさんの、申し訳ないですが、私も動きません。良く分からないので直せません。
くまぷーさんに完成版のファイル送ってアップしていただいた方が良いかも＾＾；

＞ある意味、基本中の基本のような・・・
そう見たいですが、時々見かけるのですが、覚えてないｗ

で、これからからくちさんの研究しますが・・・・
>３関数を使用禁止
想像もつきませんでした＾＾；
すごい！

[投稿情報: からくち]

＞kirさんの配列数式じゃないの、良く理解できてませんがやはりこう言うのできるんですね。
勘違いされないで下さい
Ctrl + Shift + Enterの代わりにＩＮＤＥＸを使っているだけです
全く同じ配列です

＞ますたーさんの、申し訳ないですが、
どの式をさしているのかな・・・

[投稿情報: minmax]

念のため
C列すべておんなじ人
注文主空欄
もありですよね？

[投稿情報: y sakuda]

>Ctrl + Shift + Enterの代わりにＩＮＤＥＸを使っているだけです
私はIndex使っても、所謂Ctrl+Shift+Enter
を避けられなかった・・・・・・・

>どの式をさしているのかな・・・
エラーチェックをしていると言う長いバージョンです。
先の方が#Numになっちゃう＾＾；
IFで文字列にしているのがまずいんだと思うのですが・・・・

[投稿情報: y sakuda]

>C列すべておんなじ人
>注文主空欄
>もありですよね？
そのつもりでした。
確認してみたら、私の手抜きの58は空欄にするとダメでしたね＾＾；

[投稿情報: y sakuda]

からくちさんのLookup
実は私も最初Countifで注文主の数を数えて、その数の変わったところをピックアップしてと言う発想もやってたのですが、完成できませんでした。
あきらめて消しちゃったのでどういう形か良く分からないのですが、少なく共Lookupは思いついてなかったです。

見せられるとなるほどと思いましたが・・・・・

[投稿情報: y sakuda]

からくちさんの重複を合計するバージョン・・・
ぱっと見では理解できませんが、感激もんですねーー
すごすぎる＾＾

[投稿情報: からくち]

＞Lookupは思いついてなかったです。
若しかしたら
｛=INDEX(A:A,MATCH(ROW(A1),COUNTIF(OFFSET(C$1,,,ROW($1:$25)),E$3),))｝
とした方が一般的かも知れませんが
これですと
{=IF(COUNTIF(C:C,E$3) などのようにエラー処理も必要になってしまいますので
わたしは結構多様しているＬＯＯＫＵＰをつい使ってしまいます

＞長いバージョンです。
ＩＦのエラー処理に書き落としありますね
のような

[投稿情報: からくち]

消えちゃってる

＞長いバージョンです。
ＩＦのエラー処理に書き落としありますね
＜ＲＯＷ（Ａ１）、””、
のような

[投稿情報: y sakuda]

そうか。
{=IF(COUNTIF(C:C,E$3) なら上手く行きますね＾＾

[投稿情報: からくち]

＞{=IF(COUNTIF(C:C,E$3) などのように

{=IF(COUNTIF(C:C,E$3)＜ROW(A1),"",INDEX(A:A,MATCH(ROW(A1),COUNTIF(OFFSET(C$1,,,ROW($1:$25)),E$3),))｝

などのように

[投稿情報: y sakuda]

失敗しちゃったｗ
改めて
{=IF(COUNTIF(C:C,E$3)＜ROW(A1),"",INDEX(A$4:A$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(A1))))}
なら上手く行きますね。

[投稿情報: かず]

私は短縮しきれず、６０文字でした。

｛=INDEX(A:A,SMALL(IF(C$4:C$28=E$3,ROW($4:$28),99),ROW(A1)))｝

[投稿情報: minmax]

かなりのみなさんがＨ列の方は対応していないのかな？？

[投稿情報: からくち]

＞所謂Ctrl+Shift+Enterを避けられなかった・・・・・・・

＞{=INDEX(A:A,29-LARGE(($C$4:$C$28=$E$3)*(26-ROW($1:$25)),ROW()-3),1)}
：=INDEX(A:A,29-LARGE(INDEX(($C$4:$C$28=$E$3)*(26-ROW($1:$25)),),ROW()-3),1)

＞{=INDEX(A:A,SMALL(9^($C$4:$C$28<>$E$3)*ROW($4:$28),ROW(A1)))}
：=INDEX(A:A,SMALL(INDEX(9^($C$4:$C$28<>$E$3)*ROW($4:$28),),ROW(A1)))

＞{=INDEX(A:A,SMALL(9^($C$4:C28<>$E$3)*ROW($4:28),ROW(A1)))}
：=INDEX(A:A,SMALL(INDEX(9^($C$4:C28<>$E$3)*ROW($4:28),),ROW(A1)))

[投稿情報: む印]

からくちさんの式は、どれもこれも凄いですねー、

特に重複削除の式は、きっと何かでまた役に立つから
ちゃんと勉強して理解せねば・・・

＞３関数を使用禁止
LOOKUPって便利なんですねー、
からくちさんのお陰で少しずつ理解できるようにはなってきましたが、、
COUNTIFの範囲指定でOFFSET使って配列にするアイデアが在ってこそ！
こういうテクニックも覚えておかないと・・・φ(..)メモメモ

[投稿情報: くまぷー]

みなさん、今帰って来ました。
遅れてすみません。
今回は（も）手も足も出ませんでした。

それにしても、からくちさんの
数量合計バージョンはすごい！！
びっくりです。

[投稿情報: くまぷー]

ますたあさん、

＞くまぷーさんに完成版のファイル送ってアップしていただいた方が良いかも＾＾；

宜しければファイルを送って下さい。
アップ致します。

[投稿情報: くまぷー]

ますたあさんの・・・

H4=｛=IF(COUNTIF(C:C,E$3) .INDEX(B$4:B$28,SMALL(IF(C$4:C$28=E$3,ROW($1:$24),""),ROW(B1))))｝

INDEXの前が「.」になっているだけで
「,」に直せば動きますね。
商品のない欄はエラー表示になりますけど・・・

[投稿情報: ジョー３]

今見ました。・・・・・・・・

>みなさん、今帰って来ました。
>遅れてすみません。
>今回は（も）手も足も出ませんでした。

ふふふ・・・・・
手と、足どころか・・・・
ふふふ・・・・・
口も出ませんでした。
（風邪のせいにしておく・・・・）

・・・・

[投稿情報: からくち]

＞LOOKUPって便利なんですねー、
そう思います
で、つい多用してしまいます
Ctrl + Shift + Enterも必要無いように出来ますし

これをＶＬＯＯＫＵＰを使うと
：｛=IF(COUNTIF(C:C,E$3) CHOOSE({1,2},COUNTIF(OFFSET(C$1,,,ROW($1:$28)),E$3),A$1:A$28),2,))｝

一目瞭然

[投稿情報: からくち]

上記訂正

：｛=IF(COUNTIF(C:C,E$3)＜ROW(A1),"",VLOOKUP(ROW(A1),CHOOSE({1,2},COUNTIF
(OFFSET(C$1,,,ROW($1:$28)),E$3),A$1:A$28),2,))｝

[投稿情報: y sakuda]

からくちさんの、Lookup,Vlookup及び重複ありバージョン正直なところ理解しきれてません・・・・
明日以降じっくり研究します＾＾；
しかし、それにしてもからくちさんの引き出しの多さには敬服します。

[投稿情報: くまぷー]

・・・ところで第22回はジョー３さんの予定なんですが
出題の準備は出来ていますでしょうか？

＞２０回む印さん
＞２１回sakudaさん

＞の予定で

＞ジョー３さんは２２回ですね。

＞その後、からくちさんが続きます。

問題の送付、準備ができましたら
よろしくお願いします。m(__)m

[投稿情報: ますたあ]

皆さん、大変ご迷惑をおかけしました。
申し訳ないです。

昨夜、酔っ払ってアップしようとしましたら
うまく表示できなかったんですね。
でっ、いじってたら何とか表示できたんですが
ちょっと変だな～とは感じましたが、まあいいかなんて．．．。m(__)m

エラー処理のところが切れてしまったんですね。^^;

再度、アップしてみます。

やってみましたが、なぜかうまく表示されません。
くまぷーさんとこにファイルを送ります。
よろしくおねがいします。

[投稿情報: くまぷー]

ますたあさん、わざわざすみません。
さっそく、アップさせて頂きました。
みなさん、ご覧下さい。m(__)m

[投稿情報: y sakuda]

私は配列数式は実用では全く使ってませんので、概念を分かってるだけでこなれてないことは自覚してましたが、今回のでつくづく分かってないことを痛感しました＾＾

からくちさんのVlookup版ですが、今朝改めて眺めて理解できました。
私は{1,1,1,2,2,2・・・・・}とならんだ、最初の１とか２のポジションを取得する手段がMatchしか思いつかず、Matchはなぜか配列数式の中でつかうと思うように機能せず断念したのですが、Chooseで配列を組み立ててVlookupとは・・・・・
同じ発想でもLookupの方がスマートなんですが、このVlookupを使えるようにした発想もうなっちゃいました。

わたしがやろうとしていたのを記憶で復元して、教えていただいたテクニックを使ってやってみた所上手く行きますね。
からくちさんのVlookupをMatchに変えただけの形ですが、エラー対応はしてません。
=INDEX(A:A,MATCH(ROW(A1),INDEX(COUNTIF(OFFSET($C$1,,,ROW($1:$28)),$E$3),),))

#Frequency使った重複版、未だ納得できてないです。今晩もう一度考えます＾＾；

[投稿情報: む印]

>ちゃんと勉強して理解せねば・・・
ということで
>{=INDEX(A:A,1/MAX(1/30,LARGE((FREQUENCY(IF(C$4:C$28=E$3,
>MATCH(A$4:A$28,A$4:A$28,)),ROW($1:$24))>0)/ROW($4:$28),ROW(A1))))}
を検証してて、何故わざわざLARGEを使われたのか・・・？

・・・で、やっと気づきました、「LARGEでも正順で表示する方法」
も示されたんだという事に！

[投稿情報: y sakuda]

からくちさんのFrequencyバージョン・・・・やっと理解できまっした。
芸が細かい・・・・・とても書ける気がしないです＾＾；

ところで、ジョー3の問題届かないんですかね？
ひょっとすると今週お休みになるのかな？

[投稿情報: くまぷー]

>ところで、ジョー3の問題届かないんですかね？

まだ、届きません。
何のコメントもないところをみると
ここも見ていないようですね。
今日は何らかのコメントがあるかとおもうのですが・・・

[投稿情報: くまぷー]

>ひょっとすると今週お休みになるのかな？

いや、そのときは・・・
からくちさんのを使わせてもらうつもりです。

[投稿情報: supermab]

今回のは、ホント難しかった・・・
配列数式を理解してないことを痛感！

それにしても、
ジョー３さんは
だいじょぶか？

心配なのだ！

[投稿情報: jyo-3]

あああ・・・・
すんませーーん

今送ります。
（もちろん次回に回っても結構です）

[投稿情報: jyo-3]

一応送りました。

で、

文字の間違い探し第2弾です。
1問目は、やってみました。
2問目は、やってません、、ははは、、、
（できないかも、わたし）

[投稿情報: くまぷー]

今、帰って来ました。
これから、食事をしてそれから
ジョー３さんから頂いた問題をあっぷします。

ジョー３さん、ありがとうございました。m(__)m