EXCEL 尋找並傳回右邊欄位值

linss520

linss520
個人積分：8分
文章編號：54900164

8分

樓主

2015-03-16 22:56

各位大大，請教：

EXCEL 2010
在一範圍內A1:F3，尋找並傳回右邊欄位值，
如找到"台南"，並傳回"9"，
"高雄"顯示"無資料"，
"台北"則傳回"1"的語法。

謝謝！

EXCEL 尋找並傳回右邊欄位值

2015-03-16 22:56 發佈

文章關鍵字 excel 右邊欄位值

無的放矢

無的放矢
個人積分：270分
文章編號：54920465

270分

2樓

2015-03-18 7:19

投機取巧的方法如下: (正常應該要分成3段sumif再加起來)
I2的公式為: =SUMIF($A$2:$E$4,$H2,$B$2:$F$4)

但話說回來,如果在其它地方,將原本6欄的資料改成2欄,就可以套用更多公式了.

linss520

linss520
個人積分：8分
文章編號：54948238

8分

樓主

2015-03-19 18:40

無的放矢 wrote:
投機取巧的方法如下...(恕刪)

謝謝！SUMIF函數的確可用。
這樣說：今有一大資料，範圍很大，各項資料長短不一，且又隨時可能異動，其結構如下：

若用VLOOKUP，則會以最左邊欄位為搜尋對象，傳回指定陣列的第幾欄位之值。但在此例中，可能會以A、C、E、G…等欄位來進行，不適用。
若用countif，是做計數，sumif是做加總。
在姓名欄裡資料是獨一性之下，sumif可達成所需。
但若姓名欄裡可能相同情況之下，且班級欄並非數值，可能所傳非所需。
在此例中sumif好像又行不通。

I3=SUMIF($A$2:$E$4,$H3,$B$2:$F$4)
無對映顯示 0 ，該如何才能顯示為〝無資料〞？
ISNA可以搭配SUMIF？

D17=SUMIF($A$8:$G$14,C17,$B$8:$H$14)
傳回的是文字資料又無法顯示，是哪出錯嘞？

無的放矢

無的放矢
個人積分：270分
文章編號：54954991

270分

4樓

2015-03-20 8:29

最簡單的方法還是之前提到的,sheet2重新整理(複製)一份2或3欄的資料:
　A欄　　　　　B欄　　　　C欄
1 姓名　　　　　班級　　　單位
2 簡O如　　　　1_1　　　　教務處
3 謝O惠　　　　1_2　　　　教務處
......
N 柯O好　　　　4_5　　　　輔導室

以下是我大致測試可行的其中一種公式,考量不周在所難免,
在D17儲存格先輸入:
=OFFSET($A$1,MAX(($A$8:$H$14=C11)*ROW($A$8:$H$14)-1),
MAX(($A$8:$H$14=C11)*COLUMN($A$8:$H$14)))
輸入完成再同時按下Ctrl+Shift+Enter.
最後會看到公式變成下面這樣"應該"就可以了.
{=OFFSET($A$1,MAX(($A$8:$H$14=C11)*ROW($A$8:$H$14)-1),
MAX(($A$8:$H$14=C11)*COLUMN($A$8:$H$14)))}

snare

snare
個人積分：3041分
文章編號：54966070

3041分

5樓

2015-03-20 19:03

改變表格會比較好…

如果用4樓的方式，公式可變成
(假設d2是要找的資料，e2要帶出資料右邊那格)

公式放在e2

=IF(ISNA(VLOOKUP(D2,A:B,2,FALSE))=FALSE,VLOOKUP(D2,A:B,2,FALSE),"找不到")

用樓主的方式…我暫時只想到這個又臭又長的方法
(以下同一行，公式放在e17)

=IF(ISNA(VLOOKUP(D17,$A$8:$B$14,2,FALSE))=FALSE,VLOOKUP(D17,$A$8:$B$14,2,FALSE)
,IF(ISNA(VLOOKUP(D17,$C$8:$D$14,2,FALSE))=FALSE,VLOOKUP(D17,$C$8:$D$14,2,FALSE)
,IF(ISNA(VLOOKUP(D17,$E$8:$F$14,2,FALSE))=FALSE,VLOOKUP(D17,$E$8:$F$14,2,FALSE)
,IF(ISNA(VLOOKUP(D17,$G$8:$H$14,2,FALSE))=FALSE,VLOOKUP(D17,$G$8:$H$14,2,FALSE),"找不到"))))

linss520 wrote:
I3=SUMIF($A$2:$E$4,$H3,$B$2:$F$4)
無對映顯示 0 ，該如何才能顯示為〝無資料〞？
ISNA可以搭配SUMIF？
...(恕刪)

i3=if(SUMIF($A$2:$E$4,$H3,$B$2:$F$4)=0,"無資料",SUMIF($A$2:$E$4,$H3,$B$2:$F$4))

linss520 wrote:
D17=SUMIF($A$8:$G$14,C17,$B$8:$H$14)
傳回的是文字資料又無法顯示，是哪出錯嘞？
...(恕刪)

sumif ，只能用在"數字"
1_1，2_1，3_1 ……等等，是"文字"，當然不行
如果您願意改成 11，21，31… 不要底線
那就可以繼續用 sumif