การดึงข้อมูลที่สลับ header

[kimudao]

มีข้อมูล สองชีต ชีตแรก เปน raw data sheet 2 ต้องการใส่สูตร เพื่อให้ข้อมูล เรียงตามเฮดเดอร์ที่สลับ โดยที่ ถ้าต้นทาง ไม่มีให้ใส่ค่าว่าง "-" ควรใช้เปน hlookup or index match ดีค่ะ

และเพิ่มเติมสูตร ในช่อง service ในชีต raw data หลังจาก ดึงข้อมูลมาแล้ว ให้เขียนสูตรเพิ่มในช่อง service ในชีต modification ว่า ถ้าขึ้นต้นด้วย ในชีต raw data
AS --- ให้เปลี่ยนเป็นคำว่า save
AX --- ให้เปลี่ยนเป็นคำว่า flex
AF --- ให้เปลี่ยนเป็นคำว่า fast
ให้เปลี่ยนในชีต modification น่ะค่ะ

พิเศษเฉพาะ ช่อง service นอกนั้น ดึงข้อมูลตาม header ที่ตรงกันปกติ แล้วถ้าเจอ ข้อมูลว่าง ก็ให้แสดง คำว่า -
อยากจะได้สูตรที่ flexible ไม่ยาวจนเกินไป
คือ ที่ของเดิม มีคนทำไว้เขาใช้ iferror if match index สองชั้นน่ะค่ะ แต่อ่านแล้วงง ไม่แน่ใจว่าทำไมต้องทำ สูตรนี้ หรือจริงๆ ต้องทำแบบนี้


IFERROR(IF(INDEX('raw data'!$1:$1048576,ROW(),MATCH(Modification!A$1,'raw data'!$1:$1,0))=0,"",(INDEX('raw data'!$1:$1048576,ROW(),MATCH(Modification!A$1,'raw data '!$1:$1,0)))),"-")
เลยไม่แน่ใจว่า เขียนเพื่อครอบคลุมและให้ยืดหยุ่นได้กับข้อมูลด้วยหรือเปล่า ไม่เข้าใจว่า ทำไมต้องมี = 0 และทำไมต้องมี index match สองหน

test.xlsx

[DhitiBank]

สวัสดีครับ

หากให้ผมแนะนำ สูตรก็แทบจะเหมือนกันครับคือ ลองคีย์ที่ A2 ว่า

=IFERROR(IF(INDEX('raw data'!$A$2:$G$12,ROWS(A$2:A2),MATCH(A$1,'raw data'!$A$1:$G$1,0))="","-",INDEX('raw data'!$A$2:$G$12,ROWS(A$2:A2),MATCH(A$1,'raw data'!$A$1:$G$1,0))),"-")

Enter --> คัดลอกไปทางขวาและลงล่าง
จากนั้น เปลี่ยนสูตรใน C2 เป็น

=IFERROR(IF(INDEX('raw data'!$A$2:$G$12,ROWS(C$2:C2),MATCH(C$1,'raw data'!$A$1:$G$1,0))="","-",INDEX({"save","flex","fast"},MATCH(LEFT(INDEX('raw data'!$A$2:$G$12,ROWS(C$2:C2),MATCH(C$1,'raw data'!$A$1:$G$1,0)),2),{"AS","AX","AF"},0))),"-")

Enter --> แล้วคัดลอกลงล่างครับ

อาจมีวิธีที่ใช้สูตรรวบรัดกว่านี้ รอคำตอบจากอาจารย์หรือเพื่อนๆ ท่านอื่นครับ

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

ปล. หากลองดูอีกครั้ง สูตรดังกล่าวก็ไม่ซับซ้อนเท่าไรครับ เพราะคนเก่าเขาใช้ IF มาช่วยตรวจสอบค่าว่าง
ถ้ามองหาแล้วพบ 0 ก็ให้แทนที่ด้วย "-" แต่ถ้าไม่ใช่ก็ให้ใส่ค่าที่พบ
การซ้ำ Index(...,Rows...,Match(...,...,0)) 2 รอบก็เพื่อใช้ตรวจสอบเงื่อนไขในครั้งแรก ส่วนครั้งที่สองก็เพื่อให้แสดงค่านั้นกรณี If ตรวจแล้วเป็น Fasle ครับ

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

[kimudao]

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

[DhitiBank]

จะมีคอลัมน์ service เท่านั้นครับ ที่สูตรแตกต่างจากพวก คีย์สูตรดังกล่าวลงไปในเซลล์ C2 แล้วคัดลอกลงล่างเลยครับ

[kimudao]

อ๋อได้ค่า เดี๋ยวลองดูค่ะ ขอบคุณมากนะคะ

[snasui]

อ๋อได้ค่า เดี๋ยวลองดูค่ะ ขอบคุณมากนะคะ

งดใช้ภาษาแชทตามกฎข้อ 1 ด้านบนครับ

[kimudao]

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

[DhitiBank]

ลองตรวจสอบการสะกดชื่อชีท และชื่อหัวตารางดูครับ
-หัวตารางปลายทางต้องสะกดเหมือนหัวตารางต้นทาง
-ชื่อชีทในสูตรต้องสะกดเหมือนชื่อชีทต้นทาง

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

[kimudao]

พอดี ลองเปลี่ยนสูตร ตรง array เป็น IFERROR(IF(INDEX('Paste data (QV)'!$1:$1048576,ROWS(J$2:J3),MATCH(J$1,'Paste data (QV)'!$A$1:$GD$1,0))="","-",INDEX({"save","flex","fast"},MATCH(LEFT(INDEX('Paste data (QV)'!$1:$1048576,ROWS(J$2:J3),MATCH(J$1,'Paste data (QV)'!$A$1:$GD$1,0)),2),{"AS","AX","AF"},0))),"-") แต่ว่า รันไม่ออก
ลองไปเปลี่ยนใน test แล้วก็ออกมาเป็น - หมดเลยค่ะ เพราะสูตร ของคุณ Dhitibank บอกมาตอนแรกมันสูตร raw data'!$A$2:$G$12 มันจะคลุมแค่ ช่อง ถ้าอนาคตเพิ่มมาก็ต้องมาแก้สูตร คือไม่แน่ใจว่า ที่มัน รันไม่ออกเพราะตรงจุด array นี้หรือเปล่า น่ะค่ะ รบกวนสอบถามหน่อยนะคะ ขอบคุณมากค่ะ

[DhitiBank]

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

[kimudao]

ขออนุญาติซิปไฟล์นะคะ มันแนบไม่ได้
ไฟล์ที่ใช้คือ ช่อง cc in paste data sheet

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

[snasui]

ที่ชีท Modification เซลล์ J2 เปลี่ยนสูตรเป็นด้านล่างครับ

=IFERROR(IF(INDEX('Paste data (QV)'!$1:$1048576,ROWS(J$1:J2),MATCH(J$1,'Paste data (QV)'!$A$1:$GD$1,0))="","-",INDEX({"save","flex","fast"},MATCH(LEFT(INDEX('Paste data (QV)'!$1:$1048576,ROWS(J$1:J2),MATCH(J$1,'Paste data (QV)'!$A$1:$GD$1,0)),2),{"AS","AX","AF"},0))),"-")

Enter > Copy ลงด้านล่าง

การคลุมพื้นที่ทั้งหมดเผื่อไว้ไม่มีผลดีต่อการคำนวน นี่จริงเเหรอ คะ

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

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

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

การทำงานของแต่ละฟังก์ชั่นไม่เหมือนกัน ต้องค่อย ๆ ศึกษา หากใช้งานแล้วติดปัญหาก็สามารถสอบถามกันมาได้ครับ

[kimudao]

ขอบคุณมากนะคะเดี๋ยวจะลองแก้สูตรดูแล้วจะ มาแจ้งผลค่ะ

[kimudao]

ลอง เปลี่ยนสูตรแล้วค่ะ แต่ ข้อมูลยังเพี้ยน อยู่ ไม่ถูกทุกอันเลยน่ะค่ะ เอ มันผิดตรงไหนเหรอคะ

[snasui]

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