INDEX+SMALL完成乾坤大挪移：滿足條件的數(shù)據(jù)自動(dòng)“跑到”其它工作表

INDEX+SMALL完成乾坤大挪移：滿足條件的數(shù)據(jù)自動(dòng)“跑到”其它工作表

問題來源

學(xué)習(xí)群里，有朋友提出的問題：

如下兩個(gè)數(shù)據(jù)表，一個(gè)是“全部”工作表，一個(gè)是“已對(duì)”（已經(jīng)核對(duì)）工作表，如何使得“全部”工作中，每核對(duì)一條，數(shù)據(jù)會(huì)自動(dòng)從“全部”工作表“自動(dòng)”跑到“已對(duì)”工作表，即：

結(jié)果如下動(dòng)圖：

公式實(shí)現(xiàn)

在A2單元格輸入公式：

=INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已對(duì)”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&””，以三鍵組合結(jié)束；

公式向下填充，填充到和“全部”數(shù)據(jù)表中的行數(shù)一致。

公式解析

ROWS(A:A)：

整個(gè)工作表的行數(shù)，EXCEL2016默認(rèn)工作表行數(shù)為1048576。此處用工作表最大行數(shù)，使得不管數(shù)據(jù)有多少行，公式都能使用。

IF(全部!$F:$F=”已對(duì)”,ROW(全部!A:A),ROWS(A:A))：

用IF函數(shù)，建立一新的數(shù)組，這一新的數(shù)組建立的規(guī)則是：

如果“全部”工作表中F列單元格內(nèi)容等于“已對(duì)”，則返回該單元格所在的行，否則返回整個(gè)工作表的行數(shù)。

所以：

如果“全部”工作表中第二行數(shù)據(jù)已經(jīng)核對(duì)（標(biāo)題行為第一行），“已對(duì)”工作表中A2單元格的公式，此部分返回的數(shù)組是：

{1048576;2;1048576;1048576;1048576;1048576;1048576;1048576;1048576;1048576;……}

如果“全部”工作表中第五行數(shù)據(jù)已經(jīng)核對(duì)（標(biāo)題行為第一行），“已對(duì)”工作表中A2單元格的公式，此部分返回的數(shù)組是：A3單元格的公式，此部分返回的數(shù)組是：

{1048576;1048576;1048576;1048576;5;1048576;1048576;1048576;1048576;1048576;……}

SMALL(IF(全部!$F:$F=”已對(duì)”,ROW(全部!A:A),ROWS(A:A)),ROW(A1))：

從上一步數(shù)組中取出的ROW(A1）小的數(shù)值。

ROW(A1)是一個(gè)動(dòng)態(tài)的數(shù)值，公式往下填充一行，行數(shù)加1，即當(dāng)公式在A3單元格時(shí)，是ROW(A2)，當(dāng)公式填充到A4單元格是，是ROW(A3)，當(dāng)?shù)紸5單元格時(shí)，是ROW(A3)……

這樣，就在A2、A3、A4、A5……的數(shù)組中找到了第1、2、3、4小的值，即第一條、第二條、第三條、第四條……已經(jīng)核對(duì)的數(shù)值。

$F:$F，F(xiàn)列絕對(duì)引用，因?yàn)椴还芄较蛳隆⑾蛴姨畛洌欠窈藢?duì)都在F列。

INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已對(duì)”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))：

當(dāng)公式在A2單元格時(shí)，返回第一條“已對(duì)”數(shù)據(jù)的A列的值，當(dāng)公式在A3單元格時(shí)，返回第二條“已對(duì)”數(shù)據(jù)的A列的值……

因?yàn)楣街蠭F部分是數(shù)組計(jì)算，所以公式以三鍵組合結(jié)束。

公式向右填充，得到“已對(duì)”數(shù)據(jù)B列值……

INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已對(duì)”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&””：

在最后加上&””，這一步是容錯(cuò)處理。用空單元格與空文本合并返回空文本的特性，將超出結(jié)果數(shù)量的部分不顯示出來。

公式雖然麻煩，但用于核對(duì)數(shù)據(jù)，絕對(duì)是一絕，試試吧！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。