…COUNT/IF/VSTACK関数の併用で対応する方法 範囲の数だけCOUNTIF関数を繰り返して足す方法 次の画像ではH3セルに式を入力し、左側の表のうち英語と国語の列（C,E列）にある「A」の数をカウントしています。 H3セル =COUNTIF(C3:C7,"A")+COUNTIF(E3:E7,"A") 単純にそれぞれの列をCOUNTIF関数でカウントして足し算しています。 もちろんカウントする各範囲の行数や列数は異なっていても問題ありません。 1つのCOUNTIFで複数…

…KUP(TRUE,HSTACK((B3:B8=F3)*(C3:C8=G3)>0,D3:D8),2,FALSE) ちょっと複雑な式ですが「(B3:B8=F3)*(C3:C8=G3)>0」の部分により、左側の表のうち両方の条件を満たす行をTRUE、そうでない行をFALSEに変換した配列を生成しています。そしてHSTACK関数によりこの配列とD列とつないだ2列の表を生成しています。 あとはVLOOKUP関数によりこの表からTRUEを検索し、対応する2列目の値（D列の値）を返すという…

…LOOKUP関数とHSTACK/EXACT関数を併用することで、大文字と小文字を区別して検索できます。 手順 XLOOKUP関数で対応する INDEX/MATCH関数で対応する 手順 次の画像では、E3セルに入力された検索値「abc」を左側の表から検索して対応する商品名「ぶどう」を取得しています。 普通にVLOOKUP関数を使うと「Abc」にマッチするものと判断され「さくらんぼ」を取得してしまいますが、他の関数を併用することで大文字と小文字を区別した検索を行っています。 F3…

…ントです。 さらにHSTACK関数と組み合わせて飛び飛びの列の合計を求められます。 SUMIF関数だと引数に関数が使えないためできませんが、こちらの方法では可能になります。 HSTACKが使えなければ「=SUM(IF(B3:B7=H3,C3:D7,0),IF(B3:B7=H3,F3:F7,0))」のようにすることもできます。SUMIFで1列ごと足し算するよりマシですが、やはり対象範囲が増えると式が長くなりやすくHSTACKを使うのに比べて分が悪いです。 余談になりますが、スピ…

…LOOKUP関数とHSTACK関数を併用すれば検索範囲の列よりも左側の値を取得できます。 ただしVLOOKUPにこだわる理由がなければXLOOKUP関数の使用をおすすめします。 手順 代替案 XLOOKUP関数を使う方法 INDEX/MATCH関数を使う方法 手順 次の画像ではF3セルに入力された商品IDをD列から検索し、一致する位置に対応する商品名をB列から取得しています。 G3セル =VLOOKUP(F3,HSTACK(D3:D7,B3:B7),2,FALSE) 「HST…

…する範囲）を横に広げただけです。 FILTER関数は条件を課す列数と抽出する列数が一致してなくてもいい（SUMIFやSUM+IFの組み合わせではここが難しい）ので簡単にこのようなことができます。 また、合計する数値が飛び飛びでも合計できます。 J3セル =SUM(FILTER(HSTACK(E3:E11,G3:G11),(C3:C11="東京都")+(D3:D11>=1980))) HSTACK関数を使って合計する2つの列だけを抽出し、それにFILTER関数を適用しています。

…り使用可能となったVSTACK/HSTACK関数の使い方についてです。 「=VSTACK(範囲1,範囲2)」という式で、2つの範囲を縦に結合できます。3つ以上の結合も可能です。 同様にHSTACK関数では複数の範囲を横に結合した結果が得られます。 従来の「={範囲1;範囲2}」や「={範囲1,範囲2}」といった結合の仕方と異なり、列数（行数）が異なる範囲を結合できる特徴があります。 機能と構文 基本的な使用例 従来の配列記法との違い 機能と構文 VSTACK関数は指定した複数…

…FILTER関数とHSTACK関数を併用すれば、HSTACK関数で指定した列だけを対象としてFILTER関数を実行できます。 抽出する列の見出しを用意しておき、CHOOSECOLS/XMATCH関数と併用する方法もあります。 抽出内容について HSTACK関数を併用する方法（列見出しを参照しない） CHOOSECOLS/XMATCH関数を併用する方法（列見出しを参照する） 備考 抽出内容について 画像にある5つの列を持つ表のうち、赤い枠で囲んだ3つの列だけを取得対象とし、条件…

…「=UNIQUE(VSTACK(列1,列2,…))」とする方法もあります。列が飛び飛びに離れている場合はこちらを使います。手順 次の画像ではE2セルに「=UNIQUE(B2:C5)」と入力しています。 UNIQUE関数で複数列の範囲を指定した場合は行全体（列の組み合わせ）で重複を判定して重複する行を除くので、この場合は指定した範囲の内容がそのまま返ってきます（2つの列で重複する組み合わせがない）。 そこで次の画像では「=UNIQUE(TOCOL(B2:C5))」としました。 …

…つの式の第3引数をHSTACKで１つにまとめれば1つの式で2列出力できます。ただし検索値（担当者名）を複数与えたときに左の列（日）しか返りません。FILTER/TOCOL関数を使う方法 上記の方法中のXLOOKUP関数をFILTER関数に簡単に置き換えれば、検索値（担当者名）が複数存在するときに、対応する見出しをすべて抽出することができます。 （※H3セル以降には表示形式「日付」を適用しています） H3,I3セル =FILTER(TOCOL(IF(B3:B6&C2:E2<>"…

…BDA(b,c, HSTACK(b,MIN(INDEX(a,c)+(MID(B3,c,1)<>MID(C3,INDEX(b,1),1)),INDEX(a,c+1)+1,INDEX(b,c)+1)) ))))), -1) いわゆる動的計画法（DP）に基づく式となっています。 この方法に関しては式を各セルに分散させて表を完成させる方が簡便でExcelの本質に即しているとは思いますが、ここではあえて1セルで完結させています。 基本的な手順については「具体例で学ぶ数学」さんの記事が参…

…(B3,b),a,VSTACK(a,b,B3/b))) ) ))) 「1」をaの初期値（配列）にしておき、「1から『B3の平方根（小数切捨）』までの整数の配列」をbとしています。 bの値をそれぞれチェックし、B3の約数であったら（B3をbで割った余りが0だったら）bとB3/b（いずれもB3の約数）を配列aに追加していきます。 約数には重複するものも出るのでUNIQUEで重複を除き、ソートと転置を行って完了です。 SEQUENCEで生成する配列については2からの「SQRT(B3…

…MBDA(a,b,VSTACK(a,XLOOKUP(b,B3:B8,C3:E8,"")))), 1) MAP/LAMBDAで済みそうにも見えますがXLOOKUPとの相性が悪く、エラーになってしまいます。 そこでREDUCE/LAMBDAを使っています。VSTACK関数を併用し、G3:G5（=b）の各値に対するXLOOKUP関数の実行結果を縦にスタックしています。 最初の行に無駄な空文字列が残りますので最後にDROP関数で削除して完成です。この辺りはISOMITTEDなどを使っ…

…で、REDUCE/VSTACK関数を使い出力を積み重ねていきます。無用な初期配列を設定して削除する作業が生じるのが残念な点です。 なお、対象となるセルに空白セルや空文字列が含まれていると全体が1つのエラーになります。FILTER関数等で対策は可能ですが不必要に長くなりそうなので省略します。 D3セル =DROP(IFNA( REDUCE("",B3:B6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"☆")))), ""),1) REDUCE/LAMBDA…

…( REDUCE(HSTACK("",B3),C2:L2, LAMBDA(a,b,HSTACK(DROP(a,,-1),INT(TAKE(a,,-1)/b),MOD(TAKE(a,,-1),b))) ),,1),,-1) 基本的には金額を10000で割った商（小数切捨。以下同様）を求め、その余りを5000で割った商を求め……ということを繰り返しています。 具体的な式の内容ですが、REDUCE関数とLAMBDA関数を使い、「HSTACK("",B3)」をa（の初期配列）、「C2…

…3,0)),1),HSTACK(OFFSET(B6,,,B3,2),MUNIT(B3))), TRANSPOSE(SORT(FILTER(x,INDEX(x,,2)<=C3),{1,2},{-1,1})) ) まず「SEQUENCE(2^B3-1)」により1から2^B3-1（ここでは2^7-1=127）までの値を生成し、BITRSHIFT/BITAND関数と併用することで{1,0,0,0,0,0,0}から{1,1,1,1,1,1,1}までの配列、つまりアイテム選択の全組み合わ…

…(1,4), x,HSTACK(B3:B14,VALUE(TEXTBEFORE(TEXTAFTER("."&B3:B14&".",".",n),".",1))), INDEX(SORT(x,n+1),,1) ) まず「.」を基準にしてそれぞれの文字列を区切りますが、TEXTSPLIT関数を使おうとすると（第1引数に配列を与えたときに）最初のオクテットしか返ってきません。 そこでTEXTBEFORE/TEXTAFTERで代用しています。「"."&B3:B14&"."」により文字…

…&C2:F2), HSTACK(TEXTBEFORE(x,"_"),TEXTAFTER(x,"_"),TOCOL(C3:F5)) ) まず「TOCOL(B3:B5&"_"&C2:F2)」の部分により、「池袋店_11月」というように店名と月名を組み合わせた文字列（下記参照）を縦1列（店名3つ×月4つ=12行）に並べた配列を生成しています。そしてLET関数により、これにxという名前を付けています。 次に「TEXTBEFORE(x,"_")」によりxのうち店名だけの列を、「TEXT…

…, REDUCE(HSTACK(1,B3),SEQUENCE(LOG(B3,2)),LAMBDA(a,b, LET(c,CHOOSECOLS(a,-1),d,SEQUENCE(SQRT(B3),,2),e,MIN(FILTER(d,MOD(c,d)=0,c)), HSTACK(TAKE(a,,b),e,c/e)))), FILTER(x,x>1) ) 基本的に試し割り法をやっていますが、細かいところはキリがないので要点のみ記したいと思います。 まず2行目の「HSTACK(1,…

…C3:C6)), HSTACK(TEXTBEFORE(x,"_"),TEXTAFTER(x,"_")) ) まず「B3:B5&"_"&TRANSPOSE(C3:C6)」の部分により、県名と品目を「_」でつないだ文字列を全通り（12個）生成しています（下記参照）。 そしてこれをTOCOL関数で縦1列に並べたものをLET関数によりxと名付けています。 さらにTEXTBEFORE関数とTEXTAFTER関数を使い、xの各項目を「_」の前後に分割した列をそれぞれ生成し、HSTACK関…

…す。 G3セル =HSTACK(TOCOL(IF(C3:E6="",NA(),B3:B6),2),TOCOL(IF(C3:E6="",NA(),C3:E6),2)) HSTACK関数を使って2つの列を並べています。 最初の「TOCOL(IF(C3:E6="",NA(),B3:B6),2)」の部分ですが、これは「氏名を、それぞれが持っている資格の数だけ繰り返した1列の表を作る」というものです。詳しくは下記の備考をご覧ください。 2番目の「TOCOL(IF(C3:E6="",NA…