Snasui Excel Forum

ถ้าจะให้ดึงวันที่ของ sheet หลายคนมาใส่ที่ Column C (high light สีเหลือง) ดึงสูตรอย่างไรบ้างคะ

คือต้อง key in วันที่เองของชื่อรายคน ซึ่งมีเป็นร้อยๆ คนอยากใช้สูตรดึงมาเลยทำได้ไหมคะ

Add column A
=COUNT(IF(FREQUENCY(MATCH(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6")),),ROW($B$1:$B$3))>0,ROW($B$1:$B$3)))

copy ไปไว้บรรทัดแรกของชื่อคน จะบอกจำนวนว่าจะต้อง copy ชื่อคนกีี่ row เพื่อให้ cell อ้างอิงวันที่ขึ้นมาในวันที่ไม่ซ้ำ

C31

=IFERROR(INDEX(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),SMALL(IF(FREQUENCY(MATCH(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6")),),ROW($B$1:$B$3))>0,ROW($B$1:$B$3)),COUNTIF($C$31:C31,C31))),"")
Array fomula {}

Copy down

insert column A
=COUNT(IF(FREQUENCY(MATCH(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6")),),ROW($B$1:$B$3))>0,ROW($B$1:$B$3)))

copy ไปไว้บรรทัดแรกของชื่อคน จะบอกจำนวนว่าจะต้อง copy ชื่อคนกีี่ row เพื่อให้ cell อ้างอิงวันที่ขึ้นมาในวันที่ไม่ซ้ำ

C31

=IFERROR(INDEX(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),SMALL(IF(FREQUENCY(MATCH(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6"),(INDIRECT("'"&LEFT($C31,11)&"'!$C$4:$C$6")),),ROW($B$1:$B$3))>0,ROW($B$1:$B$3)),COUNTIF($C$31:C31,C31))),"")
Array fomula {}

Copy down

ถ้าไม่ใช้ array formula และไม่ต้องเพิ่ม column จะได้ไหมคะ เพราะข้อมูลรายคนเยอะอยู่แล้ว จะทำให้เครื่องคำนวณอืดๆน่ะค่ะ

March201711 wrote: Fri Apr 26, 2019 4:48 pm ถ้าไม่ใช้ array formula และไม่ต้องเพิ่ม column จะได้ไหมคะ เพราะข้อมูลรายคนเยอะอยู่แล้ว จะทำให้เครื่องคำนวณอืดๆน่ะค่ะ

เพิ่ม column เพียงแค่ให้รู้จำนวนวันที่เท่านั้นที่ซ้ำเท่านั้น ไม่จำเป็นก็ได้ครับ.
แต่ต้องเพิ่มชื่อของคนซ้ำเท่ากับวันที่ ที่มีรายการซ้ำ.

สูตรอื่นๆเดียวมีผู้รุ้ท่านอื่นๆมาช่วยคิด

ทำไมได้แค่บรรทัดเดียวคะ

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

1. สร้าง Range Name ชื่อ SheetList โดย เข้าเมนู Formula > Name Manager > New > ช่อง Name คีย์คำว่า SheetList > ช่อง Refers to: คีย์สูตร
   =INDEX(RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))),0)
2. ที่ชีต AR(AP)_Client_BTF
   1. ที่ H28:I28 คีย์ Sheet Name,Trade Date เพื่อเป็นหัวคอลัมน์
   2. ที่ H31 คีย์เพื่อแสดงชีตที่จะนำมาใช้
      =IFERROR(INDEX(SheetList,INT((ROWS(H$31:H31)-1)/5)+1),"")
      Enter > Copy ลงด้านล่าง
   3. ที่ I31 คีย์เพื่อแสดงวันที่ที่เกี่ยวข้อง
      =IFERROR(OFFSET(INDIRECT("'"&H31&"'!c4"),COUNTIF(H$31:H31,H31)-1,0),"")
      Enter > Copy ลงด้านล่าง
   4. ที่ A31 คีย์เพื่อแสดง ID ของชีตนั้น ๆ
      =IF(COUNTIF(H$31:H31,H31)=1,INDIRECT("'"&H31&"'!a2"),"")
      Enter > Copy ลงด้านล่าง
   5. ที่ B31 คีย์เพื่อแสดง Name ของชีตนั้น ๆ
      =IF(COUNTIF(H$31:H31,H31)=1,INDIRECT("'"&H31&"'!b2"),"")
      Enter > Copy ลงด้านล่าง
   6. ที่ C31 คีย์เพื่อแสดงวันที่เฉพาะที่ไม่ซ้ำของชีตนั้น ๆ
      =IFERROR(INDEX($I$31:$I$40,AGGREGATE(15,6,(ROW($I$31:$I$40)-ROW($I$31)+1)/((($H$31:$H$40=H31)*($I$31:$I$40>0)*FREQUENCY(MATCH($I$31:$I$40,$I$31:$I$40,0),ROW($I$31:$I$40)-ROW($I$31)+1))>0),COUNTIF(H$31:H31,H31))),"")
      Enter > Copy ลงด้านล่าง
   7. ที่ D31 คีย์เพื่อแสดงยอด Gross BU
      =IF(N(C31),SUMIFS(INDIRECT("'"&$H31&"'!$L$4:$L$7"),INDIRECT("'"&$H31&"'!$E$4:$E$7"),D$29,INDIRECT("'"&$H31&"'!$C$4:$C$7"),$C31),0)
      Enter > Copy ลงด้านล่างที่เกี่ยวข้อง
   8. ที่ E31 คีย์เพื่อแสดงยอด Gross SE
      =IF(N(C31),-SUMIFS(INDIRECT("'"&$H31&"'!$L$4:$L$7"),INDIRECT("'"&$H31&"'!$E$4:$E$7"),E$29,INDIRECT("'"&$H31&"'!$C$4:$C$7"),$C31),0)
      Enter > Copy ลงด้านล่างที่เกี่ยวข้อง

ลองดูนะครับ

ได้ตามที่อาจารย์บอกเลยค่ะ แต่มีปัญหาค่ะ
อาจารย์คะ สงสัยสูตร GET.WORKBOOK(1) คืออะไรคะ

คือว่า file ที่ทำงานจริงๆ มีหลายsheet มาก มีมากกว่า 20 sheet
ถ้าเอา file : Book10 sheet 3 sheet แถบสีน้ำเงินมา copy หรือ move ไปใส่ที่ file อื่น เช่น file : Main ทำไม column H จะเปลี่ยนเป็น ชื่อ sheet1 sheet2 เลยค่ะ ทำไมถึงเปลี่ยนไป ไม่เหมือน file : Book10 คะ

March201711 wrote: Sun Apr 28, 2019 8:23 am อาจารย์คะ สงสัยสูตร GET.WORKBOOK(1) คืออะไรคะ

เป็น Excel 4.0 Macro Functions สำหรับกรณีนี้นำมาใช้เพื่อแสดงชื่อชีต เนื่องจากฟังก์ชั่นในปัจจุบันไม่สามารถแสดงชื่อชีตได้ ศึกษาเพิ่มเติมที่นี่่ครับ wordpress/list-all-sheets/

March201711 wrote: Sun Apr 28, 2019 8:23 am คือว่า file ที่ทำงานจริงๆ มีหลายsheet มาก มีมากกว่า 20 sheet

จะมีกี่ชีตก็ไม่ใช่ข้อจำกัดของการแสดงชื่อชีตด้วยวิธีการนี้ครับ

March201711 wrote: Sun Apr 28, 2019 8:23 am ถ้าเอา file : Book10 sheet 3 sheet แถบสีน้ำเงินมา copy หรือ move ไปใส่ที่ file อื่น เช่น file : Main ทำไม column H จะเปลี่ยนเป็น ชื่อ sheet1 sheet2 เลยค่ะ ทำไมถึงเปลี่ยนไป ไม่เหมือน file : Book10 คะ

เพราะไม่ได้เขียน Excel 4.0 Macro Functions ไว้ที่ไฟล์ปลายทาง เขียนไว้ที่ใดก็จะมีผลกับไฟล์นั้น ๆ ครับ

ไฟล์ที่จะใช้ Excel 4.0 Macro Functions จะต้อง Save เป็น .xlsm เนื่องจากถือว่าเป็นไฟล์ที่มี Macro ครับ

save เป็น marco แล้วก็ไม่ได้คะ ยังเหมือนเดิมค่ะ

เนื่องจากมีการแทรกชีตไว้ด้านหน้าแสดงว่าต้องการนำชีตนั้นมาเป็นรายงานครับ

วิธีการแก้ไข ทำตามข้อใดข้อหนึ่งด้านล่าง

1. ลบหรือย้ายชีตที่ไม่เกี่ยวข้องไปไว้ด้านหลังของชีตที่แสดงรายงานแล้วทำการ Refresh สูตรใหม่ด้วยการ Replace (กดแป้น Ctrl+H เพื่อเปิดหน้าต่าง Replace > ช่องบนและช่องล่างคีย์เครื่องหมาย = จากนั้นคลิกปุ่ม Replace All)
2. ยกเลิกการซ่อนบรรทัดล่าง ๆ แล้ว Copy สูตรที่เขียนไว้แล้วทั้งหมดลงไปด้านล่าง

Refresh all แล้วไม่เห็นได้เลยค่ะ

อ่านวิธีการ Refresh ตามที่ผมบอกไปอย่างละเอียดทุกอักขระแล้วทำตามนั้น หรือคลิกที่ H31 > กดแป้น F2 แล้ว Enter ครับ

ค่ะ อาจารย์ ถ้าย้ายไป file งานจริง ที่มี sheet ก่อนหน้านั้นประมาณ 70 กว่าชีทก่อน เพื่อให้ข้อมูลจำว่าอยู่sheet อันดับแรกเลย อย่างนั้นหรือเปล่าคะอาจารย์ แล้วก็ย้ายกลับมาที่ sheet อันดับที่ 80 ใช่ไหมคะ

แล้วจะทำให้เครื่องคำนวณสูตรช้า หน่วงๆหรือเปล่าคะ เพราะต้องใช้ file นี้ทำงานทุกวันกลัวจะทำให้ file งานพังค่ะ

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

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

เครื่องคำนวณช้าหรือไม่ทดสอบเองได้เลยครับ

ค่ะ จะลองปรับไปใช้ดูค่ะ แต่สงสัย คำว่า =Get.Workbook ค่ะ ดูใน marcoแล้วไม่มี ใน vba ค่ะ แล้ว =Get.Workbook(1) ; =Get.Workbook(2) ; =Get.Workbook(3)
=Get.Workbook(4) ใช้งานแตกต่างกัน อย่างไรคะ มีทั้งหมดกี่ ( ) ค่ะ

มีอยู่จำนวนมากและยังไม่จำเป็นต้องสนใจประเด็นที่นอกเหนือจากที่ผมตอบไปเพราะยังไม่ถึงเวลาที่จะใช้งาน หากจะสนใจควรเป็นการเขียน VBA เข้ามาจัดการเนื่องจาก Excel 4.0 Macro Functions เป็นของเก่าที่หาแหล่งศึกษาลำบากครับ

ฟังก์ชั่นพวกนี้เขียนอยู่ใน Range Name แต่มีสภาพเหมือนการใช้ Macro ครับ

อ๋อ เข้าใจแล้วค่ะ ขอบคุณอาจารย์และทุกท่านมากค่ะ