☰

Excel で行の右端の値を取得するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel
Excel で行の右端の値を取得するにはどうすればよいですか?

配列内の各行の右端の空白でないセルの値を表示する必要があります。 Excel でこれをどのように実現できますか?

この例のテーブルでは、[Current] 列に目的の結果が含まれています。

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

テーマのバリエーションは、左端、上端、下端の値、またはより大きい値になります。んなど。バージョンが該当する場合は、Office 2016 のデスクトップ Excel。

答え1

ここに画像の説明を入力してください

  • この数式を入力しE2て下へ記入してください。

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

使い方：

  • 数式は、ルックアップ値が2 ルックアップ ベクトルに表示されるどの値よりも意図的に大きいことを認識します。
  • 式は値A2:C2<>""の配列を返します 。TrueFalse
  • 1次に、この配列で除算され、1 またはゼロ除算エラー (#DIV/0!) で構成される新しい配列が作成されます: {1,0,1,...}。
  • この配列はルックアップ ベクトルです。
  • 数式が検索値を見つけられない場合は、
    Lookup次に小さい値と一致します。
  • この場合、ルックアップ値は です2が、ルックアップ配列内の最大値は なので、ルックアップは配列内の1最後の と一致します。1
  • LOOKUP は、同じ位置にある値である、結果ベクトル内の対応する値を返します。

:編集済み:

  • Google スプレッドシートの場合、使用する数式は次のとおりです。

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • 最後にCtrl+Shift+Enter式は次のようになります。

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

答え2

この問題にはすでに複数の解決策がありますが、ここでは私が好む解決策を紹介します。私にとってはこれが自然な考え方に最も近いものです。

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- これは配列数式なので、入力後にCTRL+ SHIFT+を押します。ENTER

ここに画像の説明を入力してください

使い方：

  • IF(A2:C2="","",COLUMN(A2:C2))- 行内の各セルについて、セルが空の場合は空の文字列を返し、そうでない場合は列番号を返します。
  • MAX( ... )- 返された列番号の最大値を選択します
  • =INDEX(A2:C2, ... )- 最も高い列番号に基づいて行からセルを選択します

警告: 範囲が最初の列から始まる場合にのみ正しく機能します。それ以外の場合は、シフトを補正する必要があります。たとえば、範囲が列 C から始まる場合:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

答え3

テーブルが C2:F12 にレイアウトされ、ヘッダー行が行 2、集計列が F であると仮定します。次の数式を F3 に入力して、下にコピーします。

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

有価証券

注記：

  • AGGREGATE は、数式選択肢 14 および 15 を使用して配列操作を実行します。したがって、AGGREGATE 関数内で完全な列/行参照を使用しないでください。実行される計算の数によってシステムが遅くなったりクラッシュしたりする可能性があります。配列型関数の外部で完全な列参照を使用することは問題ありません。INDEX に 3:3 が使用されていることに注意してください。

  • 新しい列を挿入する場合、列 F を選択して挿入を実行すると、F の数式を更新して、C3:F3 が新しい範囲になるようにする必要があります。列 E を選択して新しい列を挿入すると、範囲は自動的に更新されますが、データは間違った列に入ります。列 F を空白のままにして、代わりに列 G に数式を配置し、AGGREGATE 内の範囲として C3:F3 を使用すると、将来、挿入に列 F を選択すると、数式が更新され、F に新しいデータを入力できます。翌年もこのプロセスを繰り返すために選択できる空白の列が右側に残ります。

答え4

もう 1 つのアプローチは、洗練されておらず、より粗雑ですが、簡単に理解できる TEXTJOIN() を使用することです。

最初の行に A2:C2 を使用して、次の内容を D2 に入力し、コピーして貼り付けます。または、塗りつぶし、または... アイデアはおわかりでしょう。

のためにテキスト結合文字列以下では、TEXTJOIN() 関数を使用して、調べたいセル範囲全体を連結します。文字列を短くするために空白を省略するには「TRUE」を使用し、区切り文字には、データに実際に出現することのない文字を使用します。以下では「Ŧ」を使用します (最後の文字を「Ų」に置き換える文字として)。TEXTJOIN() やその関連関数でよく使用されるカンマを使用すると、問題が発生する可能性があります。

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

理解しやすくなります。SUBSTITUTE()は、実例 ＃これにより、区切り文字の最後の使用箇所を見つけることができます区切り文字付きのテキスト結合文字列最後の行では、区切り文字ありとなしのTextjoin文字列のLEN()を計算し、減算して差を求めます。これが区切り文字の数であり、したがって実例 ＃必要なもの。

最後から 2 番目の行では、そのインスタンスを別の文字に置き換え、FIND() を使用して文字列内の位置を取得します。

2 行目では、文字列全体の LEN() からその位置を減算して、その後に続く文字数を調べます。これにより、作成した文字列の右側から削除する文字数がわかります。

最初の行はまさにそれを実行し、範囲内の最後のセルの内容を残します。

Excel が使用する文字列の長さは関数によって異なります。たとえば、6,000 ～ 7,000 の範囲のものもあれば、32,000 程度のものもあります。これを念頭に置いて (これが「TRUE」を指定する理由です)、A2:C2 ではなく、非常に大きな範囲を指定できます。

セルではなく結合された文字列を操作していることに注意してください。

  1. 携帯電話のアドレスなどを探す必要はありません。
  2. 実際に、結合された「サブ」範囲で構成された範囲や、完全に分割されたセルで構成された範囲でこれを使用できます。不連続な範囲はあなたの味方です。

数式内の Excel によって評価される部分内にデータが存在する方法により、チャンクを名前付き範囲に分割すると問題が発生する場合と発生しない場合があります。数式を評価する Excel によって作成および使用される中間結果は、名前付き範囲が提示する最終結果とは異なる形式になる可能性があるため、将来の使いやすさのために数式のロジックをレイアウトするために部分に名前付き範囲を使用できない場合があります。ただし、上記のいずれにもその問題は発生しないため、たとえば TEXTJOIN の名前付き範囲を作成し、残りをネイティブに入力して、セルをクリックするユーザーがロジックを確認できるようにすることができます。または、部分を "InstanceNumber" (名前付き範囲) などの論理的なものに分割して、さらに読みやすくします。作成してから、すべてを名前付き範囲にダンプします。または、名前付き範囲をまったく気にしないでください。

私が言うように、洗練されていません。一部のソリューションよりも長いですが、一部のソリューションのように本当に「粗雑」ではありません。ヘルパー列や、ユーザーが使用できないもの、または使用しないものはありません。{配列} 数式はありません。

(また、必要に応じて不連続な範囲を使用することもできます。) このアプローチでは、レポート エンジンが PDF 化し、Excel に抽出した大量のテキストとデータを取得することもできますが、関連するセットごとにセルに別々にチャンク化されます (つまり、10 人のクライアントに関する情報がそれぞれ 10 列 x 13 行のブロックに設定されますが、1 つのクライアントのアドレスはセル 4、6 にあり、もう 1 つのクライアントのアドレスはセル 3、8 にありますが、同じフローに従い、インポート時に異なるセルに入力されるだけです)。これを 1 つの文字列にすることで、公式に部分を検索できます。多くの場合、少なくともそうです。または、セルのブロックを取得し、マクロや配列ではなく関数を使用して、ブロック内の各セルに 1 つのヘルパー セルを使用して、ブロック内のどこかにデータが少し表示されるかどうかを確認します。

関連情報