:D snasui.com ยินดีต้อนรับ :D
ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย :thup: สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ :arrow: ระบุ Version ของ Excel
:!: โปรดทราบ :!:
  1. กรุณาอ่านกฎการใช้บอร์ด (Forum rules) ในตำแหน่งด้านบนของแต่ละบอร์ด
  2. การสมัครสมาชิก การ Login การกู้คืนรหัสผ่าน
    1. สมัครสมาชิกดูขั้นตอนได้ที่ :arrow: สมัครสมาชิก
    2. Login เข้าระบบโดยคลิกปุ่ม Login ตรงมุมขวาบนของหน้านี้ :roll:
    3. การ Login ผ่าน Facebook ดูวิธีที่ :arrow: Login ผ่าน Facebook
    4. ลืมรหัสผ่านสามารถรับรหัสใหม่ได้ที่ :arrow: Reset รหัสผ่าน
  3. มีปัญหาการใช้งาน แจ้งผู้ดูแลระบบได้ที่ :arrow: ติดต่อผู้ดูแลระบบ
  4. กำหนดการตั้งค่าส่วนตัว เช่นตั้งค่าภาษาเป็นไทยหรืออังกฤษดูได้ที่ :arrow: ตั้งค่าส่วนตัว
  5. การตั้งและตอบกระทู้ดูได้ที่ :arrow: วิธีการตั้งและตอบกระทู้
  6. การจัดรูปแบบตัวอักษรด้วย bbcode ในช่องแสดงความคิดเห็นดูได้ที่ :arrow: จัดรูปแบบตัวอักษร
  7. กำหนดขนาดตัวอักษรใน Browser ดูได้ที่ :arrow: กำหนดขนาดตัวอักษรใน Browser

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

ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
  1. ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
  2. ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
  3. กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
  4. กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
  5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
  6. กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#1

Post by 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
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

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

#2

Post by 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 ครับ

เวลานำไปใช้จริง ก็ปรับช่วงสีน้ำเงินให้สอดคล้องกับข้อมูลต้นทางครับ
Last edited by DhitiBank on Sat Sep 05, 2015 5:31 pm, edited 2 times in total.
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#3

Post by kimudao »

อันี้ต้องทำสูตรแยกหรือเปล่าคะ ถ้าอยากจะให้มันอยู่ในช่องเดียวกันเลยใน ชีต modification ช่อง service สูตรเพิ่มเติมนี่ต้องการแค่ ช่องนี้ นอกนั้นก็ใช้สูตร เดิมตอนแรก ทุกช่องน่ะค่ะ :)
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

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

#4

Post by DhitiBank »

จะมีคอลัมน์ service เท่านั้นครับ ที่สูตรแตกต่างจากพวก คีย์สูตรดังกล่าวลงไปในเซลล์ C2 แล้วคัดลอกลงล่างเลยครับ
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#5

Post by kimudao »

อ๋อได้ค่า เดี๋ยวลองดูค่ะ ขอบคุณมากนะคะ
User avatar
snasui
Site Admin
Site Admin
Posts: 30736
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:

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

#6

Post by snasui »

kimudao wrote:อ๋อได้ค่า เดี๋ยวลองดูค่ะ ขอบคุณมากนะคะ
:D งดใช้ภาษาแชทตามกฎข้อ 1 ด้านบนครับ :roll:
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#7

Post by kimudao »

ขอโทษนะคะ ลืมตัวไปค่ะ
อยากจะรบกวนสอบถามเพิ่ม คือลองเอาสูตรไปลองใช้กับข้อมูลอื่น และมีการเปลี่ยนชื่อ ชีต แต่ค่าที่ออกมามันมาไม่ครบและผิด ด้วยน่ะค่ะ ไม่แน่ว่าเป็นเพราะอะไร ทุกอย่างก็ถูกแล้วนะคะ
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

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

#8

Post by DhitiBank »

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

หรือหากยังติดปัญหาอยู่ รบกวนแนบไฟล์ที่ได้ลองใส่สูตรแล้วมาดูครับ เพื่อนๆ จะได้ช่วยกันดูและแนะนำได้ตรงจุด :)
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#9

Post by 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 นี้หรือเปล่า น่ะค่ะ รบกวนสอบถามหน่อยนะคะ ขอบคุณมากค่ะ
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

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

#10

Post by DhitiBank »

แนบไฟล์ตัวอย่างมาด้วยได้ไหมครับ จะได้ตรวจได้ง่ายว่าปัญหาเกิดจากอะไรครับ หากดูคร่าวๆ ตอนนี้ปัญหาคงเป็นการระบุช่วงอ้างอิงในสูตร Index เพราะการระบุอย่างนั้นเท่ากับคลุมเซลล์ที่มีการเขียนสูตรลงไปด้วยเป็นการอ้างอิงค่าที่ตัวเอง นอกจากนี้ การระบุช่วงกว้างเผื่อไว้ทั้งชีทขนาดนั้น จะมีปัญหากับการคำนวณได้นะครับ คือเผื่อเกินความจำเป็นครับ
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#11

Post by kimudao »

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

ส่วนช่อง ที่ใช้ใน modification คือ serivcelevel
พอดีข้อมูลบริษัท เลยต้อง ลบส่วนอื่นทิ้งน่ะค่ะ เอาแค่ช่องที่มีปัญหา
ที่ใส่สูตรอย่างที่แจ้งข้างบนแล้ว สูตรไม่มา
ส่วนที่แจ้งมาว่า การคลุมพื้นที่ทั้งหมดเผื่อไว้ไม่มีผลดีต่อการคำนวน นี่จริงเเหรอ คะ
คือเราควรจะครอบเฉพาะพื้นที่จริงๆ และต้องแก้ทุกครั้งที่ เอาข้อมูลใหม่ที่มีข้อมูลเปลี่ยนแปลงมากขึ้นน้อยลง อย่างนี้ตลอดเหรอคะ พอดี สูตรเดิม เขาทำไว้เขาครอบชีตพื่นที่ทั้งหมดให้เลยน่ะค่ะ เลย คิดว่ามันคงคลุมเพื่อยืดหยุ่น ด้วย
ลองดูไฟล์และช่วยหน่อยนะคะ ว่าทำไมสูตรมันดึงมา บางที ดึงมาผิด คำที่ตั้งไว้ และบาง row ก็ไม่มา ทั้งๆที่เขียนเงื่อนไขไว้
ตอนแรกคิวว่าติดช่องว่างก็ลอง ทริมไว้ด้วย
ขอบคุณมากค่ะ
You do not have the required permissions to view the files attached to this post.
User avatar
snasui
Site Admin
Site Admin
Posts: 30736
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:

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

#12

Post by snasui »

:D ที่ชีท 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 ลงด้านล่าง
kimudao wrote:การคลุมพื้นที่ทั้งหมดเผื่อไว้ไม่มีผลดีต่อการคำนวน นี่จริงเเหรอ คะ
โดยทั่วไปจะเป็นเช่นนั้นครับ จากสูตรนี้สำหรับในช่วงของ Index สามารถคลุมไว้เยอะได้เท่าที่ต้องการครับ แต่ใน Match ควรคลุมเท่าที่มีข้อมูลหรือเพียงเท่าที่ข้อมูลสามารถขยายไปถึง

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

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

การทำงานของแต่ละฟังก์ชั่นไม่เหมือนกัน ต้องค่อย ๆ ศึกษา หากใช้งานแล้วติดปัญหาก็สามารถสอบถามกันมาได้ครับ
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#13

Post by kimudao »

ขอบคุณมากนะคะเดี๋ยวจะลองแก้สูตรดูแล้วจะ มาแจ้งผลค่ะ
kimudao
Member
Member
Posts: 12
Joined: Sun Aug 30, 2015 10:06 am

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

#14

Post by kimudao »

ลอง เปลี่ยนสูตรแล้วค่ะ แต่ ข้อมูลยังเพี้ยน อยู่ ไม่ถูกทุกอันเลยน่ะค่ะ เอ มันผิดตรงไหนเหรอคะ
User avatar
snasui
Site Admin
Site Admin
Posts: 30736
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:

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

#15

Post by snasui »

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