シフトのリストの横に名前のリストがあります。各シフトの従業員のリストを日ごとにまとめ、自動的かつ動的に更新したいと考えています。最適な方法がわかりません。
メインの勤務表には、従業員名の列があり、その後にシフトの列が 14 列続きます。次に、最初のテーブルで誰がどのシフトに就いているかに基づいて、名前のリストを 14 個作成します。
例: メイン範囲には以下が含まれます:
A B C D E
---------- --- --- --- ---
1 Joe Blogs E E L O
2 Jill Bleg L L E E
3 Geoff Ted O L L L
次に、たとえば列 D を検索し、L の最初のインスタンスを検索して Joe Blogs をリストし、次に L の 2 番目のインスタンスを検索して Geoff Ted をリストする、というようにしたいと思います。
セル範囲内で文字列の N 番目のインスタンスを検索する方法はありますか?
答え1
これは見た目ほど簡単ではありません。まず、列 D で「L」が出現するすべての行番号を見つけるには、次のようにします。
=IF(D1:D3="L"; ROW(D1:D3))
保存する配列数式: 入力後、Ctrl+Shift+Return（Macの場合はCommand+Shift+Return）を押します。見せる中括弧で囲みます。結果として 1 つのセルではなく、作業している範囲と同じ数のセルが返されます。上記の例では、値が 1、空白、3 の 3 つのセルが返されます。
次、使用SMALLN番目の値を見つけるには:
SMALL(numberlist; n)（順序付けられていない）数値の範囲または配列内で
n番目に小さい数値を返しますnumberlist。
上記を適用するとSMALL、結果として単一のセルに戻ります。
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)
それでも、結果は単一のセルであるにもかかわらず、Ctrl + Shift + Return を使用して保存する必要があります。
行番号がわかれば、INDEX最初の列にある名前を見つけることができますA1:A3。
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)
繰り返しますが、これらすべては Ctrl + Shift + Return を使用して保存する必要があります。
ただし、このような数式をドラッグして他のセルに拡張することはできません。ランク「1」と「2」が自動的に「3」になるわけではないためです。代わりに、数式がある行に基づいて必要なランクを計算するには、次のようにします。
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)
これを配列数式として 1 行目のどこかに保存した後、下にドラッグして 2 行目、3 行目などに追加することができます。
あるいは、結果を拡張する代わりに、配列数式をコピー/貼り付けして、列と行の参照をその場で調整することもできます。下のスクリーンショットでは、B7、B12、B17 から次の内容を他のセルにコピーしました。
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)
配列数式のキーボード ショートカットは、数式を実際に変更した後にのみ機能することに注意してください。単に Return キーを押した後、再度数式に入り、Ctrl + Shift + Return キーを押しても効果はありません。
また、配列数式を拡張したら、その数式を変更するにはすべての結果セルを選択する必要があります。そうしないと、「配列の一部だけを変更することはできません」。