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

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

ฟอรั่มถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
  1. ไม่อนุญาตให้ใช้ภาษาแชทในการถาม-ตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
  2. ห้ามถามโดยระบุชื่อผู้ตอบ ต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
  3. อธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
  4. ควรแนบตัวอย่างไฟล์มาที่ฟอรั่มนี้เพื่อเพิ่มความสะดวกในการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่น นอกจากนี้ไม่ควรแนบไฟลที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
  5. สำหรับคำถามเกี่ยวกับ VBA ให้ลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน ควรโพสต์ Code ให้แสดงเป็น Code เพื่อสะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
  6. แจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
ampertise
Member
Member
Posts: 21
Joined: Sun Jun 17, 2012 1:41 pm

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

#1

Postby ampertise » Sun Oct 22, 2017 10:55 pm

ก่อนหน้านี้ได้มีการสอบถามเรื่อง สอบถามการใช้สูตร 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ลงมาอัตโนมัติ แต่ยังคิดสูตรไม่ออก :?:

:)
You do not have the required permissions to view the files attached to this post.

User avatar
DhitiBank
Gold
Gold
Posts: 1604
Joined: Mon Oct 15, 2012 12:07 am

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

#2

Postby DhitiBank » Mon Oct 23, 2017 12:26 am

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

1. สร้าง dynamic range name เพื่อรองรับการเขียนสูตรที่มีการอัพเดทข้อมูลในชีท Data
    1.1 กด Ctrl+ปุ่ม F3 เพื่อเปิด Name manager
    1.2 กดปุ่ม New...
    1.3 ช่อง Name คีย์ _CSR และช่อง Refer to: คีย์ =OFFSET(Data!$B$3,1,,MAX(1,COUNTA(Data!$B:$B)-1)) > OK
    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
    2.1 B1 คีย์
=IF(OFFSET(Data!$B$3,,COLUMNS($B1:B1))=0,"",OFFSET(Data!$B$3,,COLUMNS($B1:B1)))
Enter >> คัดลอกไปทางขวา คัดลอกเผื่อเอาไว้สัก 20-30 คอลัมน์ก็ได้

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

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

    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
You do not have the required permissions to view the files attached to this post.

ampertise
Member
Member
Posts: 21
Joined: Sun Jun 17, 2012 1:41 pm

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

#3

Postby ampertise » Mon Oct 23, 2017 10:58 am

:D ขอขอบคุณ คุณDhitiBank มากค่ะ :thup:
จากได้ลองทำตามสูตรที่แนะนำมาแล้ว พบว่าในชีต 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
Member
Member
Posts: 21
Joined: Sun Jun 17, 2012 1:41 pm

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

#4

Postby ampertise » Mon Oct 23, 2017 12:34 pm

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

menem
Bronze
Bronze
Posts: 429
Joined: Mon Jan 26, 2015 11:02 am

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

#5

Postby menem » Mon Oct 23, 2017 1:09 pm

ไม่แน่ใจว่าตรงกับที่ต้องการไหมนะครับ
You do not have the required permissions to view the files attached to this post.

ampertise
Member
Member
Posts: 21
Joined: Sun Jun 17, 2012 1:41 pm

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

#6

Postby ampertise » Tue Oct 31, 2017 8:15 pm

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

menem
Bronze
Bronze
Posts: 429
Joined: Mon Jan 26, 2015 11:02 am

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

#7

Postby menem » Tue Oct 31, 2017 8:59 pm

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

menem
Bronze
Bronze
Posts: 429
Joined: Mon Jan 26, 2015 11:02 am

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

#8

Postby menem » Wed Nov 01, 2017 9:15 am

ถ้าเข้าใจไม่ผิด สิ่งที่ต้องการคือ เมื่อเพิ่มเดือนใน DATA
จะเพิ่มเดือนใน RPT1 อัตโนมัติ ซึ่งหากทำด้วยสูตรจะทำไม่ได้ครับ ต้องเป็น VBA เท่านั้น
กรณีจะทำด้วยสูตร เราจะทำได้แค่ "เตรียมพื้นที่" เผื่อไว้ให้ว่าจะมีได้กี่เดือนย้อนหลัง
(เพราะถ้าเอาทั้งหมดจริง ๆ ก็จะต้องวางสูตรยาวมาก ซึ่งจะมีผลต่อขนาดของไฟล์โดยรวม)
You do not have the required permissions to view the files attached to this post.


Return to “Excel”

Who is online

Users browsing this forum: sksk and 37 guests