การประมวลผลข้อมูลในสเปรดชีต

มุมมองรายการของสเปรดชีต

รายการหรือฐานข้อมูลเป็นวิธีหนึ่งในการจัดระเบียบข้อมูลบนเวิร์กชีต มันถูกสร้างขึ้นเป็นชุดที่มีป้ายกำกับซึ่งประกอบด้วยแถวที่มีข้อมูลประเภทเดียวกัน ตัวอย่างเช่น รายชื่อพนักงานของแผนกหนึ่งๆ ซึ่งคอลัมน์มีชื่อดังต่อไปนี้: ชื่อเต็ม, ยอดคงค้าง, ยอดหัก ณ ที่จ่าย, จำนวนเงินที่จะออก

ข้อมูลที่จัดเป็นรายการเรียกว่า ฐานข้อมูล- ในกรณีนี้ แถวในตารางคือบันทึกของฐานข้อมูล และคอลัมน์คือเขตข้อมูลบันทึก ที่จะเลี้ยว สเปรดชีต Excelในรายการ คุณจะต้องกำหนดชื่อให้กับคอลัมน์ที่จะใช้เป็นชื่อฟิลด์บันทึก เมื่อสร้างฐานข้อมูลต้องปฏิบัติตามกฎต่อไปนี้:

1. คุณไม่ควรวางมากกว่าหนึ่งรายการในหนึ่งแผ่นงานเพราะว่า การดำเนินการบางอย่าง เช่น การกรอง จะทำงานในรายการเดียวเท่านั้นในแต่ละครั้ง

2 คุณควรแยกรายการข้อมูลจากข้อมูลในแผ่นงานอื่นด้วยคอลัมน์ว่างอย่างน้อยหนึ่งคอลัมน์หรือแถวว่างหนึ่งแถว ซึ่งจะช่วยให้คุณเน้นรายการโดยอัตโนมัติเมื่อคุณกรองหรือจัดเรียงข้อมูล

3. ส่วนหัวของคอลัมน์รายการข้อมูลไม่ควรรวมเซลล์

4. ชื่อคอลัมน์ควรอยู่ในบรรทัดแรกของรายการ เนื่องจาก Excel จะถือว่าแถวแรกเป็นส่วนหัวของคอลัมน์เสมอ

5. เพื่อแยกส่วนหัวของคอลัมน์ออกจากข้อมูลหลัก คุณสามารถใช้แบบอักษร การจัดตำแหน่ง หรือรูปแบบที่แตกต่างจากที่สะท้อนถึงข้อมูลตารางหลักได้ คุณไม่สามารถเว้นบรรทัดว่างระหว่างส่วนหัวของคอลัมน์และข้อมูลหลักได้ ไม่แนะนำให้ใช้เส้นประเพื่อแยก

การเรียงลำดับข้อมูลในตาราง

การเรียงลำดับข้อมูลใน Excel มีข้อดีและข้อเสีย แง่บวก – ใช้งานได้หลากหลายและสะดวก ส่วนต่อประสานกับผู้ใช้- ข้อเสีย: ความง่ายที่ตารางจะกลายเป็นข้อมูลที่ยุ่งวุ่นวาย

หากต้องการเรียงลำดับ ให้เลือกเซลล์ตารางและเลือกคำสั่ง ข้อมูล การเรียงลำดับ Excel จะเน้นรายการทั้งหมดโดยอัตโนมัติ หากบรรทัดแรกมีชื่อฟิลด์ จะไม่รวมอยู่ในการเรียงลำดับ โปรดทราบว่าแถวผลลัพธ์ของรายการเดิมจะรวมอยู่ในการเรียงลำดับด้วย ดังนั้นบางครั้งการเลือกช่วงข้อมูลเพื่อจัดเรียงด้วยตนเองก็เหมาะสมกว่า

ในช่องป้อนข้อมูลสามช่องของหน้าต่าง การเรียงลำดับคุณสามารถตั้งค่าคีย์ที่จะจัดเรียงข้อมูลได้ คุณยังสามารถตั้งค่าตัวเลือกได้: ตั้งค่าลำดับการจัดเรียงตามคีย์แรก (ปกติหรือที่ผู้ใช้กำหนด) เช่นเดียวกับทิศทางการจัดเรียง (จากน้อยไปหามากหรือจากมากไปน้อย)

หากต้องการเรียงลำดับอย่างรวดเร็ว คุณสามารถใช้ปุ่ม "เรียงลำดับจากน้อยไปหามาก" และ "เรียงลำดับจากมากไปหาน้อย" บนแถบเครื่องมือมาตรฐาน (ในกรณีนี้ ปุ่มเรียงลำดับคือคอลัมน์ที่มีเซลล์ปัจจุบัน)

หากการเรียงลำดับไม่บรรลุผลตามที่คาดหวัง ให้ยกเลิกการจัดเรียง โดยคลิกที่ปุ่ม "ยกเลิก" บนแถบเครื่องมือมาตรฐาน

การกรองข้อมูลในรายการ

ตัวกรองคือการออกแบบที่ออกแบบมาเพื่อเลือกแถวของตารางที่ตรงตามเงื่อนไขที่กำหนดและซ่อนแถวที่เหลือไว้ชั่วคราว พื้นฐานของตัวกรองคือรายการที่มีเงื่อนไขในการเลือกแถว Excel ช่วยให้คุณดูข้อมูลที่ต้องการจากรายการได้อย่างรวดเร็วและสะดวกโดยใช้ตัวกรองอัตโนมัติ การสืบค้นฐานข้อมูลที่ซับซ้อนมากขึ้นสามารถนำมาใช้ได้โดยใช้คำสั่งตัวกรองขั้นสูง

ตัวกรองอัตโนมัติ

เมื่อใช้ฟังก์ชันตัวกรองอัตโนมัติ คุณสามารถแสดงเฉพาะบางรายการบนหน้าจอได้ เมื่อต้องการทำเช่นนี้ คุณต้องเลือกรายการที่มีส่วนหัวของฟิลด์ก่อน

ปุ่มที่ขยายรายการตัวกรองจะถูกสร้างขึ้นในแถวแรกของช่วง หากมีเซลล์ว่างในคอลัมน์ รายการจะเสริมด้วยหมวดหมู่ “ว่าง” และ “ไม่ว่าง” ไม่ว่าในกรณีใด แถวบนสุดของช่วงจะไม่ถือเป็นออบเจ็กต์สำหรับการกรองและไม่รวมอยู่ในรายการ แม้ว่าบางแถวจะไม่รวมอยู่ในการเลือก หลักการกรองก็จะนำไปใช้กับแถวเหล่านั้น รวมถึงแถวสุดท้ายด้วย หลังจากเลือกช่วงแล้ว ให้เลือกคำสั่ง ข้อมูล/ตัวกรอง/ตัวกรองอัตโนมัติ.

หลังจากคำสั่งนี้ Excel จะวางรายการดรอปดาวน์ลงในชื่อคอลัมน์รายการโดยตรง องค์ประกอบคอลัมน์ที่ถูกเน้นในรายการดรอปดาวน์เรียกว่าเกณฑ์ตัวกรอง คุณสามารถกรองต่อไปได้โดยใช้เกณฑ์จากคอลัมน์อื่น

หากต้องการลบเกณฑ์ตัวกรองสำหรับแต่ละคอลัมน์ คุณต้องเลือกตัวเลือกทั้งหมดจากรายการแบบเลื่อนลง หากต้องการแสดงแถวที่ซ่อนอยู่ทั้งหมดในรายการ ให้เลือก ข้อมูล/ตัวกรอง/แสดงทั้งหมด.

ผู้ใช้สามารถสร้างเงื่อนไขสำหรับการเลือกแถวของตารางสำหรับแต่ละคอลัมน์ได้ เกณฑ์ที่กำหนดเองการเลือกบันทึกอาจประกอบด้วยหนึ่งหรือสองรายการ การแสดงออกทางตรรกะเชื่อมต่อด้วยตัวดำเนินการ AND (OR) การใช้ตัวดำเนินการ AND จะถือว่าเงื่อนไขจะเป็นไปตามนั้นหากทั้งสองส่วนเป็นไปตามนั้นพร้อมกัน การใช้ตัวดำเนินการ OR ช่วยให้เงื่อนไขเป็นไปตามเงื่อนไขอย่างน้อยหนึ่งส่วน

หากต้องการตั้งค่าเกณฑ์ที่กำหนดเอง คุณต้องเลือกพารามิเตอร์ Condition ในรายการแบบเลื่อนลง จากนั้นในหน้าต่าง "ตัวกรองอัตโนมัติแบบกำหนดเอง" ที่ปรากฏขึ้น ให้ตั้งค่าเกณฑ์ที่จำเป็น: แสดงเฉพาะแถวที่มีค่า... – ระบุ เกณฑ์ที่จำเป็น

หากต้องการลบตัวกรองอัตโนมัติ คุณต้องเลือกคำสั่งอีกครั้ง ข้อมูล/ตัวกรอง/ตัวกรองอัตโนมัติ

10 คนแรก.วิธีการนี้เหมาะสมสำหรับช่องที่มีข้อมูลตัวเลขรวมถึงวันที่เท่านั้น หากต้องการใช้วิธีนี้ คุณต้องเลือกตัวเลือก "10 แรก..." จากรายการ ในกล่องโต้ตอบที่ปรากฏขึ้น คุณต้องระบุจำนวนองค์ประกอบที่ใหญ่ที่สุดหรือเล็กที่สุดที่ควรแสดง ตัวอย่างเช่น ฐานข้อมูลมีช่อง “เงินเดือน” เพื่อจัดเก็บเงินเดือนของพนักงาน องค์กรมีโอกาสให้ความช่วยเหลือทางการเงินแก่พนักงานที่มีเงินเดือนต่ำสุดจำนวน 5 คน เพื่อแก้ปัญหานี้ คุณสามารถใช้วิธีการเลือก "10 อันดับแรก..." สำหรับฟิลด์ "เงินเดือน": กำหนดจำนวนบันทึกที่แสดงด้วยค่าต่ำสุด

การกรองที่ซับซ้อน (ตัวกรองขั้นสูง)

หากต้องการกรองรายการตามเกณฑ์ที่ซับซ้อน รวมถึงรับส่วนหนึ่งของรายการเดิมด้วยคอลัมน์ที่ระบุหลายคอลัมน์ ให้ใช้คำสั่งตัวกรองขั้นสูงของเมนูข้อมูล ความแตกต่างจากคำสั่งตัวกรองอัตโนมัติคือ นอกเหนือจากตัวเลือกที่แสดงไว้แล้ว เรกคอร์ดที่กรองแล้วยังสามารถย้ายไปยังตำแหน่งอื่นในเวิร์กชีต Excel โดยไม่ทำให้รายการต้นฉบับเสีย ในตัวกรองขั้นสูง เกณฑ์ตัวกรองจะถูกสร้างขึ้นเป็นตาราง ซึ่งสามารถจัดเก็บไว้ที่ใดก็ได้ในเวิร์กบุ๊ก หรือแม้แต่ในไฟล์อื่น การดำเนินการประมวลผลข้อมูล Excel ทั้งหมดใช้ได้กับตารางเกณฑ์

ตัวกรองขั้นสูงช่วยให้คุณ:

1. คัดลอกบันทึกที่กรองแล้วไปยังตำแหน่งอื่นบนแผ่นงานทันที

2. บันทึกเกณฑ์การเลือกเพื่อใช้ต่อไป (ซึ่งจะมีประโยชน์เมื่อมีการเพิ่มข้อมูลใหม่ลงในรายการ และคุณต้องดึงข้อมูลเป็นระยะตามเกณฑ์)

3. แสดงคอลัมน์ตารางไม่ทั้งหมดในบันทึกที่กรอง แต่แสดงเฉพาะคอลัมน์ที่ระบุเท่านั้น

4. รวมเงื่อนไขสำหรับคอลัมน์ต่างๆ ด้วยตัวดำเนินการ OR

5. สำหรับหนึ่งคอลัมน์ ให้รวมเงื่อนไขมากกว่าสองเงื่อนไขเข้าด้วยกันโดยใช้ตัวดำเนินการ AND หรือ OR

6. สร้างเกณฑ์การคำนวณ

เมื่อต้องการใช้คำสั่งตัวกรองขั้นสูง คุณต้องสร้างตารางเกณฑ์ก่อน ซึ่งควรวางไว้บนเวิร์กชีตเดียวกันกับรายการต้นฉบับ แต่เพื่อไม่ให้ซ่อนไว้ระหว่างการกรอง ในการสร้างตารางเกณฑ์ เพื่อให้แน่ใจว่าฟิลด์ข้อความมีเอกลักษณ์เฉพาะตัว จำเป็นต้องคัดลอกชื่อของฟิลด์รายการไปยังส่วนของแผ่นงานที่จะวางตารางเกณฑ์ ในกรณีนี้ จำนวนแถวในตารางนี้จะถูกกำหนดโดยจำนวนเกณฑ์การค้นหาเท่านั้น อย่างไรก็ตาม การรวมแถวว่างในตารางเกณฑ์ไม่เป็นที่ยอมรับ เนื่องจากการทำเช่นนั้นจะส่งผลให้พบรายการทั้งหมดในรายการ

นอกจากตารางเกณฑ์แล้ว สำหรับคำสั่งตัวกรองขั้นสูง คุณต้องกำหนดประเภทของเอกสารเอาต์พุตด้วย ซึ่งหมายความว่าคุณควรคัดลอกชื่อของฟิลด์รายการที่กำหนดประเภทของเอกสารเอาต์พุตลงในพื้นที่ว่างของแผ่นงาน Excel จะกำหนดจำนวนบรรทัดในเอกสารเอาต์พุตเอง

ดังนั้น เพื่อดำเนินการคำสั่งตัวกรองขั้นสูง:

· แบบฟอร์มเข้า พื้นที่ว่างตารางเกณฑ์แผ่นงาน

· สร้างส่วนหัวของเอกสารเอาต์พุต

· เลือกพื้นที่รายการในเอกสารต้นฉบับ

ในพื้นที่การประมวลผล คุณต้องระบุว่าจะดำเนินการกรองในสถานที่หรือเอาต์พุตจะถูกถ่ายโอนไปยังพื้นที่อื่นของแผ่นงาน หากโหมดเริ่มต้นคือ "รายการตัวกรองในตำแหน่ง" Excel จะซ่อนแถวทั้งหมดในรายการแหล่งที่มาที่ไม่ตรงตามเกณฑ์ที่ระบุ หากเลือกปุ่มตัวเลือก Unique Records Only แถวที่ซ้ำกันจากรายการแหล่งที่มาจะไม่แสดงในพื้นที่เอาต์พุต

การตั้งค่าเงื่อนไขโดยใช้ตรรกะหรือการดำเนินการ:

หากต้องการกำหนดเงื่อนไขในช่วงของเกณฑ์โดยใช้ตรรกะหรือการดำเนินการ คุณต้องวางเงื่อนไขเหล่านี้ในบรรทัดที่แตกต่างกัน: ตัวอย่างเช่น:

1) แสดงบันทึกเกี่ยวกับผู้จัดการด้วยนามสกุล “Kislov” หรือเกี่ยวกับผู้จัดการที่ขาย “Bread”:

2) รับข้อมูลเกี่ยวกับลูกค้าที่มีนามสกุลขึ้นต้นด้วยตัวอักษร B และ T:

การตั้งค่าเงื่อนไขโดยใช้การดำเนินการ AND แบบลอจิคัล:

หากต้องการตั้งค่าเงื่อนไขในช่วงเกณฑ์โดยใช้การดำเนินการ AND คุณต้องวางเงื่อนไขเหล่านี้ในบรรทัดเดียว: ตัวอย่างเช่น:

1) ค้นหาข้อมูลเกี่ยวกับผู้จัดการที่มีนามสกุล “เปโตรวา” ที่ขายสินค้ามากกว่า 50 หน่วย:

2) ค้นหาข้อมูลเกี่ยวกับสินค้าที่มีราคามากกว่า 30 และน้อยกว่า 80 รูเบิล

วัตถุประสงค์ของงาน:ดำเนินการเรียงลำดับข้อมูล ทำความคุ้นเคยกับวิธีการกรองรายการ การกรองอัตโนมัติ และการทำงานกับแบบฟอร์มข้อมูล

ภารกิจที่ 1

จัดเรียงข้อมูลในตาราง 5.5 หลายครั้งตามเกณฑ์ต่อไปนี้ - ตามลำดับตัวอักษรของนามสกุลของผู้ซื้อ ในลำดับจากมากไปน้อยของจำนวนเงินธุรกรรม โดยเรียงลำดับจากน้อยไปหามากของวันที่ทำธุรกรรม โดยผสมผสานลักษณะต่างๆ (นามสกุล วันที่ จำนวนเงิน)

วิธีการทำงาน

1. เปิดสมุดงานใหม่และบันทึกเป็น "เรียงลำดับ" ในโฟลเดอร์การทำงานของคุณ .

2. สร้างตารางดังรูปที่ 5.56

รูปที่ 5.56 – ตารางเริ่มต้นพร้อมข้อมูล

3. ตั้งค่าตัวเลือกการจัดรูปแบบสำหรับตาราง

Font Times New Roman, ขนาดตัวอักษร 12 พอยต์, สำหรับส่วนหัว, รูปแบบตัวหนาและการจัดกึ่งกลาง, การตัดคำ, เติมสีเทา; สำหรับส่วนหลัก เพื่อเป็นการเตือนความจำ คำสั่งการจัดรูปแบบจะพร้อมใช้งานบน Ribbon หน้าแรก Þ เซลล์ .

4. หากต้องการจัดเรียงตามฟิลด์นามสกุลของผู้ซื้อ ให้วางเคอร์เซอร์ที่ใดก็ได้ในคอลัมน์นี้แล้วรันคำสั่ง ข้อมูล Þ การเรียงลำดับ (รูปที่ 5.51) .

ในกล่องโต้ตอบที่เปิดขึ้นในฟิลด์ เรียงตามเลือกนามสกุลของผู้ซื้อ จากน้อยไปมาก

5. ทำซ้ำขั้นตอนทั้งหมดในขั้นตอนที่ 4 และตั้งค่าการเรียงลำดับตาม "จำนวนธุรกรรม" ตามลำดับจากมากไปน้อย

6. จัดเรียงใหม่ตามฟิลด์ "วันที่ทำธุรกรรม" จากน้อยไปหามาก

7. คัดลอกตารางไปยังแผ่นงานใหม่และจัดเรียงตามชุดคุณลักษณะ เมื่อต้องการทำเช่นนี้ ให้เรียกคำสั่ง ข้อมูล Þ การเรียงลำดับ ติดตั้ง เรียงตามนามสกุลตามลำดับจากน้อยไปมาก แล้วโดยวันที่เรียงลำดับจากน้อยไปมาก สุดท้ายนี้ โดยจำนวนเงินตามลำดับจากมากไปน้อย

8. การใช้คำสั่ง เปลี่ยนชื่อ ตั้งชื่อให้ทั้งสองแผ่นนี้

ภารกิจที่ 2- เลือกข้อมูลจากรายการตามคำสั่งตัวกรองอัตโนมัติ

วิธีการทำงาน.

1. บนแผ่นที่ 4 สร้างตารางและกรอกข้อมูลจากตาราง 5.5

2. เปลี่ยนชื่อ Sheet4 โดยตั้งชื่อว่า "AutoFilter #1"

3. หากต้องการใช้การกรองอัตโนมัติ ให้วางเคอร์เซอร์ในพื้นที่รายการแล้วรันคำสั่ง ข้อมูลÞตัวกรอง ลูกศรลงจะปรากฏถัดจากชื่อคอลัมน์ในตาราง โดยแสดงรายการค่าที่เป็นไปได้ ในคอลัมน์ "เพศ" เลือก "M" คัดลอกตารางไปที่แผ่นงาน 5 และเปลี่ยนชื่อเป็น "ตัวกรองอัตโนมัติหมายเลข 2"

4. บนชีต "ตัวกรองอัตโนมัติหมายเลข 1" ในคอลัมน์ "เพศ" ให้เปิดรายการตัวกรองแล้วเลือก "ทั้งหมด" จากนั้นในคอลัมน์ "วันเกิด" เลือก "เงื่อนไข" จากรายการตัวกรองและตั้งค่าเงื่อนไข (รูปที่ 5.57):

ตารางที่ 5.5

นามสกุล	ชื่อ	วันที่จ้าง	วันเกิด	พื้น	เงินเดือน	อายุ
ปาชคอฟ	อิกอร์	16.05.74	15.03.49	ม
แอนดรีวา	แอนนา	16.01.93	19.10.66	และ
เอโรคิน	วลาดิเมียร์	23.10.81	24.04.51	ม
โปปอฟ	อเล็กซ์	02.05.84	07.10.56	ม
ทูนคอฟ	วลาดิเมียร์	03.11.88	19.07.41	ม
นอตคิน	เยฟเกนี่	27.08.85	17.08.60	ม
คูบรินา	มารีน่า	20.04.93	26.06.61	และ
กุดคอฟ	นิกิต้า	18.03.98	05.04.58	ม
กอร์บาตอฟ	ไมเคิล	09.08.99	15.09.52	ม
บิสตรอฟ	อเล็กซ์	06.12.00	08.10.47	ม
ครีโลวา	ตาเตียนา	28.12.93	22.03.68	และ
เบอร์เชวา	ออลก้า	14.12.01	22.12.74	และ
รูซาโนวา	หวัง	24.05.87	22.01.54	และ

รูปที่ 5.57 – การตั้งค่าเงื่อนไขการกรอง

5. คัดลอกตารางที่กรองไปยังแผ่นงาน 6 และเปลี่ยนชื่อเป็น "ตัวกรองอัตโนมัติหมายเลข 3" บนแผ่นงาน AutoFilter No. 1 ให้ยกเลิกการเลือก

รูปที่ 5.58 – ตัวกรองแบบกำหนดเอง

6. ในคอลัมน์ "นามสกุล" เลือก "เงื่อนไข" ในรายการตัวกรองและกำหนดเงื่อนไขสำหรับการเลือกพนักงานทั้งหมดที่มีนามสกุลขึ้นต้นด้วย "B" (รูปที่ 5.58)

7. คัดลอกรายการที่กรองไปยังแผ่นงาน 7 และเปลี่ยนชื่อเป็น "ตัวกรองอัตโนมัติหมายเลข 4"

8. บนแผ่น "ตัวกรองอัตโนมัติหมายเลข 1" สำหรับคอลัมน์ "นามสกุล" ให้ตั้งค่า "ทั้งหมด" และในคอลัมน์ "เงินเดือน" ให้ตั้งค่า "10 แรก ... " โดยในกล่องโต้ตอบให้ป้อน "แสดงองค์ประกอบที่ใหญ่ที่สุด 5 ประการ ของรายการ”

9. บันทึกไฟล์

ภารกิจที่ 3เลือกเรกคอร์ดจากรายการโดยใช้คำสั่งตัวกรองขั้นสูง

ระเบียบวิธีในการปฏิบัติงาน

1. ไปที่ชีต 8 แล้วเปลี่ยนชื่อเป็น "ตัวกรองขั้นสูง"

2. คัดลอกตารางจากงานก่อนหน้า (ตาราง 5.5) ลงบนแผ่นงานนี้ วางโดยเริ่มจากบรรทัดที่ 7 6 บรรทัดแรกสงวนไว้สำหรับการตั้งค่าเงื่อนไข

3. มาสร้างเงื่อนไขต่างๆกัน สมมติว่าเราต้องเลือกชื่อพนักงานที่ได้รับมากกว่า 5,000 รูเบิล หรือมีอายุเกิน 50 ปีบริบูรณ์ กรอกเงื่อนไขดังรูปที่ 5.59

รูปที่ 5.59 – เงื่อนไขสำหรับตัวกรองขั้นสูง

4. รันคำสั่ง ข้อมูล Þ เพิ่มเติม - กรอกกล่องโต้ตอบดังต่อไปนี้ (รูปที่ 5.60):

รูปที่ 5.60 – หน้าต่างพารามิเตอร์ตัวกรองขั้นสูง

ดูผลการคัดเลือก เมื่อเขียนเงื่อนไขในบรรทัดเดียว ตรรกะ AND จะถูกนำไปใช้ เมื่อเขียนเงื่อนไขในบรรทัดที่ต่างกัน เงื่อนไขเหล่านั้นจะถือว่าเชื่อมโยงกันด้วยตรรกะ OR เราได้พิจารณาตัวเลือกแรกแล้ว ตอนนี้เราจะพิจารณาตัวเลือกที่สอง

5. สมมติว่าเราต้องแสดงเฉพาะพนักงานที่มีนามสกุลขึ้นต้นด้วยตัวอักษร A, G หรือ N กรอกข้อมูลในช่วงเงื่อนไข (รูปที่ 5.61)

รูปที่ 5.61 – เงื่อนไขสำหรับตัวกรองขั้นสูง

6. รันคำสั่ง ข้อมูลÞเพิ่มเติม และกรอกข้อความในกล่องโต้ตอบ (รูปที่ 5.62)

รูปที่ 5.62 – หน้าต่างพารามิเตอร์ตัวกรองขั้นสูง

ดูผลลัพธ์ของการเลือกบันทึก

1. พิมพ์รายชื่อพนักงานที่มีเงินเดือนสูงกว่าค่าเฉลี่ยทั้งหมด ก่อนที่จะสร้างตัวกรองนี้ ให้ป้อนสูตร =AVERAGE(F8:F20) ในเซลล์ H2 เพื่อคำนวณเงินเดือนโดยเฉลี่ย

2. จากนั้นในเซลล์ A2 เราป้อนเงื่อนไขจากการคำนวณ =F8>$H$2 ซึ่งอ้างอิงถึงเซลล์ H2 (รูปที่ 5.63 และ 5.64)

รูปที่ 5.63 – เงื่อนไขสำหรับตัวกรองขั้นสูง

รูปที่ 5.64 – พารามิเตอร์ตัวกรองขั้นสูง

คุณสามารถแสดงข้อมูลเกี่ยวกับพารามิเตอร์หนึ่งหรือหลายตัวได้โดยใช้การกรองข้อมูลใน Excel

มีสองเครื่องมือสำหรับจุดประสงค์นี้: ตัวกรองอัตโนมัติและตัวกรองขั้นสูง พวกเขาจะไม่ลบ แต่ซ่อนข้อมูลที่ไม่ตรงตามเงื่อนไข ตัวกรองอัตโนมัติดำเนินการที่ง่ายที่สุด ตัวกรองขั้นสูงมีตัวเลือกมากมาย

ตัวกรองอัตโนมัติและตัวกรองขั้นสูงใน Excel

มีอยู่ ตารางที่เรียบง่ายไม่ได้จัดรูปแบบหรือประกาศเป็นรายการ คุณสามารถเปิดใช้งานตัวกรองอัตโนมัติผ่านเมนูหลัก

หากคุณจัดรูปแบบช่วงข้อมูลเป็นตารางหรือประกาศเป็นรายการ ตัวกรองอัตโนมัติจะถูกเพิ่มทันที

การใช้ตัวกรองอัตโนมัตินั้นง่ายมาก: คุณต้องเลือกรายการด้วยค่าที่ต้องการ เช่น แสดงการจัดส่งไปยังร้านค้าหมายเลข 4 วางเครื่องหมายถูกไว้ถัดจากเงื่อนไขการกรองที่เกี่ยวข้อง:

เห็นผลทันที:

คุณสมบัติของเครื่องมือ:

1. ตัวกรองอัตโนมัติจะทำงานเฉพาะในช่วงที่ไม่แตกหักเท่านั้น ตารางที่ต่างกันในแผ่นงานเดียวกันจะไม่ถูกกรอง แม้ว่าจะมีข้อมูลประเภทเดียวกันก็ตาม
2. เครื่องมือจะถือว่าบรรทัดบนสุดเป็นส่วนหัวของคอลัมน์ - ค่าเหล่านี้จะไม่รวมอยู่ในตัวกรอง
3. อนุญาตให้ใช้เงื่อนไขการกรองหลายรายการพร้อมกันได้ แต่ผลลัพธ์ก่อนหน้านี้แต่ละรายการอาจซ่อนบันทึกที่จำเป็นสำหรับตัวกรองถัดไป

ตัวกรองขั้นสูงมีความสามารถมากขึ้น:

1. คุณสามารถกำหนดเงื่อนไขการกรองได้มากเท่าที่ต้องการ
2. มองเห็นเกณฑ์ในการเลือกข้อมูลได้
3. การใช้ตัวกรองขั้นสูงทำให้ผู้ใช้สามารถค้นหาค่าที่ไม่ซ้ำในอาร์เรย์หลายบรรทัดได้อย่างง่ายดาย



วิธีสร้างตัวกรองขั้นสูงใน Excel

ตัวอย่างสำเร็จรูป - วิธีใช้ตัวกรองขั้นสูงใน Excel:

มีเพียงแถวที่มีค่า "มอสโก" เท่านั้นที่ยังคงอยู่ในตารางดั้งเดิม หากต้องการยกเลิกการกรอง คุณต้องคลิกปุ่ม "ล้าง" ในส่วน "จัดเรียงและกรอง"

วิธีใช้ตัวกรองขั้นสูงใน Excel

ลองใช้ตัวกรองขั้นสูงใน Excel เพื่อเลือกแถวที่มีคำว่า "Moscow" หรือ "Ryazan" เงื่อนไขการกรองต้องอยู่ในคอลัมน์เดียวกัน ในตัวอย่างของเรา - อยู่ด้านล่างกัน

กรอกเมนูตัวกรองขั้นสูง:

เราได้รับตารางที่มีแถวที่เลือกตามเกณฑ์ที่กำหนด:

เรามาเลือกแถวที่มีค่า "หมายเลข 1" ในคอลัมน์ "ร้านค้า" และ ">1,000,000 รูเบิล" ในคอลัมน์ต้นทุน เกณฑ์สำหรับการกรองต้องอยู่ในคอลัมน์ที่เหมาะสมของตารางเงื่อนไข ในหนึ่งบรรทัด

กรอกพารามิเตอร์การกรอง คลิกตกลง

ให้เราออกจากตารางเฉพาะแถวที่มีคำว่า "Ryazan" ในคอลัมน์ "ภูมิภาค" หรือค่า ">10,000,000 รูเบิล" ในคอลัมน์ "ต้นทุน" เนื่องจากเกณฑ์การคัดเลือกอยู่ในคอลัมน์ที่ต่างกัน เราจึงวางเกณฑ์ไว้ในบรรทัดที่ต่างกันใต้หัวข้อที่เกี่ยวข้อง

มาใช้เครื่องมือ "ตัวกรองขั้นสูง":

เครื่องมือนี้สามารถทำงานร่วมกับสูตรซึ่งช่วยให้ผู้ใช้สามารถแก้ปัญหาได้เกือบทุกปัญหาเมื่อเลือกค่าจากอาร์เรย์

กฎพื้นฐาน:

1. ผลลัพธ์ของสูตรคือเกณฑ์การคัดเลือก
2. สูตรที่เขียนคืนค่า TRUE หรือ FALSE
3. ช่วงเริ่มต้นถูกระบุโดยใช้การอ้างอิงแบบสัมบูรณ์ และเกณฑ์การเลือก (ในรูปแบบของสูตร) ​​ถูกระบุโดยใช้เกณฑ์สัมพัทธ์
4. หากส่งคืน TRUE แถวจะแสดงหลังจากใช้ตัวกรอง เท็จ - ไม่

เรามาแสดงแถวที่มีปริมาณสูงกว่าค่าเฉลี่ยกัน ในการดำเนินการนี้ นอกเหนือจากแผ่นเกณฑ์ (ในเซลล์ I1) ให้ป้อนชื่อ “ ปริมาณมากที่สุด- ด้านล่างนี้เป็นสูตร เราใช้ฟังก์ชัน AVERAGE

เลือกเซลล์ใดๆ ในช่วงแหล่งที่มาแล้วเรียก "ตัวกรองขั้นสูง" เราระบุ I1:I2 เป็นเกณฑ์การคัดเลือก (ลิงก์ที่เกี่ยวข้อง!)

เฉพาะแถวที่มีค่าในคอลัมน์ "ปริมาณ" สูงกว่าค่าเฉลี่ยเท่านั้นที่จะยังคงอยู่ในตาราง

หากต้องการปล่อยเฉพาะแถวที่ไม่ซ้ำในตาราง ในหน้าต่าง "ตัวกรองขั้นสูง" ให้ทำเครื่องหมายในช่องถัดจาก "เฉพาะระเบียนที่ไม่ซ้ำเท่านั้น"

คลิกตกลง เส้นที่ซ้ำกันจะถูกซ่อนไว้ เฉพาะรายการที่ไม่ซ้ำเท่านั้นที่จะยังคงอยู่ในแผ่นงาน

คุณสามารถเลือกข้อมูลที่จำเป็นจากรายการได้โดยใช้การกรอง กล่าวคือ โดยการซ่อนแถวทั้งหมดของรายการ ยกเว้นแถวที่ตรงตามเกณฑ์ที่ระบุ หากต้องการใช้ฟังก์ชันการกรอง คุณต้องวางเคอร์เซอร์ของตารางไว้บนเซลล์ส่วนหัวรายการใดเซลล์หนึ่ง (ในตารางของเราคือช่วง A1:U11) แล้วเรียกใช้คำสั่ง ข้อมูล/ตัวกรอง/ตัวกรองอัตโนมัติ- เมื่อเปิดใช้งานแล้ว สี่เหลี่ยมเล็กๆ ที่มีลูกศรแบบเลื่อนลงจะปรากฏขึ้นที่มุมขวาล่างของแต่ละเซลล์ส่วนหัว

มาดูวิธีการทำงานกับตัวกรองอัตโนมัติโดยใช้ตัวอย่างต่อไปนี้ มาดูกันว่ามีตัวแทนขายบริการทางเพศที่แข็งแกร่งในองค์กรกี่คน คลิกปุ่มตัวกรองที่อยู่ในเซลล์ที่มีหัวข้อ เพศ และเลือกตัวอักษร M (ชาย) จากรายการที่เปิดขึ้น ข้อความ Filter: Selection จะปรากฏในแถบสถานะ (รูปที่ 4.20) แถวทั้งหมดที่ไม่ตรงตามเกณฑ์ที่ระบุจะถูกซ่อน ลูกศรบนปุ่มรายการจะเปลี่ยนเป็นสีน้ำเงิน แสดงว่าเปิดใช้งานตัวกรองอัตโนมัติสำหรับฟิลด์นั้นแล้ว

ข้าว. 4.20.การใช้ตัวกรองอัตโนมัติเพื่อเลือกบันทึกตาม "M" (ชาย)

หากคุณต้องการชี้แจงว่ามีผู้ชายกี่คนที่เป็นหัวหน้า ให้คลิกปุ่มตัวกรองอัตโนมัติในเซลล์ตำแหน่ง และเลือกคำว่า หัวหน้า ในรายการที่เกี่ยวข้อง ข้อความจะปรากฏในแถบสถานะเพื่อระบุจำนวนแถวที่ตรงตามเกณฑ์ที่ระบุ: บันทึกที่พบ: 2 จาก 10 (นั่นคือคำตอบจะได้รับทันที) ผลลัพธ์จะแสดงในรูป 4.21.

หากต้องการยกเลิกการกรองตามคอลัมน์ใดคอลัมน์หนึ่ง เพียงเปิดรายการตัวกรองอัตโนมัติในคอลัมน์นั้นแล้วเลือกทั้งหมด อย่างไรก็ตาม หากตั้งค่าฟังก์ชันการกรองไว้หลายคอลัมน์ คุณจะต้องดำเนินการนี้ซ้ำหลายครั้ง ในกรณีนี้ควรใช้คำสั่งจะดีกว่า ข้อมูล/ตัวกรอง/แสดงทั้งหมด.

ข้าว. 4.21.ใบงานหลังกรองรายชื่อพนักงานตามเกณฑ์ “เจ้านายชาย”

ฟังก์ชั่นการกรองจะทำงานได้อย่างถูกต้องหากคุณระมัดระวังในการป้อนข้อมูล โดยเฉพาะอย่างยิ่ง คุณต้องแน่ใจว่าไม่มีช่องว่างเพิ่มเติมที่จุดเริ่มต้นและจุดสิ้นสุดของข้อมูลข้อความ สิ่งเหล่านี้ไม่สามารถมองเห็นได้ชัดเจนบนหน้าจอ แต่สามารถนำไปสู่ผลลัพธ์ที่ผิดพลาดได้ และต้องใช้เวลามากในการระบุสิ่งเหล่านี้

การกรองจะเลือกข้อมูลที่ตรงตามเกณฑ์ที่กำหนดทุกประการ ดังนั้นหากแทนที่จะเป็นคำว่า "Head" คำว่า "Head_" ปรากฏในคอลัมน์นั่นคือด้วยการเว้นวรรคที่ส่วนท้าย Excel จะถือว่าค่าเหล่านี้แตกต่างออกไป หากต้องการกำจัดความไม่สอดคล้องกันประเภทนี้ ให้คัดลอกเซลล์ที่มีคำว่า "Boss" ไปยังคลิปบอร์ด เปิดใช้งานตัวกรองสำหรับการเลือกโดย "Boss_" และแทนที่ค่าที่ไม่ถูกต้องด้วยเนื้อหาของบัฟเฟอร์

การกรองข้อมูลในรายการ

ตัวกรองรวดเร็วและ วิธีง่ายๆค้นหาชุดย่อยของข้อมูลและทำงานกับข้อมูลเหล่านั้นในรายการ รายการที่กรองจะแสดงเฉพาะแถวที่ตรงกัน เงื่อนไข(เงื่อนไข ข้อจำกัดที่ระบุในการเลือกบันทึก) ที่กำหนดไว้สำหรับคอลัมน์ ในกรณีนี้ เส้นที่เหลือจะกลายเป็น ที่ซ่อนอยู่.

ทั้งหมดนี้ช่วยลดความยุ่งยากในการป้อนและลบบันทึกตลอดจนกระบวนการค้นหาข้อมูล

ข้อดีของการใช้ตัวกรองคือสามารถคัดลอกผลลัพธ์การกรองไปยังพื้นที่แยกต่างหากของตารางและนำไปใช้ในการคำนวณได้ทันที

ใน ไมโครซอฟต์ เอ็กเซลมีสองคำสั่งสำหรับการกรองรายการ:

• ตัวกรองอัตโนมัติรวมถึงการกรองตามการเลือก สำหรับเงื่อนไขการเลือกอย่างง่าย
• ตัวกรองขั้นสูงสำหรับเงื่อนไขการเลือกที่ซับซ้อนมากขึ้น

ต่างจากการเรียงลำดับ ตัวกรองจะไม่เปลี่ยนลำดับของรายการในรายการ การกรองจะซ่อนแถวที่คุณไม่ต้องการแสดงไว้ชั่วคราว

แถวที่เลือกโดยการกรองใน Microsoft Excel สามารถแก้ไข จัดรูปแบบ สร้างเป็นแผนภูมิ และพิมพ์ได้โดยไม่ต้องเปลี่ยนลำดับของแถวหรือย้ายแถว

ตัวกรองอัตโนมัติ ใช้ในกรณีที่จำเป็นต้องกรองข้อมูลอย่างรวดเร็วโดยระบุเงื่อนไขการเลือกง่ายๆ หนึ่งหรือสองเงื่อนไข เงื่อนไขเหล่านี้ถูกกำหนดให้กับเนื้อหาของเซลล์ในคอลัมน์ใดคอลัมน์หนึ่ง

เมื่อใช้คำสั่ง ตัวกรองอัตโนมัติปุ่มลูกศรปรากฏทางด้านขวาของชื่อคอลัมน์ในรายการที่กรอง

Microsoft Excel ระบุรายการที่กรองเป็นสีน้ำเงิน

เมื่อต้องใช้เงื่อนไขการเลือกกับเซลล์ในสองคอลัมน์ขึ้นไปพร้อมกัน

เมื่อต้องใช้เงื่อนไขการเลือกสามเงื่อนไขขึ้นไปกับเซลล์ในคอลัมน์เดียวกัน:

เมื่อเงื่อนไขการเลือกใช้ค่าที่ได้รับจากการคำนวณสูตรที่กำหนด

ตัวกรองขั้นสูงสามารถใช้การดำเนินการ AND, OR และยังสร้างเกณฑ์จากการคำนวณได้อีกด้วย

คำสั่งทั้งสองถูกเรียกโดยเป็นผลมาจากการเลือกคำสั่ง Data/Filter

โดยใช้คำสั่ง ตัวกรองขั้นสูงคุณสามารถกรองรายการได้เช่นเดียวกับการใช้คำสั่ง ตัวกรองอัตโนมัติแต่รายการแบบเลื่อนลงสำหรับคอลัมน์จะไม่แสดงขึ้น

หากต้องการค้นหาข้อมูลโดยใช้ตัวกรองขั้นสูง คุณต้องเตรียมข้อมูลตามนั้นก่อน:

1 สร้างฐานข้อมูล ( ช่วงเดิม).

2. สร้าง ช่วงเงื่อนไขซึ่งระบุเงื่อนไขการค้นหาข้อมูล บรรทัดบนสุดของช่วงนี้ควรมีส่วนหัวของฟิลด์ที่ตรงกับส่วนหัวของฟิลด์ในช่วงเดิมทุกประการ (รายการพร้อม)

ช่วงของเงื่อนไขต้องมีเซลล์ว่างอย่างน้อยหนึ่งแถวที่อยู่ด้านล่างส่วนหัว ใน เส้นนี้หรือเส้นนี้ เงื่อนไขจะถูกเขียนลง และทุกสิ่งที่เขียนในคอลัมน์ใต้ส่วนหัวของฟิลด์จะอ้างอิงถึงฟิลด์นี้โดยเฉพาะ

เงื่อนไขของคอลัมน์ทั้งหมดของแต่ละแถวเชื่อมต่อกันด้วยการดำเนินการเชิงตรรกะ " และ" จากนั้นทุกบรรทัดจะเชื่อมต่อกันด้วยการดำเนินการเชิงตรรกะ " หรือ»

3 จากเมนู ข้อมูลเลือกทีม กรอง,แล้ว ตัวกรองขั้นสูง

ในช่องป้อนข้อมูล ช่วงของเงื่อนไขระบุช่วงของเซลล์บนเวิร์กชีตที่มีเงื่อนไขของคุณ ในช่องป้อนข้อมูล วางผลลัพธ์ในช่วงระบุช่วงของเซลล์ที่จะคัดลอกแถวไป ฟิลด์นี้จะใช้ได้เฉพาะเมื่อมีการเลือกปุ่มตัวเลือกเท่านั้น คัดลอกไปยังตำแหน่งอื่น.

4 ตรวจสอบว่าช่วงทั้งหมดถูกกรอกอย่างถูกต้องโดยอัตโนมัติ และแก้ไขให้ถูกต้องหากจำเป็น

ตัวอย่างของเงื่อนไขการเลือกที่ซับซ้อน:

หลายเงื่อนไขสำหรับหนึ่งคอลัมน์ หากมีเงื่อนไขการเลือกสองเงื่อนไขขึ้นไปสำหรับหนึ่งคอลัมน์ ให้ป้อนเงื่อนไขการเลือกเหล่านี้ด้านล่างกันโดยแยกบรรทัดกัน พนักงานขาย ที่รัก บาตูริน โรชชิน เงื่อนไขเดียวสำหรับหลายคอลัมน์ หากต้องการค้นหาข้อมูลหลายคอลัมน์ที่ตรงตามเงื่อนไขการเลือกเดียวกัน ให้ป้อนเงื่อนไขการเลือกทั้งหมดในแถวเดียวของช่วงเงื่อนไขการเลือก พิมพ์ พนักงานขาย ฝ่ายขาย ผลไม้ ที่รัก >1 000 เงื่อนไขที่แตกต่างกันสำหรับคอลัมน์ต่างๆ พิมพ์ พนักงานขาย ฝ่ายขาย ผลไม้ ที่รัก >1 000

หากต้องการค้นหาข้อมูลที่ตรงตามเงื่อนไขหนึ่งในคอลัมน์หนึ่ง หรือตรงตามเงื่อนไขอื่นในอีกคอลัมน์หนึ่ง ให้ป้อนเงื่อนไขการเลือกในแถวต่างๆ ของช่วงเงื่อนไขการเลือก ตัวอย่างเช่น,

หนึ่งในสองชุดเงื่อนไขสำหรับสองคอลัมน์

เมื่อต้องการค้นหาแถวที่ตรงกับเกณฑ์หนึ่งในสองชุด ซึ่งแต่ละแถวมีเงื่อนไขสำหรับคอลัมน์มากกว่าหนึ่งคอลัมน์ ให้ป้อนเกณฑ์การเลือกในแถวที่แยกจากกัน ตัวอย่างเช่น,

พนักงานขาย ฝ่ายขาย ที่รัก >3 000 บาตูริน >1 500 มีเงื่อนไขมากกว่าสองชุดในหนึ่งคอลัมน์

หากต้องการค้นหาแถวที่ตรงตามเงื่อนไขมากกว่าสองชุด ให้รวมหลายคอลัมน์ด้วย โดยมีหัวเรื่องเดียวกัน- ตัวอย่างเช่น,

วันที่ตีพิมพ์: 2015-10-09; อ่าน: 205 | การละเมิดลิขสิทธิ์เพจ | สั่งเขียนกระดาษ

เว็บไซต์ - Studopedia.Org - 2014-2020. Studiopedia ไม่ใช่ผู้เขียนเนื้อหาที่โพสต์ แต่ให้ใช้งานฟรี(0.002 วิ) ...