10 ฟังก์ชันที่จำเป็นสำหรับ Microsoft Excel สำหรับการวิเคราะห์ข้อมูล
เบ็ดเตล็ด / / November 29, 2021
1. CONCATENATE
=CONCATENATE เป็นหนึ่งในฟังก์ชันที่สำคัญที่สุดสำหรับการวิเคราะห์ข้อมูล เนื่องจากช่วยให้คุณสามารถรวมข้อความ ตัวเลข วันที่ ฯลฯ จากหลายเซลล์ให้เป็นหนึ่งเดียว ฟังก์ชันนี้มีประโยชน์อย่างยิ่งในการรวมข้อมูลจากเซลล์ต่างๆ เข้าเป็นเซลล์เดียว ตัวอย่างเช่น มันมีประโยชน์สำหรับการสร้างพารามิเตอร์การติดตามสำหรับแคมเปญการตลาด สร้างการสืบค้น API เพิ่มข้อความในรูปแบบตัวเลข และอื่นๆ อีกหลายอย่าง
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
ในตัวอย่างข้างต้น ฉันต้องการเดือนและยอดขายรวมกันในคอลัมน์เดียว เพื่อที่ฉันได้ใช้สูตร =CONCATENATE(A2, B2) ในเซลล์ C2 เพื่อให้ได้ Jan$700 เป็นผลลัพธ์
สูตร: = เชื่อมต่อ (เซลล์ที่คุณต้องการรวม)
2. เลน
=LEN เป็นฟังก์ชันที่มีประโยชน์อีกอย่างหนึ่งสำหรับการวิเคราะห์ข้อมูลที่ส่งออกจำนวนอักขระในเซลล์ที่กำหนด ฟังก์ชันนี้ใช้งานได้อย่างโดดเด่นในขณะที่สร้างแท็กชื่อหรือคำอธิบายที่มีจำนวนอักขระสูงสุด นอกจากนี้ยังมีประโยชน์เมื่อคุณพยายามค้นหาความแตกต่างระหว่างตัวระบุที่ไม่ซ้ำกันซึ่งมักจะค่อนข้างยาวและไม่อยู่ในลำดับที่ถูกต้อง
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
ในตัวอย่างข้างต้น ฉันต้องการนับตัวเลขสำหรับจำนวนการดูที่ฉันได้รับในแต่ละเดือน สำหรับสิ่งนี้ ฉันใช้สูตร =LEN(C2) ในเซลล์ D2 เพื่อให้ได้ผลลัพธ์เป็น 5
สูตร: =เลน(เซลล์)
3. VLOOKUP
=VLOOKUP น่าจะเป็นหนึ่งในฟังก์ชันที่เป็นที่รู้จักมากที่สุดสำหรับทุกคนที่คุ้นเคยกับการวิเคราะห์ข้อมูล คุณสามารถใช้เพื่อจับคู่ข้อมูลจากตารางที่มีค่าอินพุตได้ ฟังก์ชันนี้มีโหมดการจับคู่สองโหมด — แบบตรงและแบบประมาณ ซึ่งควบคุมโดยช่วงของการค้นหา หากคุณตั้งค่าช่วงเป็น FALSE ระบบจะค้นหาค่าที่ตรงกันทั้งหมด แต่ถ้าคุณตั้งค่าเป็น TRUE ระบบจะค้นหาค่าที่ตรงกันโดยประมาณ
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
ในตัวอย่างข้างต้น ฉันต้องการค้นหาจำนวนการดูในแต่ละเดือน เพื่อที่ฉันใช้สูตร =VLOOKUP("Jun", A2:C13, 3) ในเซลล์ G4 และฉันได้ 74992 เป็นผลลัพธ์ ในที่นี้ "มิถุนายน" คือค่าการค้นหา A2:C13 คืออาร์เรย์ตารางที่ฉันกำลังมองหา "มิถุนายน" และ 3 คือจำนวนคอลัมน์ที่สูตรจะค้นหามุมมองที่สอดคล้องกันสำหรับเดือนมิถุนายน
ข้อเสียเพียงอย่างเดียวของการใช้ฟังก์ชันนี้คือใช้งานได้กับข้อมูลที่จัดเรียงเป็นคอลัมน์เท่านั้น ดังนั้นชื่อคือการค้นหาแนวตั้ง ดังนั้นหากคุณได้จัดเรียงข้อมูลเป็นแถวแล้ว ก่อนอื่นคุณต้อง ย้ายแถวเป็นคอลัมน์.
สูตร: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
4. ดัชนี/MATCH
เช่นเดียวกับฟังก์ชัน VLOOKUP ฟังก์ชัน INDEX และ MATCH มีประโยชน์สำหรับการค้นหาข้อมูลเฉพาะตามค่าอินพุต เมื่อใช้ INDEX และ MATCH ร่วมกัน สามารถเอาชนะข้อจำกัดของ VLOOKUP ในการให้ผลลัพธ์ที่ไม่ถูกต้อง (ถ้าคุณไม่ระวัง) ดังนั้นเมื่อคุณรวมทั้งสองฟังก์ชันเข้าด้วยกัน ฟังก์ชันเหล่านี้จึงสามารถระบุการอ้างอิงข้อมูลและค้นหาค่าในอาร์เรย์มิติเดียวได้ ที่ส่งคืนพิกัดของข้อมูลเป็นตัวเลข
![ฟังก์ชัน Excel ที่จำเป็น การวิเคราะห์ข้อมูล 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
ในตัวอย่างข้างต้น ฉันต้องการค้นหาจำนวนการดูในเดือนมกราคม สำหรับสิ่งนั้น ฉันใช้สูตร =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3) ที่นี่ A2:C13 คือคอลัมน์ของข้อมูลที่ฉันต้องการให้สูตรส่งคืน "Jan" คือค่าที่ฉันต้องการจับคู่ A2:A13 คือ คอลัมน์ที่สูตรจะค้นหา "ม.ค." และ 0 แสดงว่าฉันต้องการให้สูตรค้นหาที่ตรงกันทั้งหมดสำหรับ ค่า.
หากคุณต้องการหาค่าที่ใกล้เคียงกัน คุณจะต้องแทนที่ 0 ด้วย 1 หรือ -1 เพื่อที่ 1 จะพบค่าที่มากที่สุดซึ่งน้อยกว่าหรือเท่ากับค่าการค้นหา และ -1 จะค้นหาค่าที่น้อยที่สุดที่น้อยกว่าหรือเท่ากับค่าการค้นหา โปรดทราบว่าถ้าคุณไม่ใช้ 0, 1 หรือ -1 สูตรจะใช้ 1 โดย
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
ตอนนี้ ถ้าคุณไม่ต้องการฮาร์ดโค้ดชื่อเดือน คุณสามารถแทนที่ด้วยหมายเลขเซลล์ได้ ดังนั้นเราจึงสามารถแทนที่ "Jan" ในสูตรที่กล่าวถึงข้างต้นด้วย F3 หรือ A2 เพื่อให้ได้ผลลัพธ์แบบเดียวกัน
สูตร: =ดัชนี(คอลัมน์ของข้อมูลที่คุณต้องการส่งคืน, MATCH(จุดข้อมูลทั่วไปที่คุณพยายามจับคู่, คอลัมน์ของแหล่งข้อมูลอื่นที่มีจุดข้อมูลทั่วไป, 0))
5. MINIFS/MAXIFS
=MINIFS และ =MAXIFS คล้ายกันมากกับฟังก์ชัน =MIN และ =MAX ยกเว้นว่าอนุญาตให้คุณใช้ชุดค่าต่ำสุด/สูงสุดและจับคู่กับเกณฑ์เฉพาะได้เช่นกัน โดยพื้นฐานแล้ว ฟังก์ชันจะค้นหาค่าต่ำสุด/สูงสุด และจับคู่กับเกณฑ์อินพุต
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
ในตัวอย่างข้างต้น ฉันต้องการค้นหาคะแนนขั้นต่ำตามเพศของนักเรียน สำหรับสิ่งนั้น ฉันใช้สูตร =MINIFS (C2:C10, B2:B10, "M") และได้ผลลัพธ์ 27 ที่นี่ C2:C10 คือคอลัมน์ที่สูตรจะค้นหาคะแนน B2:B10 คือคอลัมน์ที่สูตรจะค้นหาเกณฑ์ (เพศ) และ "M" คือเกณฑ์
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
ในทำนองเดียวกัน สำหรับคะแนนสูงสุด ฉันใช้สูตร =MAXIFS(C2:C10, B2:B10, "M") และได้ผลลัพธ์ 100
สูตรสำหรับ MINIFS: =MINIFS(min_range, criteria_range1, criteria1,...)
สูตรสำหรับ MAXIFS: =MAXIFS(max_range, criteria_range1, criteria1,...)
6. ค่าเฉลี่ย
ฟังก์ชัน =AVERAGEIFS ช่วยให้คุณค้นหาค่าเฉลี่ยสำหรับชุดข้อมูลเฉพาะตามเกณฑ์อย่างน้อยหนึ่งรายการ ขณะใช้ฟังก์ชันนี้ คุณควรจำไว้ว่าเกณฑ์และช่วงค่าเฉลี่ยแต่ละเกณฑ์อาจแตกต่างกัน อย่างไรก็ตาม ในฟังก์ชัน =AVERAGEIF ทั้งช่วงเกณฑ์และช่วงผลรวมต้องมีช่วงขนาดเดียวกัน สังเกตความแตกต่างของเอกพจน์และพหูพจน์ระหว่างฟังก์ชันเหล่านี้หรือไม่ นั่นแหละครับที่ต้องระวัง
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
ในตัวอย่างนี้ ฉันต้องการค้นหาคะแนนเฉลี่ยตามเพศของนักเรียน เพื่อที่ฉันใช้สูตร =AVERAGEIFS(C2:C10, B2:B10, "M") และได้ 56.8 เป็นผลลัพธ์ โดยที่ C2:C10 คือช่วงที่สูตรจะค้นหาค่าเฉลี่ย B2:B10 คือช่วงเกณฑ์ และ "M" คือเกณฑ์
สูตร: =ค่าเฉลี่ย (Average_range, criteria_range1, criteria1,...)
7. COUNTIFS
ตอนนี้ ถ้าคุณต้องการนับจำนวนอินสแตนซ์ที่ชุดข้อมูลตรงตามเกณฑ์ที่กำหนด คุณจะต้องใช้ฟังก์ชัน =COUNTIFS ฟังก์ชันนี้ช่วยให้คุณเพิ่มเกณฑ์แบบไม่จำกัดให้กับคิวรีของคุณ และทำให้เป็นวิธีที่ง่ายที่สุดในการนับจำนวนตามเกณฑ์การป้อนข้อมูล
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
ในตัวอย่างนี้ ฉันต้องการหาจำนวนนักเรียนชายหรือหญิงที่สอบผ่าน (เช่น >=40) เพื่อที่ฉันใช้สูตร =COUNTIFS(B2:B10, "M", C2:C10, ">=40") โดยที่ B2:B10 คือช่วงที่สูตรจะค้นหาเกณฑ์แรก (เพศ) "M" คือเกณฑ์แรก C2:C10 คือช่วงที่สูตรจะค้นหาเกณฑ์ที่สอง (เครื่องหมาย) และ ">=40" เป็นเกณฑ์ที่สอง เกณฑ์.
สูตร: =COUNTIFS(เกณฑ์_ช่วง1, เกณฑ์1,...)
8. SUMPRODUCT
ฟังก์ชัน =SUMPRODUCT ช่วยให้คุณคูณช่วงหรืออาร์เรย์เข้าด้วยกัน แล้วส่งกลับผลรวมของผลิตภัณฑ์ เป็นฟังก์ชันที่ใช้งานได้หลากหลายและสามารถใช้ในการนับและรวมอาร์เรย์ เช่น COUNTIFS หรือ SUMIFS ได้ แต่มีความยืดหยุ่นเพิ่มขึ้น คุณยังสามารถใช้ฟังก์ชันอื่นๆ ภายใน SUMPRODUCT เพื่อขยายฟังก์ชันการทำงานให้ดียิ่งขึ้นไปอีก
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
ในตัวอย่างนี้ ฉันต้องการค้นหาผลรวมของผลิตภัณฑ์ทั้งหมดที่ขาย เพื่อการนั้น ฉันใช้สูตร =SUMPRODUCT(B2:B8, C2:C8) โดยที่ B2:B8 คืออาร์เรย์แรก (จำนวนผลิตภัณฑ์ที่ขาย) และ C2:C8 คืออาร์เรย์ที่สอง (ราคาของแต่ละผลิตภัณฑ์) จากนั้นสูตรจะคูณปริมาณของแต่ละผลิตภัณฑ์ที่ขายด้วยราคาของมัน จากนั้นจึงบวกทั้งหมดขึ้นเพื่อส่งมอบยอดขายทั้งหมด
สูตร: =SUMPRODUCT(array1, [array2], [array3],...)
9. TRIM
ฟังก์ชัน =TRIM มีประโยชน์อย่างยิ่งเมื่อคุณทำงานกับชุดข้อมูลที่มีช่องว่างหลายตัวหรืออักขระที่ไม่ต้องการ ฟังก์ชันนี้ช่วยให้คุณลบช่องว่างหรืออักขระเหล่านี้ออกจากข้อมูลของคุณได้อย่างง่ายดาย ช่วยให้คุณได้ผลลัพธ์ที่แม่นยำในขณะที่ใช้ฟังก์ชันอื่นๆ
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 11](/f/04381c96e973bee237ac85395449540a.jpg)
ในตัวอย่างนี้ ฉันต้องการลบช่องว่างพิเศษทั้งหมดระหว่างคำว่า Mouse และ pad ใน A7 เพื่อที่ฉันใช้สูตร =TRIM(A7)
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
สูตรเพียงแค่ลบช่องว่างพิเศษและส่งมอบแผ่นรองเมาส์ผลลัพธ์ที่มีช่องว่างเดียว
สูตร: = ทริม(ข้อความ)
10. ค้นหา/ค้นหา
การปัดเศษเป็นฟังก์ชัน FIND/SEARCH ซึ่งจะช่วยให้คุณแยกข้อความเฉพาะภายในชุดข้อมูลได้ ฟังก์ชันทั้งสองค่อนข้างคล้ายกันในสิ่งที่พวกเขาทำ ยกเว้นข้อแตกต่างที่สำคัญอย่างหนึ่ง — ฟังก์ชัน =FIND จะคืนค่าการจับคู่ที่ตรงตามตัวพิมพ์เล็กและตัวพิมพ์ใหญ่เท่านั้น ในขณะเดียวกัน ฟังก์ชัน =SEARCH ไม่มีข้อจำกัดดังกล่าว ฟังก์ชันเหล่านี้มีประโยชน์อย่างยิ่งเมื่อมองหาความผิดปกติหรือตัวระบุที่ไม่ซ้ำ
![การวิเคราะห์ข้อมูลฟังก์ชัน Excel ที่จำเป็น13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
ในตัวอย่างนี้ ฉันต้องการค้นหาจำนวนครั้งที่ 'Gui' ปรากฏภายใน Guiding Tech ซึ่งฉันใช้สูตร =FIND(A2, B2) ซึ่งให้ผลลัพธ์เป็น 1 ถ้าฉันต้องการค้นหาจำนวนครั้งที่ 'gui' ปรากฏภายใน Guiding Tech แทน ฉันต้องใช้สูตร =SEARCH เพราะไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
สูตรสำหรับค้นหา: = ค้นหา (find_text, within_text, [start_num])
สูตรสำหรับการค้นหา: = ค้นหา (find_text, within_text, [start_num])