ฉันจะรับรายการค่าที่ไม่ต่อเนื่องกันจากแถวใน Excel ได้อย่างไร (โดยไม่สนใจช่องว่าง)

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

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

ฉันต้องทำสิ่งที่คล้ายกันโดยดึงค่าที่เกิดขึ้นครั้งที่ 1 ถึง 10 แยกกัน และสามารถทำได้สำเร็จหลังจากทิ้ง hloookup เรียนรู้ดัชนี + การจับคู่และสูตรอาร์เรย์ และปรับวิธีแก้ปัญหาที่พบทางออนไลน์สำหรับปัญหาที่คล้ายกันกับคอลัมน์ เช่น สิ่งนี้

นี่คือวิธีที่ฉันแก้ไขปัญหาในการดึงข้อมูลเหตุการณ์ที่ 2 ติดต่อกัน:

=IFERROR(INDEX($FR5:$GT5,SMALL(IF($FR5:$GT5>0,COLUMN($FR5:$GT5)-COLUMN($FR5)+1,FALSE),2)),"9999" )

โดยที่ $FR5:$GT5 เป็นช่วงที่ฉันต้องการดึงค่า และ 9999 คือรหัสของฉันสำหรับค่าที่หายไป แค่คิดว่าจะโยนมันไปตรงนั้น คนที่มีทักษะจำกัดอย่างตัวฉันเองอาจจะพบว่ามันมีประโยชน์

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


array-formulas excel-formula excel-2011
person user5225119    schedule 13.08.2015    source แหล่งที่มา
comment
ขอบคุณ นั่นใช้งานได้จริง ยกเว้นว่าฉันต้องทำสิ่งนี้กับชุดข้อมูลหลายชุด สองสามครั้งในชุดข้อมูลแต่ละชุด สำหรับช่วงและจำนวนค่าที่แตกต่างกันที่จะดึงออกมา ดังนั้นฉันจึงต้องอาศัยสูตรที่สามารถตรวจสอบซ้ำได้ ฯลฯ ฉันยังมีผลรวม/การนับในช่วงแหล่งกำเนิดและโชคชะตาของฉันด้วย เพื่อเป็นการตรวจสอบความปลอดภัยว่าค่าทั้งหมดถูกดึงออกมาอย่างถูกต้อง! ไม่ว่าในกรณีใด ขอขอบคุณที่เพิ่มเทคนิค Excel ลงในกระเป๋าของฉัน!   -  person user5225119    schedule 16.08.2015

คำตอบ (1)


arrow_upward
1
arrow_downward

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

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

หากคุณใส่ =COLUMN(A:A) ลงในเซลล์และกรอกไปทางขวา คุณจะได้รับลำดับจำนวนเต็ม เช่น 1, 2, 3, 4 เป็นต้น ในทำนองเดียวกัน หากคุณใส่ =ROW(1:1) ลงในเซลล์แล้วกรอกลงไป คุณจะได้รับ 1, 2, 3, 4 เป็นต้น หนึ่งในสูตรย่อยเหล่านี้สามารถใช้เพื่อเพิ่ม k ของ SMALL ของคุณ ขึ้นอยู่กับว่าคุณต้องการเติมสูตรของคุณเพื่อรับค่าที่ส่งคืนในแถวเดียวหรือกรอกลงไปที่ รับค่าที่ส่งคืนในคอลัมน์เดียว ในลักษณะนี้ การกรอกไปทางขวาหรือลงจะทำให้คุณได้รับผลตอบแทนครั้งที่สอง สาม ฯลฯ

หากต้องการรับค่าที่ส่งคืนติดต่อกันให้ใช้ สูตรอาร์เรย์

=IFERROR(INDEX(5:5, SMALL(IF($FR5:$GT5>0, COLUMN($FR:$GT)), COLUMN(A:A))),"9999")

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

=IFERROR(INDEX($5:$5, SMALL(IF(FR$5:GT$5>0, COLUMN(FR:GT)), ROW(1:1))),"9999")

กรอกตามความจำเป็นเพื่อจับค่าทั้งหมด

สูตรอาร์เรย์จะต้องปิดท้ายด้วย Ctrl+Shift+Enter↵ เมื่อป้อนลงในเซลล์แรกอย่างถูกต้องแล้ว ก็สามารถเติมหรือคัดลอกลงหรือไปทางขวาได้เหมือนกับสูตรอื่นๆ

ฉันไม่เคยเป็นแฟนกายกรรมทางคณิตศาสตร์ (เช่น COLUMN($FR5:$GT5)-COLUMN($FR5)+1) ที่สูตรดั้งเดิมของคุณใช้กำหนดคอลัมน์ (หรือแถว) ในรูปแบบสูตรนั้นมาก่อน ฉันได้ตัดมันลงอย่างมากในสูตรที่เทียบเท่าข้างต้น แต่มันมีค่าเท่ากัน

person Community    schedule 15.08.2015
comment
ขอบคุณ! นั่นทำงานได้อย่างยอดเยี่ยม ฉันขอขอบคุณที่คุณสละเวลาอธิบายว่าทำไมมันถึงได้ผลเช่นกัน :-) ฉันสงสัยว่าวิธีแก้ปัญหานี้ติดอยู่ตรงหน้าฉันมาก...555 การมีแนวทางที่หรูหรายิ่งขึ้นในโซลูชันที่ n ของฉันเช่นกัน - person user5225119; 16.08.2015