เรียงตามสูตร

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

วิธีที่ 1 ข้อมูลตัวเลข

หากรายการมีเฉพาะข้อมูลที่เป็นตัวเลข การเรียงลำดับสามารถทำได้ง่ายโดยใช้ฟังก์ชัน น้อยที่สุด (เล็ก) и สาย (แถว):

ฟังก์ชัน น้อยที่สุด (เล็ก) ดึงองค์ประกอบที่เล็กที่สุดลำดับที่ n ออกจากอาร์เรย์ (คอลัมน์ A) เหล่านั้น. SMALL(A:A;1) คือจำนวนที่น้อยที่สุดในคอลัมน์ SMALL(A:A;2) คือจำนวนที่น้อยที่สุดเป็นอันดับสอง และต่อไปเรื่อยๆ

ฟังก์ชัน สาย (แถว) ส่งกลับหมายเลขแถวสำหรับเซลล์ที่ระบุ เช่น ROW(A1)=1, ROW(A2)=2 เป็นต้น ในกรณีนี้ จะใช้เป็นตัวสร้างลำดับของตัวเลข n=1,2,3… สำหรับ รายการเรียงของเรา ด้วยความสำเร็จแบบเดียวกัน คุณสามารถสร้างคอลัมน์เพิ่มเติม เติมด้วยตนเองด้วยลำดับตัวเลข 1,2,3 … และอ้างอิงถึงคอลัมน์แทนฟังก์ชัน ROW

วิธีที่ 2 รายการข้อความและสูตรปกติ

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

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

ในเวอร์ชันภาษาอังกฤษจะเป็น:

=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)

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

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

ในที่สุด มันก็ยังคงเป็นเพียงการดึงชื่อออกจากรายการด้วยตัวเลข ในการทำเช่นนี้ คุณสามารถใช้สูตรต่อไปนี้:

ฟังก์ชัน เปิดเผยมากขึ้น (การแข่งขัน) ค้นหาในคอลัมน์ B สำหรับหมายเลขซีเรียลที่ต้องการ (1, 2, 3 ฯลฯ) และที่จริงแล้วจะส่งคืนหมายเลขของบรรทัดที่มีหมายเลขนี้ การทำงาน ดัชนี (ดัชนี) ดึงชื่อที่หมายเลขบรรทัดนี้ออกจากคอลัมน์ A

วิธีที่ 3: สูตรอาร์เรย์

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

ในเซลล์ E1 ให้คัดลอกสูตรของเรา:

=INDEX(รายการ; MATCH(SMALL(COUNTIF(รายการ; “<"&รายการ); ROW(1:1)); COUNTIF(รายการ; "<"&รายการ); 0))

หรือในเวอร์ชันภาษาอังกฤษ:

=INDEX(รายการ, MATCH(SMALL(COUNTIF(รายการ, «<"&รายการ), ROW(1:1)), COUNTIF(รายการ, "<"&รายการ), 0))

และผลักดัน Ctrl + Shift + Enterเพื่อป้อนเป็นสูตรอาร์เรย์ จากนั้นสูตรผลลัพธ์สามารถคัดลอกตามความยาวทั้งหมดของรายการได้

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

ขั้นแรก ต้องตั้งค่าช่วงรายการแบบไดนามิก เมื่อต้องการทำสิ่งนี้ เมื่อสร้าง คุณต้องระบุไม่ใช่ช่วงคงที่ C3:C10 แต่เป็นสูตรพิเศษที่จะอ้างอิงถึงค่าที่มีอยู่ทั้งหมด โดยไม่คำนึงถึงตัวเลข คลิก Alt + F3 หรือเปิดแท็บ สูตร – ผู้จัดการชื่อ (สูตร — ตัวจัดการชื่อ), สร้างชื่อใหม่และในช่อง ลิงค์ (อ้างอิง) ป้อนสูตรต่อไปนี้ (ฉันคิดว่าช่วงของข้อมูลที่จะจัดเรียงเริ่มจากเซลล์ C1):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=ออฟเซ็ต(C1,0,0,SCHÖTZ(C1:C1000),1)

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

=การอ้างอิง(INDEX(รายการ; MATCH(SMALL(COUNTIF(รายการ; “<"&รายการ); ROW(1:1)); COUNTIF(รายการ; "<"&รายการ); 0))-

=IFERROR(NDEX(รายการ, MATCH(SMALL(COUNTIF(รายการ, «<"&รายการ), ROW(1:1)), COUNTIF(รายการ, "<"&รายการ), 0));"")

มันจับข้อผิดพลาด #NUMBER และแสดงผลเป็นโมฆะ (เครื่องหมายอัญประกาศเปล่า) แทน

:

• จัดเรียงช่วงตามสี
• สูตรอาร์เรย์คืออะไรและทำไมจึงจำเป็น
• การเรียงลำดับและอาร์เรย์แบบไดนามิกใน Office 365 . ใหม่