☰

如何取得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  |
+---------+----------+---------+----------+

主題的變化將是最左邊、最頂端、最底部的數值；或值大於n等等。

答案1

在此輸入影像描述

  • 輸入此公式E2並向下填寫。

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

怎麼運作的：

  • 公式識別 的查找值2 故意大於查找向量中出現的任何值。
  • 此表達式A2:C2<>""傳回由 True和False值組成的陣列。
  • 1然後除以該數組並建立一個由 1 或除以零錯誤組成的新數組 (#DIV/0!)：{1,0,1,...}。
  • 此數組是查找向量。
  • 當公式找不到查找值時，則會
    Lookup符合下一個最小值。
  • 在本例中，查找值是2，但查找數組中的最大值是，因此查找將匹配數組中的1最後一個值。1
  • LOOKUP 傳回 Result Vector 中對應的值，即同一位置的值。

：編輯：

  • 對於 Google Sheet，這是要使用的公式：

    =(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。

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

POC

筆記：

  • AGGREGATE 使用公式選擇 14 和 15 執行陣列運算。在數組類型函數之外使用完整列引用是可以的。請注意 INDEX 使用的 3:3。

  • 插入新列時，如果選擇 F 列並執行插入，則需要更新 F 中的公式，以便 C3:F3 成為新範圍。如果您選擇了 E 列並插入了新列，則範圍將自動更新，但現在您的資料位於錯誤的列中。如果您將F 列留空，將公式放在G 列中，並使用C3:F3 作為AGGREGATE 內的範圍，那麼將來您可以選擇F 列進行插入，您的公式將更新，您可以在F 中輸入新數據您將在右側仍有一個空白列，以供明年選擇以重複該過程。

答案4

另一種方法雖然不優雅、更粗暴，但很容易理解，那就是使用 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()，並以減法求出差異。這是分隔符號的數量，因此實例 ＃你需要。

在倒數第二行中，您用不同的字元取代該實例，然後使用 FIND() 取得其在 strng 中的位置。

第二行從字串的整個 LEN() 中減去該位置，以找出其後面有多少個字元。這告訴您要從您建立的字串右側刪除多少個字元。

第一行就是這樣做的，為您留下該範圍內最後一個單元格的內容。

Excel 將使用的字串長度因函數而異，有些在 6-7,000 範圍內，例如，有些更像 32,000。考慮到這一點（這就是為什麼你指定「TRUE」），人們可以做一個巨大的範圍而不是 A2:C2。

請注意，您隨後使用的是連接的字串，而不是單元格，因此：

  1. 您永遠不必查找單元格地址等。
  2. 實際上，您可以在由連接的“子”範圍組成的範圍以及由真正分解的單元格組成的範圍上使用它。不連續的範圍是你的朋友和盟友。

由於資料存在於公式內由 Excel 計算的片段內的方式，將區塊分解為命名範圍可能會導致問題，也可能不會，因為 Excel 計算公式時創建和使用的臨時結果可能與公式的形式不同。結果是命名範圍向前呈現，有時不能在片段上使用命名範圍來佈置公式的邏輯以方便將來。但上面的內容並沒有出現這個問題，因此您可以為 TEXTJOIN 建立命名範圍，並以本機方式輸入其餘部分，以便單擊單元格的任何人都可以看到邏輯。或將各個部分分解為諸如“InstanceNumber”（命名範圍）之類的邏輯內容，以便更容易閱讀。建立它，然後將其全部轉儲到命名範圍中。或者根本不用擔心命名範圍。

正如我所說，不優雅。比某些解決方案更長，但不像某些事情那樣「野蠻」。沒有輔助列或其他人們經常無法使用的東西。或不會。沒有 {array} 公式。

（並且您可以在需要時使用不連續的範圍。）該方法還可以採用報表引擎PDF 的一堆文本和數據，然後將其提取到Excel，但針對每個相關集以不同的方式分塊到單元格中（因此大約10 個客戶端的資訊） ，每個集合都在10 列x 13 行區塊中，但其中一個的位址位於儲存格4,6 中，而另一個集合的位址位於儲存格3,8中，但它遵循相同的流程，只是在導入時填充不同的單元格）並將其設為單字串，讓您可以公式化地尋找零件。無論如何，經常如此。或使用一個儲存格區塊，並使用函數（而不是巨集或陣列或區塊中每個儲存格的輔助儲存格）來查看是否有一位資料出現在其中的任何位置。

相關內容