การเรียงลำดับแถวใน Excel การเรียงลำดับข้อมูลใน Excel จัดเรียงตามหลายคอลัมน์

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

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

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

วิธีจัดเรียงแผ่นงาน (ตาราง รายการ) ใน Excel

ในตัวอย่างต่อไปนี้ เราจะเรียงลำดับแบบฟอร์มการสั่งซื้อเสื้อยืดตาม นามสกุล(คอลัมน์ C) และจัดเรียงตามลำดับตัวอักษร

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

วิธีจัดเรียงช่วงใน Excel

ในตัวอย่างต่อไปนี้ เราจะเลือกตารางเล็กๆ ที่แยกต่างหากในเวิร์กชีต Excel เพื่อจัดเรียงจำนวนเสื้อยืดที่สั่งซื้อในวันที่กำหนด

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

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

การเรียงลำดับมีสามประเภท:

ตามลำดับจากน้อยไปหามาก
ตามลำดับจากมากไปน้อย
ในลำดับที่กำหนดเอง

การเรียงลำดับรายการจากน้อยไปมากหมายถึงการเรียงลำดับรายการตามลำดับ: 0 ถึง 9 ช่องว่าง สัญลักษณ์ ตัวอักษร A ถึง Z หรือ A ถึง Z และจากมากไปน้อย - ในลำดับย้อนกลับ ผู้ใช้ระบุลำดับการจัดเรียงแบบกำหนดเองในกล่องโต้ตอบ "ตัวเลือก" บนแท็บ "รายการ" ซึ่งเปิดโดยคำสั่ง "ตัวเลือก" ในเมนู "เครื่องมือ" และลำดับการจัดเรียงนี้จะแสดงใน "ตัวเลือกการเรียงลำดับ" " กล่องโต้ตอบ (รูปที่ 1)

ข้าว. 1.

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

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

ข้าว. 2.

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

ข้าว. 3.

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

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

อัลกอริทึมสำหรับการเรียงลำดับบันทึกตามคอลัมน์เดียวมีดังนี้:

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

รูปที่ 4 และ 5 แสดงส่วนของรายการก่อนเรียงลำดับ และหลังการเรียงลำดับ "จากน้อยไปหามาก" ด้วย "หมายเลขคลังสินค้า" หนึ่งคอลัมน์

ข้าว. 4.

ข้าว. 5.

เมื่อเรียงลำดับรายการตามหลายคอลัมน์ แถวที่มีค่าเหมือนกันในคอลัมน์แรกที่เรียงลำดับ (เรียงลำดับตาม) จะถูกเรียงลำดับตามลำดับที่กำหนดโดยส่วนหัวที่ระบุในรายการ จากนั้นตาม และแถวที่มีค่าเหมือนกันในรายการ สองคอลัมน์แรกที่เรียงลำดับจะถูกจัดเรียงตามชื่อที่ระบุในรายการ "Last By" กล่าวอีกนัยหนึ่ง คอลัมน์ที่ระบุในรายการ Next By และ Last By จะไม่ส่งผลต่อลำดับการจัดเรียงของคอลัมน์แรกที่ถูกจัดเรียง แต่จะทำให้รายการที่เรียงลำดับไม่ชัดเจน

อัลกอริทึมสำหรับการเรียงลำดับบันทึกตามสองคอลัมน์ขึ้นไปมีดังนี้

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

อัลกอริทึมสำหรับการจัดเรียงข้อมูลตามแถว

หากต้องการจัดเรียงคอลัมน์ในรายการใหม่ คุณต้องจัดเรียงตามค่าในแถว:

ระบุเซลล์ในรายการที่จัดเรียงได้
ในเมนู "ข้อมูล" เลือกคำสั่ง "การเรียงลำดับ"
ในหน้าต่างการเรียงลำดับช่วง ให้คลิกปุ่มตัวเลือก
ตั้งค่าปุ่มตัวเลือก Sort เป็น Range Columns แล้วคลิก OK
ในหน้าต่าง Sort Range ให้เลือกแถวที่คุณต้องการเรียงลำดับคอลัมน์ในรายการ Sort By จากนั้น By หรือ Last By
คลิกตกลงเพื่อจัดเรียง

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

อัลกอริทึมการเรียงลำดับตามลำดับที่กำหนดเอง

เลือกเซลล์ในรายการ
ในเมนู "ข้อมูล" เลือกคำสั่ง "การเรียงลำดับ"
เลือกส่วนหัวคอลัมน์เพื่อเรียงลำดับจากรายการ "จัดเรียงตาม"
ตั้งสวิตช์ไปที่ตำแหน่ง "จากน้อยไปมาก" หรือ "จากมากไปน้อย"
คลิกปุ่ม "ตัวเลือก";
ในรายการแบบเลื่อนลง "เรียงลำดับตามคีย์แรก" ให้เลือกลำดับการจัดเรียงที่ต้องการแล้วคลิกตกลง
คลิกตกลงในหน้าต่าง Sort Range เพื่อทำการเรียงลำดับให้เสร็จสิ้น

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

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

ตัวเลข

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

ตอนนี้เรามีข้อมูลในภาษา C จัดเรียงจากมากไปน้อย

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

ตามลำดับตัวอักษร

ทำตามหลักการเดียวกันกับที่อธิบายไว้ข้างต้น เลือกช่วงที่ต้องการแล้วกดปุ่ม "การเรียงลำดับและกรอง". รายการในเมนูแบบเลื่อนลงมีการเปลี่ยนแปลง เลือกจาก "A ถึง Z" หรือจาก "Z ถึง A"

รายชื่อในตัวอย่างจะเรียงลำดับตามตัวอักษร

ตามวันที่

หากต้องการเรียงลำดับวันที่ใน Excel ขั้นแรกให้ใส่ใจกับรูปแบบที่กำหนดไว้สำหรับเซลล์ที่เขียนไว้ เลือกพวกเขาและบนแท็บ "หน้าแรก" ดูที่กลุ่ม "หมายเลข" รูปแบบที่ดีที่สุดคือ "วันที่" สั้นหรือยาว หรือ "(ทุกรูปแบบ)" - สามารถเขียนวันที่ได้หลายวิธี: DD.MM.YYYY, DD.MMM, MMM.YY

ประเด็นนี้สำคัญมาก เพราะไม่เช่นนั้นวันที่อาจเรียงลำดับง่ายๆ โดยเริ่มจากตัวเลขสองตัวแรกจากน้อยไปหามาก หรือตามเดือนตามลำดับตัวอักษร

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

ตามเซลล์หรือสีข้อความ

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

เลือกช่วงทั้งหมดแล้วคลิกที่ปุ่ม "การเรียงลำดับและกรอง"และเลือกจากเมนู "ปรับแต่งได้...".

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

ตอนนี้คุณต้องมีคอลัมน์ที่มีตัวเลขสีเขียว คลิกที่ปุ่ม "เพิ่มระดับ". การตั้งค่าทั้งหมดเหมือนกัน เพียงเลือก “สีเขียว” คลิกตกลง

คอลัมน์ของเราจัดเรียงดังนี้

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

ตอนนี้คอลัมน์ของเราจัดเรียงตามสีข้อความและเรียงลำดับข้อมูลจากน้อยไปมาก

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

ตาราง

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

ลองเรียงลำดับชั้นเรียนจากน้อยไปหามากและให้คะแนนเฉลี่ยในลักษณะเดียวกัน

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

หากต้องการทำเช่นเดียวกันกับคะแนนเฉลี่ย ให้คลิกที่ปุ่ม "เพิ่มระดับ". ในส่วน "คอลัมน์" เลือก "คะแนนเฉลี่ย" คลิกตกลง

ข้อมูลในตารางจะถูกจัดเรียง

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

จัดเรียงคอลัมน์นี้ตามสีของเซลล์: อันดับแรกจะมีเด็กผู้หญิงแล้วจึงเป็นเด็กผู้ชาย เลือกทั้งตารางอีกครั้งคลิก "เรียงลำดับ" - "ปรับแต่งได้...".

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

คลิกที่ปุ่ม "เพิ่มระดับ". ในส่วน "คอลัมน์" เลือก "ชื่อ" จัดเรียง - "สีของเซลล์" ลำดับ - "สีชมพู" "ด้านบน"

ตอนนี้ใช้ลูกศรเพื่อย้ายบรรทัดนี้ไปที่ด้านบนของรายการ คลิกตกลง

ตารางที่มีการจัดเรียงข้อมูลที่มีลักษณะเช่นนี้

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

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

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

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

ตอนนี้เราจะเพิ่มคอลัมน์ที่จะทำการเรียงลำดับ สิ่งแรกในกรณีของเราคือคอลัมน์ สินค้า. ในสนาม การเรียงลำดับเราจัดแสดง ความหมายและติดตั้ง คำสั่งจาก A ถึง Z.

ตอนนี้ผลิตภัณฑ์จะถูกจัดเรียงตามคอลัมน์แรกก่อนและตามด้วยคอลัมน์ที่สอง ในกรณีนี้ การเรียงลำดับที่สองจะไม่ส่งผลต่อการเรียงลำดับครั้งแรก คุณสามารถจัดเรียงตามคอลัมน์เพิ่มเติมได้ในลักษณะเดียวกัน

อย่างไรก็ตาม การเรียงลำดับสามารถทำได้ตามแถวด้วย ฉันจะบอกวิธีการทำเช่นนี้ในบทเรียนถัดไป

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

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

ในบทนี้ ฉันจะบอกวิธีใส่เครื่องหมายบวกหรือศูนย์หน้าตัวเลขที่ตำแหน่งเริ่มต้นของเซลล์ใน Excel ลองจินตนาการถึงสถานการณ์ที่คุณต้องป้อนหมายเลขโทรศัพท์ลงในเซลล์ในรูปแบบ "+7 987..." โดยปกติ Excel จะลบเครื่องหมายบวกนี้ออก

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

ให้มีตารางประกอบด้วย 2 คอลัมน์ คอลัมน์หนึ่งเป็นข้อความ: รายการผลไม้; และอันที่สองเป็นตัวเลข ปริมาณการขาย(ดูไฟล์ตัวอย่าง)

ปัญหาที่ 1 (การเรียงลำดับตารางตามคอลัมน์ตัวเลข)

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

เพื่อความชัดเจนค่าในคอลัมน์ ปริมาณการขายเน้นด้วย () ค่าที่ซ้ำกันจะถูกเน้นด้วยสีเหลืองด้วย

บันทึก: ปัญหาการเรียงลำดับคอลัมน์เดียว (รายการ) ได้รับการแก้ไขแล้วในบทความและ

โซลูชัน1

ถ้าคอลัมน์ตัวเลขรับประกันว่าไม่มีค่า ปัญหาก็แก้ได้ง่าย:

จัดเรียงคอลัมน์ตัวเลขโดยใช้ฟังก์ชัน LARGE() (ดูบทความ)
ฟังก์ชัน VLOOKUP() หรือฟังก์ชันอื่นๆ มากมาย ดัชนี()+จับคู่()เลือกค่าจากคอลัมน์ข้อความตามค่าตัวเลขที่เกี่ยวข้อง

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

ดังนั้นกลไกการเรียงลำดับจะต้องดำเนินการแตกต่างออกไป

ดัชนี(ยอดขาย;
รอบ(REMAT(ใหญ่ที่สุด(
--(COUNTIF(ยอดขาย;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
แถว()-ROW($E$6));1)*1000;0)
)

สูตรนี้จะเรียงลำดับคอลัมน์ ปริมาณการขาย(ช่วงไดนามิก ฝ่ายขาย) ตามลำดับจากมากไปน้อย ไม่อนุญาตให้มีช่องว่างในตารางต้นฉบับ จำนวนแถวในตารางต้นฉบับต้องน้อยกว่า 1,000 แถว

ลองดูสูตรโดยละเอียดเพิ่มเติม:

สูตร COUNTIF(ยอดขาย;"<"&Продажи) ส่งคืนอาร์เรย์ (4:5:0:2:7:1:3:5) แปลว่า เลข 64 (จากเซลล์ B7 โต๊ะเดิมคือ หมายเลขแรกจากช่วง ฝ่ายขาย) มากกว่า 4 ค่าจากช่วงเดียวกัน หมายเลข 74 (จากเซลล์ B8 โต๊ะเดิมคือ หมายเลขที่สองจากช่วง ฝ่ายขาย) มากกว่า 5 ค่าจากช่วงเดียวกัน หมายเลข 23 ถัดไปคือหมายเลขที่เล็กที่สุด (ไม่ใหญ่กว่าใคร) เป็นต้น
ทีนี้ลองเปลี่ยนอาร์เรย์ของจำนวนเต็มด้านบนให้เป็นอาร์เรย์ของตัวเลขที่มีเศษส่วน โดยที่เศษส่วนจะมีหมายเลขตำแหน่งของตัวเลขในอาร์เรย์: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). สิ่งนี้ถูกนำไปใช้โดยนิพจน์ &","&REPEAT("0";3-LENGTH(ROW(การขาย)-ROW($E$6)))&ROW(การขาย)-ROW($E$6))ในส่วนนี้ของสูตรจะมีการจำกัดไม่ให้เกิน 1,000 แถวในตารางต้นฉบับ (ดูด้านบน) สามารถเปลี่ยนแปลงได้อย่างง่ายดายหากต้องการ แต่ไม่มีจุดหมาย (ดูหัวข้อเกี่ยวกับความเร็วในการคำนวณด้านล่าง)
ฟังก์ชัน LARGE() เรียงลำดับอาร์เรย์ด้านบน
ฟังก์ชัน ROD() ส่งกลับส่วนที่เป็นเศษส่วนของตัวเลข ซึ่งเป็นหมายเลขตำแหน่ง/1000 เช่น 0.005
ฟังก์ชัน ROUND() หลังจากคูณด้วย 1000 จะปัดเศษเป็นจำนวนเต็มและส่งกลับหมายเลขตำแหน่ง ตอนนี้หมายเลขตำแหน่งทั้งหมดสอดคล้องกับหมายเลขคอลัมน์ ปริมาณการขายเรียงตามลำดับจากมากไปน้อย
ฟังก์ชัน INDEX() ที่กำหนดหมายเลขตำแหน่ง จะส่งคืนหมายเลขที่ตรงกัน

สามารถเขียนสูตรที่คล้ายกันเพื่อแสดงค่าในคอลัมน์ได้ ผลไม้=INDEX(ผลไม้,รอบ(...))

ในไฟล์ตัวอย่าง เนื่องจากการพิจารณาความเร็วในการคำนวณ (ดูด้านล่าง) ส่วนประเภทเดียวกันของสูตร กล่าวคือ ทุกอย่างภายในฟังก์ชัน ROUND() จะถูกวางไว้ในคอลัมน์ที่แยกจากกัน เจ . ดังนั้น สูตรสุดท้ายในตารางที่เรียงลำดับจะมีลักษณะดังนี้: =ดัชนี(ผลไม้,J7)และ =ดัชนี(ยอดขาย,J7)

นอกจากนี้ เมื่อเปลี่ยนฟังก์ชัน LARGE() เป็น SMALL() ในสูตรอาร์เรย์ เราจะเรียงลำดับจากน้อยไปหามาก

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

การทดสอบ

ตอนนี้เรามาเพิ่มแถวใหม่ลงในตารางต้นฉบับ ในตารางที่เรียงลำดับแบบไดนามิก เราต้องได้รับการเรียงลำดับที่เหมาะสม

1. ไปที่เซลล์ ก15 ตารางต้นฉบับป้อนคำ แครอท;
2. ไปที่เซลล์ B15 เข้า ปริมาณการขายแครอท = 25;
3. หลังจากป้อนค่าลงในคอลัมน์แล้ว ดี และ อี ตารางที่เรียงลำดับจากมากไปน้อยจะปรากฏขึ้นโดยอัตโนมัติ
4. ในตารางที่เรียงลำดับ แถวใหม่จะแสดงเป็นอันดับรองจากสุดท้าย

ความเร็วในการคำนวณสูตร

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

แนวทางอื่นในการเรียงลำดับตาราง

มาจัดเรียงแถวของตารางต้นฉบับโดยใช้ตัวกรองมาตรฐาน (เลือกส่วนหัวของตารางต้นฉบับแล้วคลิก CTRL+SHIFT+L). เลือกการเรียงลำดับที่ต้องการจากรายการแบบเลื่อนลง

เราจะได้เวอร์ชันของตารางที่เหมือนกับของเรา แต่เมื่อเพิ่มค่าใหม่ลงในตาราง เราจะต้องใช้ตัวกรองอีกครั้ง

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

เราจะได้เวอร์ชันของตารางที่เหมือนกับของเรา แต่เมื่อเพิ่มค่าใหม่ เราก็จะต้องใช้ตัวกรองอีกครั้งด้วย

เช่นเดียวกับในปัญหาก่อนหน้านี้ สมมติว่ามีการซ้ำกันในคอลัมน์ที่ใช้การเรียงลำดับ (ชื่อของผลไม้ซ้ำกัน)

ในการจัดเรียงตาราง คุณจะต้องสร้างคอลัมน์บริการ 2 คอลัมน์ (D และ E)

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

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

ในคอลัมน์ E ให้ป้อนสูตรปกติ:

=COUNTIF($D$6:D6,D7)+D7

สูตรนี้คำนึงถึงการซ้ำของค่าข้อความและปรับ "อันดับ" ตอนนี้ค่าที่แตกต่างกันของ Apple จะสอดคล้องกับ "อันดับ" ที่แตกต่างกัน - 7 และ 8 ซึ่งช่วยให้คุณสามารถแสดงรายการค่าที่เรียงลำดับได้ เมื่อต้องการทำเช่นนี้ ให้ใช้สูตร (คอลัมน์ G):

=ดัชนี($B$7:$B$14,MATCH(ROW()-ROW($G$6),$E$7:$E$14,0))

สูตรที่คล้ายกันจะแสดงปริมาณการขายที่สอดคล้องกัน (คอลัมน์ H)

ปัญหา 2.1 (การเรียงลำดับสองระดับ)

ตอนนี้เรามาจัดเรียงตารางเดิมอีกครั้งตามปริมาณการขาย แต่ตอนนี้สำหรับการทำซ้ำค่า (ในคอลัมน์ A มีค่าสามค่า 74) เราจะแสดงค่าที่สอดคล้องกันตามลำดับตัวอักษร

ในการทำเช่นนี้ เราจะใช้ผลลัพธ์ของปัญหา 1.1 และปัญหา 2