Snasui Excel Forum

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=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)))))

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

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

ข้อ 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 เหมือนกัน

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

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

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

ฟังก์ชั่น 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 ลงด้านล่าง

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

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

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

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