ดึงวันที่ของชีทรายคนมาใส่column

[March201711]

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

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

[Supachok]

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

[Supachok]

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

[March201711]

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

[Supachok]

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

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

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

[March201711]

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

[snasui]

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

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 ลงด้านล่างที่เกี่ยวข้อง

[menem]

ลองดูนะครับ

[March201711]

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

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

[snasui]

อาจารย์คะ สงสัยสูตร GET.WORKBOOK(1) คืออะไรคะ

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

คือว่า file ที่ทำงานจริงๆ มีหลายsheet มาก มีมากกว่า 20 sheet

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

ถ้าเอา 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 ครับ

[March201711]

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

[snasui]

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

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

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

[March201711]

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

[snasui]

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

[March201711]

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

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

[snasui]

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

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

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

[March201711]

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

[snasui]

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

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

[March201711]

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