EXCEL 該怎麼做到查詢後，傳回整列值呢？

xfp4oqu

xfp4oqu
個人積分：99分
文章編號：21293348

99分

樓主

2010-07-29 23:13

不知道是不是函數懂得太少，今天在公司想了一下午，可是總是卡住無法順利執行，
回家估狗後也沒有找到適合的函數
所以希望網路上的網友們可以幫幫我跳過這個難關！

首先Sheet1 是這樣。
EXCEL 該怎麼做到查詢後，傳回整列值呢？

然後希望用sheet2去比對、如果Ａ欄位是蘋果，而且Ｂ欄位也是Ｉ的話，那麼他後續的欄位就依序填上
1 7 5 4 5
EXCEL 該怎麼做到查詢後，傳回整列值呢？

請問這有哪幾個公式可以混合使用呢？我會用最簡單的分別去判斷是否為「蘋果」，及「Ｉ」，可是我卻不知道該怎麼將數值傳回～

希望有知道的人可以幫幫我！感謝

2010-07-29 23:13 發佈

文章關鍵字 excel 列值

blackicbm

blackicbm
個人積分：6分
文章編號：21296648

6分

2樓

2010-07-30 1:45

Required Functions:
1. Indirect()
2. Match()
3. Index()

Prerequisite
1. Must have data in sheet 1 grouped by Column B
2. There is no duplicated value in Column A for each group in Column B

Steps to come out the results:

0. Delete Row 1 and Row 2 in Sheet1 first

1. Append 2 columns in Sheet2: H, and I
Name Column H: Column B Block Index
Name Column I: Column A Row Index

2. In the cell H2, input the following formula:
=MATCH(Sheet2!B2,Sheet1!B:B,0)

3. Copy the formula to other cells in Column H

4. In the cell I2, input the following formula:
=MATCH(A2,INDIRECT("Sheet1!A"&H2&":A100"),0)+H2-1

5. Copy the formula to other cells in Column I

6. In the cell C2, input the following formula:
=INDIRECT("Sheet1!R"&$I2&"C"&COLUMN(C$1),FALSE)

7. Copy the formula to the range: C2:G7

8. Done

曠男消滅無限公司http://spaces.msn.com/blackicbm/

chilan

chilan
個人積分：26分
文章編號：21300862

26分

3樓

2010-07-30 10:23

用LOOKUP就可以,參考這裡,原理自己看
http://blog.yam.com/trump/article/8111314

你的情況不能照抄,幫你修改一下,
sheet2 c2輸入

=IF(ISNA(LOOKUP(2,1/((sheet1!$A$4:
$A$11=$A2)*(sheet1!$B$4:
$B$11=$B2)),sheet1!C$4:
C$11)),0,LOOKUP(2,1/((sheet1!$A$4:
$A$11=$A2)*(sheet1!$B$4:
$B$11=$B2)),sheet1!C$4:
C$11))

直接右拉下拉即可,
我另外加上 if 跟 isna ,a,b不合的會補0

ptx

ptx
個人積分：1221分
文章編號：21301769

1221分

4樓

2010-07-30 10:56

如果sheet1可以插入一欄，我會在H欄或更後面加入一欄1,2,3....的流水編號
流水編號的最大數值就等同於資料的列數，以範例的sheet1就是從1～8
然後在sheet2將C2:G2同時選起來
輸入=OFFSET(sheet1!$A$3,SUMPRODUCT((sheet1!$A$4: $A$11=$A2)*1,(sheet1!$B$2: $B$10=$B16)*1,sheet1!$H$4: $H$11),2,1,5)
再按下crtl+shift+enter
然後sheet2第三列以後需要查詢的資料就複製上述公式即可

須注意的是，查詢sheet1的AB兩欄組合需是唯一，如有重複則會找到錯誤的數值
sumproduct的陣列長度需相同，這部分可考慮用其他方式去自動計算三個陣列數值範圍
還有offset的寬度也與需要呈現的查詢數值欄位相同

一個中文，各自表述

xfp4oqu

xfp4oqu
個人積分：99分
文章編號：21329651

99分

樓主

2010-07-31 19:13

其實早看到此篇各位的回應了，可是資質駑鈍，所以應是花了一晚上研究，目前又學到一招囉
感謝各位的回應！讚讚讚