リストを並べ替える必要がある場合は、さまざまな方法がありますが、最も簡単なのはタブまたはメニューの並べ替えボタンです。 且つ (データ — 並べ替え). ただし、リストの並べ替えを自動的に行う必要がある場合、つまり式があります。 これは、たとえば、ドロップダウン リストのデータを生成するとき、グラフのデータを計算するときなどに必要になる場合があります。
方法 1. 数値データ
リストに数値情報のみが含まれている場合、関数を使用して簡単に並べ替えることができます 少なくとも （小さな） и LINE （行）:
演算 少なくとも （小さな） 配列 (列 A) から行の n 番目に小さい要素を取り出します。 それらの。 SMALL(A:A;1) は列の最小の数値で、SMALL(A:A;2) は XNUMX 番目に小さい数値などです。
演算 LINE （行） 指定されたセルの行番号を返します。つまり、ROW(A1)=1、ROW(A2)=2 などです。この場合、単純に一連の数値 n=1,2,3… のジェネレータとして使用されます。私たちのソートされたリスト。 同じ成功で、追加の列を作成し、1,2,3、XNUMX、XNUMX の数値シーケンスを手動で入力して、ROW 関数の代わりに参照することができました。
方法 2. テキスト リストと正規の数式
リストに数値ではなくテキストが含まれている場合、SMALL 関数は機能しなくなるため、少し長い別のパスに移動する必要があります。
まず、関数を使用して、将来のソート済みリスト内の各名前のシリアル番号が計算される式を含むサービス列を追加しましょう COUNTIF (カウンティフ):
英語版では次のようになります。
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
第 XNUMX 項は、現在のセルより少ないセルの数をカウントする関数です。 XNUMX つ目は、任意の名前が複数回出現する場合のセーフティ ネットです。 その後、それらは同じではなく、連続して増加する数になります。
ここで、受信した番号を昇順に並べる必要があります。 このために、関数を使用できます 少なくとも （小さな） 最初の方法から：
さて、最後に、リストから番号で名前を引き出すだけです。 これを行うには、次の式を使用できます。
演算 もっと露出 （マッチ） 列 B で目的のシリアル番号 (1、2、3 など) を検索し、実際には、この番号が配置されている行の番号を返します。 関数 INDEX （索引） この行番号の名前を列 A から引き出します。
方法 3: 配列数式
実際、この方法は方法 2 と同じ配置アルゴリズムですが、配列数式によって実装されています。 数式を簡略化するために、セル範囲 C1:C10 に名前を付けました。 リスト (セルを選択し、 Ctrlキー+ F3 とボタン 創造する):
セル E1 で、数式をコピーします。
=INDEX(リスト; MATCH(SMALL(COUNTIF(リスト; "<"&リスト); ROW(1:1)); COUNTIF(リスト; "<"&リスト); 0))
または英語版では：
=INDEX(リスト, MATCH(SMALL(COUNTIF(リスト, «<"&リスト), ROW(1:1)), COUNTIF(リスト, "<"&リスト), 0))
そして押す Ctrl + Shift + Enter配列数式として入力します。 次に、結果の式をリスト全体にコピーできます。
式が固定範囲ではなく、リストに新しい要素を追加するときに調整できるようにしたい場合は、戦略を少し変更する必要があります。
まず、リスト範囲を動的に設定する必要があります。 これを行うには、作成時に固定範囲 C3:C10 ではなく、数値に関係なくすべての使用可能な値を参照する特別な数式を指定する必要があります。 クリック Altキー+ F3 またはタブを開く 数式 – ネーム マネージャー (数式 — ネーム マネージャー)、新しい名前を作成し、フィールドで リンク （参照） 次の数式を入力します (並べ替えるデータの範囲はセル C1 から始まると想定しています)。
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,ショット(C1:C1000),1)
第 XNUMX に、上記の配列数式は、将来追加のデータが入力されることを想定して、余裕を持って縮小する必要があります。 この場合、配列数式は、まだ入力されていないセルに対してエラー #NUMBER を返し始めます。 それを傍受するには、関数を使用できます IFエラーこれは、配列数式の「周囲」に追加する必要があります。
=IFERROR(INDEX(リスト; MATCH(SMALL(COUNTIF(リスト; "<"&リスト); ROW(1:1)); COUNTIF(リスト; "<"&リスト); 0));»»)
=IFERROR(NDEX(リスト, MATCH(SMALL(COUNTIF(リスト, «<"&リスト), ROW(1:1)), COUNTIF(リスト, "<"&リスト), 0));"")
#NUMBER エラーをキャッチし、代わりに void (空の引用符) を出力します。
:
- 範囲を色で並べ替える
- 配列数式とは何か、なぜ必要なのか
- 新しい Office 365 の SORT 並べ替えと動的配列