นับข้อความพร้อมแสดงเป็นรายงานโดยอัตโนมัติ

[ampertise]

ก่อนหน้านี้ได้มีการสอบถามเรื่อง สอบถามการใช้สูตร ifที่จับเงื่อนไขทั้งข้อความและตัวเลข เมื่อวันที่14 ต.ค.60 เวลา 16.35น. หลังจากนำไปใช้งานแล้วติดปัญหากับชุดข้อมูลที่มีไม่เอื้อต่อการออกรายงานแบบ pivot table ได้ จึงต้องพึ่ง codeในการแสดงรายงานแทน จึงขอรบกวนอาจารย์คนควนอีกครั้งหรือท่านผู้รู้ทุกท่าน โปรดชี้แนะ โดยมีไฟล์แนบมาพร้อมคำถามด้วยค่ะ
1.สอบถามชีต"Rpt1" ทำอย่างไรเมื่อชีต"Data"มีการเปลี่ยนแปลงข้อมูลแต่ชีต"Rpt1" ก็อัพเดทอัตโนมัติตามด้วย
คือ ในชีต"Rpt1" ที่ช่องB2ถึงR6(พื้นที่สีฟ้า) ต้องแสดงยอดนับของแต่ละเกรดให้ตรงกับเดือน-ปีบนหัวคอลัมน์ ซึ่งเดือน-ปีที่หัวคอลัมน์จะต้องเหมือนกับชีต"Data" เสมอ และทุกเดือนที่ชีต"Data"ตรงคอลัมน์Cจะมีการแทรกคอลัมน์เพิ่มเพื่อใส่ข้อมูลเดือนใหม่ต่อ เช่น Oct-17 เป็นต้น จึงต้องการให้ชีต"Rpt1"คอลัมน์B อัพเดทตามด้วยทุกครั้ง
ซึ่งตนได้ลองทำแล้วแต่ไม่อัพเดท คือ เริ่มจากชีต"Rpt1"ช่อง B1=Data!C3" จะได้ Sep-17 และทำการcopyสูตรของ B1ไปทางด้านขวาจนถึง R1 จะได้ May-16 เป็นเดือน-ปีสุดท้ายที่เหมือนกันในชีต"Data"
ส่วนพื้นที่สีฟ้าใส่สูตรในชีต"Rpt1"ช่องB2=COUNTIF(Data!C4:C11,"=A+")จะได้ 1 คือนับจำนวนของเกรด A+ ที่ชีต "Data"ใต้ช่องC4ลงไปเพื่อแสดงจำนวนของเดือน Sep-17 และ copyสูตรช่อง B2 ไปทางขวาจนถึงR6 ส่วนB3 ให้ทำการแก้ไขสูตรตรงคำว่า A+ เปลี่ยนเป็น A และB4 เปลี่ยนเป็น A- และB5 เปลี่ยนเป็น B และB6 เปลี่ยนเป็น C และคลุมB2:B6 แล้วcopyสูตร วางที่C2:R6

2.สอบถามชีต"Rpt2" ทำอย่างไรให้เดือน-ปีอัพเดทตามหัวคอลัมน์ที่เป็นเดือน-ปีในชีต"Data"อัตโนมัติ และเมื่อเลือกเดือนแล้วข้อมูลแสดงในตารางตามtemplateโดยอัตโนมัติ

ที่ชีต"Rpt2" ช่องB2ทำdropdownlist ไว้โดยเชื่อมข้อมูลกับชีต"Data"เพื่อไว้มาเลือกเดือน-ปีที่ต้องการมาแสดง
และที่ช่องB4 ใส่สูตร=COUNT(B6:B19)เพื่อนับว่ามีกี่คนที่ได้เกรดตรงกับช่องB5 ส่วนช่อง B6:F19 (พื้้นที่สีม่วง)ให้แสดงรหัสCSRที่ได้เกรดตรงกับช่องB5 ตั้งแต่B6ลงมาอัตโนมัติ แต่ยังคิดสูตรไม่ออก

[DhitiBank]

ลองแบบนี้ครับ

1. สร้าง dynamic range name เพื่อรองรับการเขียนสูตรที่มีการอัพเดทข้อมูลในชีท Data

1. 1.1 กด Ctrl+ปุ่ม F3 เพื่อเปิด Name manager

1. 1.2 กดปุ่ม New...

1. 1.3 ช่อง Name คีย์ _CSR และช่อง Refer to: คีย์ =OFFSET(Data!$B$3,1,,MAX(1,COUNTA(Data!$B:$B)-1)) > OK

1. 1.4 ทำซ้ำข้อ 1.2 และ 1.3 อีก 2 ครั้ง เพื่อเพิ่มอีก 2 ชื่อดังนี้

Name: _Grading Refer to: =OFFSET(Data!$B$3,1,1,MAX(1,COUNTA(Data!$B:$B)-1),MAX(1,MATCH(9.99999999E+307,Data!$3:$3)-2))

Name: _Period Refer to: =OFFSET(Data!$B$3,,1,1,MAX(1,MATCH(9.999999999E+307,Data!$3:$3)-2))
ทำเสร็จขั้นตอนนี้ก็จะมี dynamic range name จำนวน 3 ชื่อ ดังรูปครับ

2017-10-23 00_27_59-Name Manager.png

2. ชีท Rpt1

1. 2.1 B1 คีย์

=IF(OFFSET(Data!$B$3,,COLUMNS($B1:B1))=0,"",OFFSET(Data!$B$3,,COLUMNS($B1:B1)))
Enter >> คัดลอกไปทางขวา คัดลอกเผื่อเอาไว้สัก 20-30 คอลัมน์ก็ได้

1. 2.2 B2 คีย์

=IF(B$1="",0,COUNTIF(INDEX(_Grading,,MATCH(B$1,_Period,0)),$A2))
Enter >> คัดลอกไปทางขวาถึงคอลัมน์ที่คัดลอกสูตรในข้อ 2.1 >> แล้วคัดลอกสูตรลงล่าง

3. ชีท Rpt2

1. 3.1 คลิกเซลล์ B2 >> เมนู Data >> Data validation >> ที่แท็ป setting >> Allow: เลือก List >> Source: คีย์ =_Period >> OK

1. 3.2 B6 คีย์

=IFERROR(INDEX(_CSR,SMALL(IF(INDEX(_Grading,,MATCH($B$2,_Period,0))=B$5,ROW(INDEX(_Grading,,1))-ROW(INDEX(_Grading,1,1))+1),ROWS(B$6:B6))),"")
กด Ctrl+Shift ค้างไว้แล้ว Enter >> คัดลอกไปทางขวาและลงล่างครับ ถ้ากดถูกจะเห็นเครื่องหมาย { } ขึ้นมาคร่อมสูตรโดยอัตโนมัติ ไม่ได้คีย์เข้าไปเองนะครับ

2017-10-23 00_41_13-Microsoft Excel - Grading.xlsx.png

[ampertise]

ขอขอบคุณ คุณDhitiBank มากค่ะ
จากได้ลองทำตามสูตรที่แนะนำมาแล้ว พบว่าในชีต Rpt2 ของตนเองไม่แสดงรหัส CSR เหมือนที่คุณDhitiBank ได้capture screenมาให้ดู จึงขอรบกวนคุณDhitiBankหรือท่านผู้รู้ ช่วยอธิบายสูตรนี้ให้ได้ไหมค่ะ เพื่อหาสาเหตุด้วยว่าตนเองนั้นทำผิดพลาดตรงไหนจึงไม่ได้ผลลัพธ์ตามที่ควรได้ {=IFERROR(INDEX(_CSR,SMALL(IF(INDEX(_Grading,,MATCH($B$2,_Period,0))=B$5,ROW(INDEX(_Grading,,1))-ROW(INDEX(_Grading,1,1))+1),ROWS(B$6:B6))),"")}

[ampertise]

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

[menem]

ไม่แน่ใจว่าตรงกับที่ต้องการไหมนะครับ

[ampertise]

ขอบคุณมากค่ะ คุณ menem สำหรับข้อมูลที่แบ่งปันมาให้ค่ะ เกือบตรงกับที่ต้องการแต่เมื่อมีการเพิ่มช่องเดือนในชีต Data ชีต Rpt1 ไม่อัพเดทเดือนตาม และชีต Rpt2ใน dropdownlist เดือนไม่อัพเดทให้ค่ะ แต่สามารถนับจำนวนชื่อพนักงานที่ได้แต่ละเกรดในชีต Rpt2 ได้ ซึ่งกำลังติดปัญหานี้อยู่พอดี ไม่แน่ใจว่าเพราะใน Cell มีสูตรarreyอยู่หรือเปล่าเลยทำให้นับไม่ได้ ค่ะ

[menem]

ขอดูตัวอย่างตอนเพิ่มเดือนได้ไหมครับ?

[menem]

ถ้าเข้าใจไม่ผิด สิ่งที่ต้องการคือ เมื่อเพิ่มเดือนใน DATA
จะเพิ่มเดือนใน RPT1 อัตโนมัติ ซึ่งหากทำด้วยสูตรจะทำไม่ได้ครับ ต้องเป็น VBA เท่านั้น
กรณีจะทำด้วยสูตร เราจะทำได้แค่ "เตรียมพื้นที่" เผื่อไว้ให้ว่าจะมีได้กี่เดือนย้อนหลัง
(เพราะถ้าเอาทั้งหมดจริง ๆ ก็จะต้องวางสูตรยาวมาก ซึ่งจะมีผลต่อขนาดของไฟล์โดยรวม)