要如何用EXCEL快速找出不連續的數值呢?像是1~100中缺了哪個數值,要怎麼快速找出呢?
別再用目視一個一個慢慢核對啦,Orange教你以下方法,讓你輕鬆解決沒煩惱。
方法二:利用「Vlookup參照函數」找出
相信大家都會建立1~100的數字吧,利用下拉拖曳能在幾秒鐘完成。
(1)首先先建立1~100的數字如下圖。
(2)將你想要查找「缺少的連續數字」選擇一欄位貼上,我們貼入到G3表格。
(3)於要查找「缺少的連續數字」的右邊欄位(H欄)都打上1。
利用下拉拖曳或是於十字點擊兩下迅速完成。
(4)按下插入函數,搜尋函數打上「VLOOKUP」,按下開始鍵,選取函數「VLOOKUP」按下確定鍵。 第一列:Lookup_value,要指定的資料,也就是你想要比對的資料,我們就直接代入表格D3。
第二列:Table_array,要參照資料的範圍,我們點選G欄按住不放,向右拖曳至H欄。
第三列:Col_index_num,指要傳回第幾欄的訊息,我們剛剛要參照的資料是G~H欄,選擇2也就是傳回從G算起第二欄的資料,也就是H欄的資料。
第四列:Range_lookup,為邏輯值,EXCEL的註解:TRUE或省略表示找出首欄最接近的值,FALSE表示僅尋找完全符合的值,我們用FALSE要尋找完全符合的值。 *可以代入1表示TRUE;代入0表示FALSE。
(5)按下確定,利用下拉拖曳或是複製貼上至100列。
(6) 若E欄位顯示1則表示含有G欄位之數字;若E欄位顯示#N/A,則表示缺少G欄位之數字。
EXCEL是不是很有趣呢,方法一跟方法二都可以快速找出1~100連續數字中少了哪個數字,運用不同的函數不同的思考方向,只要能快速解決我們的問題都是好方法,「不管黑貓白貓,會抓老鼠的就是好貓。」
方法一請至 →「【EXCEL】 如何快速找出1~100連續數字中少了哪個數字」
翻倍效率工作術:不會就太可惜的Excel 必學函數 (點此購買) 
翻倍效率工作術:不會就太可惜的Excel必學圖表 (點此購買) 
Simply Awesome!
Thank you.
Hope this helps you.
But wanna tell that this is very helpful , Thanks for taking your time to write this. Hayley Leonhard Sale
Hope the method of the article will help you.
Thank you.
Some really great articles on this web site , thanks for contribution. Allis Weber Farrington
Thank you.
Hi, constantly i used to check weblog posts here early in the daylight, as i enjoy to find out more and more. Tallie Abdel Nadab
Nice.
I love it whenever people get together and share thoughts. Great website, stick with it. Cody Stewart Searcy
It’s great to share ideas.
This is one awesome blog article. Much thanks again. Cool. Aubrie Raynor Laban
Sincere thanks.
Thanks again for the blog post. Really thank you! Much obliged. Amelita Alden Romy
You’re welcome.
Every weekend i used to pay a quick visit this web site, as i want enjoyment, since this this website conations actually pleasant funny stuff too. Daisy Alfons Hamfurd
Thank you.
Way cool! Some extremely valid points! I appreciate you writing this write-up and also the rest of the website is also very good. Regina Kingsley Guildroy
Thank you.
Amazing issues here. I am very glad to look your post. Fran Halsey Barclay
Thank you.
I have read so many articles about the blogger lovers but this article is truly a pleasant paragraph, keep it up. Rochell Samson Layman
Thank you.
I really liked your blog post. Really thank you! Much obliged. Vin Reggie Cimah
So glad you like it.
Thanks for sharing your thoughts. I really appreciate your efforts and I will be waiting for your next post thanks once again. Madelaine Guthrey Wakerly
You’re welcome.