การสร้างสูตร array แทนที่จะ pivot ทุกครั้ง

[aerojen]

ในชีทหน้า data ต้องการให้ คอลัมน์ที่ซ้ำกันด้วยเงื่อนไขที่ remark รวมกันให้ได้ดังตารางขวาสุดในหน้า summary โดยใช้สูตร array
เดิมเคยใช้สูตร IFERROR, INDEX, FREQUENCY ตามตัวอย่างที่ได้เรียนรู้ในห้องกระทู้นี้แต่ใช้ไม่ได้เพราะไม่ตรงตามเงื่อนไข

Test.xls

[snasui]

ตามตัวอย่างที่แนบมานั้นหากจะทำต้องใช้ VBA เนื่องจากมีการเชื่อมข้อความด้วย ซึ่งต้องเขียนมาก่อน ติดตรงไหนค่อยถามกันต่อครับ

[aerojen]

ขอบคุณค่ะ คุ้นๆๆเหมือนกันว่าถ้าจะเชื่อมข้อความต้องใช้ VBA แต่ถ้าเปลี่ยนมาเป็น ตารางใหม่ (ปรับตามด้านขาวสุด หน้า summary ช่วยแนะนำการสร้างสูตรไหนได้บ้างเพื่อไปประยุกต์ใช้หน่อยค่ะ)

Test#rev.xls

[snasui]

ลองตามนี้ครับ

1. ชีท Data เซลล์ H4 คีย์
   =C4&D4&E4&F4&G4
   Enter > Copy ลงด้านล่าง
2. ชีท Summary เซลล์ J2 คีย์
   =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX(Data!C$4:C$90,SMALL(IF(FREQUENCY(MATCH(Data!$H$4:$H$90,Data!$H$4:$H$90,0),ROW(Data!$H$4:$H$90)-ROW(Data!$H$4)+1),ROW(Data!$H$4:$H$90)-ROW(Data!$H$4)+1),ROWS(J$4:J4)))))
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

ข้อมูลที่ได้ไม่ได้เรียงให้เหมือนที่ได้จาก PivotTable หากต้องการให้เรียง ควรเรียงจากในชีท Data ครับ

[aerojen]

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

[snasui]

ถ้าข้อมูลมีทั้งตัวเลข และ อักษร รวมกัน ยังใช้ CHAR(255) อยู่อีกหรือเปล่าค่ะ

สำหรับ Char(255) ใช้เพื่อดึงข้อมูลทีเป็น Text เท่านั้น ไม่สามารถใช้ดึงกตัวเลขหรือตัวเลขปนตัวอักษรได้ครับ สำหรับการดึงข้อมูลที่เป็นตัวเลขใช้ 9.99999999999999e307 แทน Char(255) นอกจากนี้ตรง Choose({1,2},"" ให้เปลี่ยนเป็น Choose({1,2},0 ครับ

กรณีที่ข้อมูลปนกันคงต้องเปลี่ยนสูตร ลองแนบไฟล์ตัวอย่างมาใหม่ให้เหมือนกับข้อมูลที่เป็นอยู่จริงครับ

[aerojen]

ขอบคุณล่ะ กำลังอ่านสูตร CHAR อยู่พอดีเลยค่ะ
ได้ปรับไฟล์ใหม่ และลองปรับเปลี่ยนตามกระทู้ที่โพสต์ต่างๆแต่ก็ยังไม่ได้เลยค่ะ
ขอความรู้หน่อยค่ะ

Test%23rev.xls

[snasui]

ไฟล์ที่แนบมานั้นปัญหาคืออะไร ต้องการคำตอบเป็นอย่างไรครับ

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

[aerojen]

จะเห็นว่า คอลัมน์ Date วันที่ที่ต้องการอ้างอิง จาก Sheet data ไม่มาอยู่ใน sheet summary และพอถึง row ที่ 52 ต้องทำการป้อนสูตรใหม่ เพราะผลลัพธ์ ว่างเปล่าทุกเซลล์ หลังจาก rowที่ 52 ค่ะ ตอนแรกนึกว่าเกิดจาก ความสามารถของสูตร CHAR แต่เห็นท่านแนะนำว่า ถ้าอักษรและตัวเลขปนในเซลล์เดียวกัน ก็สามารถใช้สูตร CHAR ได้ค่ะ
ช่วยชี้แจงหน่อยค่ะ

[snasui]

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

=LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX(Data!C$4:C$90,SMALL(IF(FREQUENCY(MATCH(Data!$I$4:$I$90,Data!$I$4:$I$90,0),ROW(Data!$I$4:$I$90)-ROW(Data!$I$4)+1),ROW(Data!$I$4:$I$90)-ROW(Data!$I$4)+1),ROWS(J$4:J4)))))

[aerojen]

อืม.... คือว่าน่าจะไม่ได้ติดที่เขียนสูตรผิดอะไร แต่น่าจะเป็นข้อจำกัดสูตรนี้ด้วยหรือเปล่า
ช่วย แก้ ข้อสงสัยในไฟล์ 3 ข้อ หน่อยค่ะ เพราะสงสัยว่าถ้าเราเขียนสูตรเพื่อข้อมูลที่ยังไม่ได้ป้อนค่าเข้ามาโดยลิ้งให้เข้าไฟล์ summary อัตโนมัติ
จะทำให้ค่าทั้งหมดออกมา ว่างเปล่า

ช่วยดูในไฟล์ให้หน่อยค่ะ

Test%2523rev (2).rar

[snasui]

ข้อ 1 คือถ้าเรา ต้องการ lookup รอ ข้อมูลที่จะเพิ่มมาใน rowต่อๆๆไป ถึง
row 500 ไว้ก่อนผลลัพธ์จะไม่แสดงค่ะ
ลองปรับเป็น I$500 ผลลัพธ์ไม่แสดงค่ะ

ถ้ายังไม่มีข้อมูลหรือข้อมูลที่มาเพิ่มเหมือนกับข้อมูลเดิมที่มีอยู่แล้ว สูตรก็ไม่ดึงข้อมูลมาให้ครับ

ข้อ 2 ใน Column I วันเดือนปี ไม่ดึงมาจาก ไฟล์ data ค่ะ

ปรับสูตรเป็นด้านล่างและจัด Format ให้เป็นวันที่ครับ
=IF(J4="","",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(Data!B$4:B$135,SMALL(IF(FREQUENCY(MATCH(Data!$I$4:$I$135,Data!$I$4:$I$135,0),ROW(Data!$I$4:$I$135)-ROW(Data!$I$4)+1),ROW(Data!$I$4:$I$135)-ROW(Data!$I$4)+1),ROWS(I$4:I4))))))

ข้อ 3 นับจำนวน ข้อมูลในคอลัมน์ D ชีท data เมื่อ C,E,F,G เหมือนกัน

ไม่เข้าใจครับ ช่วยอธิบายว่าจากตัวอย่างที่เขียนมานั้นนับอย่างไรจึงได้ค่าเท่านั้นจะได้เขียนสูตรเปรียบเทียบได้

[aerojen]

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

น่านับถือท่านมากค่ะสำหรับการให้ความรู้วิทยานแก่ผู้คนค่ะ

Test%2523rev (2).rar

[snasui]

ฟังก์ชั่น Char ที่ใช้ใน Lookup ตามสูตรนี้ไม่ได้ใช้ตามปกติ แต่เป็นการใช้แบบประยุกต์

กรณีที่เลือกเซลล์เผื่อไว้ให้ปรับ Q4 เป็นตามด้านล่างครับ

=LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX(Data!C$4:C$500,SMALL(IF(FREQUENCY(MATCH("~"&Data!$I$4:$I$500,Data!$I$4:$I$500&"",0),ROW(Data!$I$4:$I$500)-ROW(Data!$I$4)+1),ROW(Data!$I$4:$I$500)-ROW(Data!$I$4)+1),ROWS(Q$4:Q4)))))

Ctrll+Shift+Enter > Copy ไปด้านขวาและลงด้านล่าง

ส่วนสูตรสำหรับการนับ

เซลล์ O4 ปรับสูตรเป็นตามด้านล่างครับ

=IF(N4="","",SUM(IF(FREQUENCY(IF(Data!$C$4:$C$135&Data!$E$4:$E$135&Data!$F$4:$F$135&Data!$G$4:$G$135&Data!$H$4:$H$135=J4&K4&L4&M4&N4,MATCH(Data!$D$4:$D$135,Data!$D$4:$D$135,0)),ROW(Data!$C$4:$C$135)-ROW(Data!$C$4)+1),1)))

Ctrll+Shift+Enter > Copy ลงด้านล่าง

[aerojen]

ขอบคุณมากค่ะ
เลือกเซลล์เผื่อใส่ ~ ตัวหนอนแล้วไม่ได้ค่ะใน Q4 และ W4 เลือกค่าเผื่อไว้
แต่ผลลัพธ์เพี้ยนค่ะ ใกล้สำเร็จแล้วค่ะ

ขอบคุณอีกครั้งค่ะ :thup

Test%2523rev (2).rar

[snasui]

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

คอลัมน์ W ก็เช่นเดียวกัน ลองเทียบเคียงดูว่าการเขียนสูตรในคอลัมน์ O ตรง If ตัวแรกสุดอ้างอิงอย่างไรก็ต้องอ้างอิงลักษณะนั้นครับ