update ข้อมูลยังไง

[parakorn]

สวัสดีครับทุกท่าน คือผมเป็นสมาชิกไหม่ของบอร์ดนี้ครับ เผอิญหลงเข้ามา พอดีผมต้องทำงานเกี่ยวกับ excel ที่ซับซ้อนมากๆ
ทุกวันเลยครับ แล้วที่ทำงานผมแต่ละคนไม่เก่ง excel เลยครับ คีย์มือกันทั้งบริษัทเลย(มีเก่งส่วนน้อยครับ)
ผมเลยอยากให้ทุกท่าน ช่วยแนะนำผมด้วยครับ ผมชอบเรียนรู้ครับก่อนมาทำงานผมไม่เป็น excel เลยครับ
แต่ตอนนี้คล่องมากๆครับ vlookup ก็ใช้คล่อง พิมพ์ก็ไวแล้ว ตอนผมมีปัญหาใหญ่มากๆ แต่ผมก็มีที่ปรึกษา
ที่เก่ง excel คอยชี้แนะครับ แต่งานที่นี่ค่อนข้างเยอะมากครับ ผมชอบสร้างปัญหาในหัวซะด้วยจะรบกวนเขาตลอดก็กระไรอยู่
ผมขอเกริ่นก่อนนะครับ ที่ทำงานผมเป็นห้างครับ(เหมือนBigC Lotus)ครับ แต่ราคาห้างผมมี 3ราคาต่อสินค้า1ชิ้น(บางอย่างก็2ราคา บางอย่างก็มากกว่า3) โดยจะแบ่งเป็น step เช่น ร่มuv24นิ้ว ซื้อ1ชิ้นราคา50บาท 3ชิ้นขึ้นไปเหลือ 45บาท 6ชิ้นขึ้นไปเหลือ 40บาท โดยสินค้าแต่ละชนิด step ก็ไม่เท่ากันด้วย บางอย่างซื้อ1000ชิ้นถึงจะลดราคาก็มี สินค้าส่วนใหญ่มาจากจีน
ทำให้สินค้าเปลี่ยนตลอดเวลา รายการสินค้าเปลี่ยนทุกอาทิตย์ ราคาปรับขึ้นลงตลอด และผมต้องคอยดูสมาชิก 2000กว่าราย
ที่คอยเข้ามาซื้อของจำนวนมากไปขาย(พ่อค้าแม่ค้า)
ตอนนี้หัวหน้าของผมให้ผมดึง TOP50 ที่ขายดีของทุกแผนก พร้อมราคาเพื่อ update ลง Ipad เพื่อไปออกแนะนำลูกค้านอกสถานที่ รายการสินค้า ผมสามารถดึงจากระบบได้ครับแต่ราคาไม่สามารถดึงได้ครับ มีปัญหาตรง itemmaster มีการปรับเปลี่ยน ทุกอาทิตย์ itemmaster ของห้างผมจะมีข้อมูลสินค้าทั้งหมดในห้างครับขอยกตัวอย่างคร่าวๆนะครับ
8008240205157 หัวก๊อกน้ำแพคเดี่ยว 2B-13065-01 P1I250 1.00 5.00 20.00
8008240205157 หัวก๊อกน้ำแพคเดี่ยว 2B-13065-01 P1I250 6.00 11.00 17.00
8008240205157 หัวก๊อกน้ำแพคเดี่ยว 2B-13065-01 P1I250 12.00 999.00 15.00
มันจะซ้ำกันตามราคา stepที่มีครับ(มี5stepก็ซ้ำกัน5ครั้ง)
วันก่อนผมดึง TOP50 ผมต้องเรียงลำดับจำนวนstepที่ซื้อ แล้วแยกไว้แต่ละ sheetแล้วเอามา vlookup
เพื่อดึงราคา แต่ละแผนก ซึงมาทั้งสิ้น 20กว่าsheet(stepหลากหลายมาก)
แต่วันนี้ itemmaster มันได้ปรับเปลี่ยนแล้วครับ ผมต้องทำยังไงให้ทั้ง 20กว่าsheetผมupdateตามครับ
แล้วหากมีสินค้าที่มี step ที่ไม่เคยมีมาก่อนผมต้องเพิ่ม sheet เองใช่ไหมครับ แล้วผมต้องทำยังไงครับถึงจะรู้
ว่ามีstepไหม่เกิดขึ้น รบกวนทุกท่านช่วยผมด้วยนะครับ

ผมแนบไฟล์ตัวอย่างนะครับ ลองเข้าไปดูกันนะครับ

ถ้าราคาถูกใจก็มาแวะเวียนห้างผมได้นะครับ

[joo]

อ่านแล้วยังไม่ค่อยเข้าใจดูเหมือนจะเป็นประโยคบอกเล่าเสียมากกว่า ช่วยยกตัวอย่างให้ดูหน่อยครับว่าเงื่อนไขคืออะไร คำต้องที่ต้องการคืออะไร ให้แสดงที่เซลล์ไหนของซีทอะไรครับ
หากต้องการคำตอบเหมือนตัวอย่างในซีท top5 ก็ไม่จำเป็นต้องแยกซีทก็ได้ให้ทำการดึงข้อมูลมาแสดงโดยตรงได้เลย เช่น ที่ซีท top5 เซลล์ I4:M4ให้เปลี่ยนข้อความในเซลล์เป็นตัวเลข 1,3,6,12,36 แทนแต่ละ step ตามลำดับจากนั้นที่เซลล์ I5 คีย์ =INDEX(itemmaster!$N$2:$N$11,MATCH(1,IF(itemmaster!$B$2:$B$11=$A5,IF(itemmaster!$L$2:$L$11=N$4,1,)),0)) กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วคัดลอกไปทางขวามือและลงมาด้านล่าง

[parakorn]

มันขึ้น #N/A ครับแก้ยังไงครับ
แล้วถ้า itemmaster update ผม coppy มาวาง
หน้า TOP5 ก็จะแก้ไขตามด้วยใช่ไหมครับ
แล้วหากมี step นอกเหนือจาก 1 3 6 12 36
ผมต้องทำอย่างไรครับ

[joo]

ถ้าไม่ต้องการให้แสดงค่า #N/A ก็ดักค่า Error ไว้สูตรก็จะเป็น =IFERROR(INDEX(itemmaster!$N$2:$N$11,MATCH(1,IF(itemmaster!$B$2:$B$11=$A5,IF(itemmaster!$L$2:$L$11=N$4,1,)),0)),"") กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วลัดลอกไปทางขวาและลงล่าง
ถ้ามี step เพิ่มมาอีกท่านก็ใส่ตัวเลขเพิ่มเข้าไปต่อจาก step เดิมได้เลยแล้วก็ Copy สูตรไปว่าง ทดลองเปลี่ยนแปลงข้อมูลดูครับ

[parakorn]

เหมือนสูตรมันจะไม่ success ครับลองแก้ดูแล้วคราวนี้
ไม่มีอะไรขึ้นเลยครับ

[joo]

ตอนกดแป้นรับสูตรท่านทำถูกต้องหรือไม่ ท่านต้องกดแป้น Ctrl+Shift ค้างไว้ก่อน แล้วกดแป้น Enter ตามมา ปล่อยแป้นแล้วสังเกตดูว่าสูตรมีเครื่องหมายปีกกาครอบหรือไม่ หากมีแล้วให้ Copy ไปยังเซลล์อื่น ๆ
ลองแนบไฟล์ที่ทดลองแล้วติดปัญหามาดูกันหน่อยครับ

[parakorn]

ฝากดูให้ทีนะครับ ผมพลาดตรงไหนไม่รู้

[joo]

ต้องขออภัยที่อ้างอิงเซลล์ผิดไปหน่อยในตอนแรก จากไฟล์ที่แนบมาผิดตรงจุดนี้ครับ
=INDEX(itemmaster!$N$2:$N$11,MATCH(1,IF(itemmaster!$B$2:$B$11=$A5,IF(itemmaster!$L$2:$L$11=N$4,1,)),0))
ให้ปรับแก้เป็น
=INDEX(itemmaster!$N$2:$N$11,MATCH(1,IF(itemmaster!$B$2:$B$11=$A5,IF(itemmaster!$L$2:$L$11=I$4,1,)),0))
กดแป้น Ctrl+Shift+Enter พร้อมกัน แล้วลัดลอกไปทางขวาและลงล่าง

[parakorn]

ขอบคุณมากครับ

[parakorn]

แล้วหากผมนำข้อมูลสินค้าทั้งหมดมาวางต้องปรับยังไงดีครับ

[snasui]

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

ไม่ทราบว่าได้ลองปรับตรงไหนไปบ้างแล้วช่วยโพสต์สูตรที่ลองปรับแล้วยังติดปัญหามาด้วยครับ

[parakorn]

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

http://www.upload-thai.com/download.php ... 95e1b8bd86

4.26mb ครับ
ชื่อไฟล์ ตัวอย่าง.xlsx

[snasui]

ไฟล์ตัวอย่างไม่ควรมีขนาดใหญ่ ไม่จำเป็นต้องนำรายการทั้งหมดมาใช้เป็นตัวอย่างครับ

ในเบื้องต้นลองแจ้งมาว่าข้อมูลรายการสุดท้ายอยู่ที่บรรทัดใด และปรับสูตรเป็นแบบใดไปบ้างแล้วครับ

[parakorn]

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

=INDEX(itemmaster!$N$2:$N$11,MATCH(1,IF(itemmaster!$B$2:$B$11=$A5,IF(itemmaster!$L$2:$L$11=I$4,1,)),0))
มี 51340 บรรทัด ผมก็ต้องเปลี่ยนเป็น

=INDEX(itemmaster!$N$2:$N$51340,MATCH(1,IF(itemmaster!$B$2:$B$51340=$A5,IF(itemmaster!$L$2:$L$51340=I$4,1,)),0))
ใช่ไหมครับ ว้าว

[joo]

เยี่ยมเข้าใจถูกต้องแล้วครับ หากไม่ต้องการปรับเปลี่ยนช่วงข้อมูลบ่อยๆก็ให้ใช้ Range Name เข้ามาช่วยได้ครับ ซึ่งจะเป็นการตั้งชื่อช่วงเซลล์แบบ Dynamic ด้วยฟังก์ชัน Offset เช่น ช่วงเซลล์ $N$2:$N$51340 ซึ่งเป็น Range Name ของ Stepราคา เราสามารถตั้ง Range Name ชื่อ Stepราคา โดยเข้าไปที่แถบ Formulas > Define Name >ตรงช่อง Names พิมพ์ชื่อ Stepราคา > ตรงช่อง Refers to พิมพ์ =OFFSET(itemmaster!$N$1,1,0,COUNTA(itemmaster!$N:$N)-1,1) ส่วน Range Name อื่นๆก็ทำเหมือนกัน
เมื่อนำมาแทนค่าในสูตรก็จะได้เป็น =INDEX(Stepราคา,MATCH(1,IF(itemmaster!$B$2:$B$51340=$A5,IF(itemmaster!$L$2:$L$51340=I$4,1,)),0))
ลองประยุกต์ปรับใช้ดูครับ

[parakorn]

แจ๋วเลยครับ ผมจะลองทำดูนะครับ
ขอบคุณมากครับ

[parakorn]

ได้ละครับ =IFERROR(INDEX(Stepราคา,MATCH(1,IF(Barcode=$A5,IF(Stepตั้งแต่=G$4,1,)),0)),"-")
หากเขียนเป็นสูตรแบบไม่อ้างอิงชื่อ เราสามารถทำได้ไหมครับ
อีกข้อนึงครับ
พอจะมีวิธีตรวจสอบ step ไหม่ๆที่เพิ่มเข้ามาไหมครับ เวลาปรับเปลี่ยนราคา
ทางdataไม่ได้แจ้งครับ

[snasui]

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

การทำดังกล่าวเราสามารถใช้ PivotTable เข้ามาช่วยได้ครับ โดย

1. คลิกลงในข้อมูล itemmaster
2. เข้าเมนู Insert > PivotTable
3. ตรง PivotTable Fields
   1. ลาก Bar Code, หน่วย, Stepตั้งแต่ มาที่ ROWS
   2. ลาก Step ราคามาที่ VALUES
4. เข้าเมนู DESIGN > เลือก Report Layout เป็น Show in Tabular Form
5. กรณีไม่ต้องการแสดงบรรทัดที่มี Total ให้คลิกขวาลงบนเซลล์ที่ต่อท้ายด้วยคำว่า Total > คลิกไปที่เมนู Subtotal...(...คือชื่อ Field)

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

นอกจากนี้ PivotTable เองก็ต้องครอบคลุมถึงการเพิ่มลดข้อมูลด้วย ไม่เช่นนั้นแล้วข้อมูลก็จะมาไม่ครบ การที่จะทำให้ PivotTable ครอบคลุมถึงข้อมูลทั้งหมดไม่ว่าจะเพิ่มลดข้อมูลไปอย่างไรมี 2 วิธีคือ

1. ให้ชื่อกับตาราง Database แล้วนำมาใช้ใน PivotTable
2. ใช้ Table เข้ามาช่วย โดยคลิกที่ตาราง Database แล้วเข้าเมนู Insert > Table

[parakorn]

ผมลองนำมาปรับใช้กับ การดึงยอดซื้อของลูกค้า ที่ทางแผนก telesales โทรหา
แต่ไม่ success ครับ มันต้องแก้ตรงไหนครับ

=INDEX(รายงานสรุปยอดขายรายวันตามสมาชิก!$G$6:$G$13266,MATCH(1,IF(รายงานสรุปยอดขายรายวันตามสมาชิก!$A$6:$A$13266=$F5,IF(รายงานสรุปยอดขายรายวันตามสมาชิก!$E$6:$E$13266=H$6,1,)),0))

ตามไฟล์ที่แนบเลยครับ

[snasui]

จาก

=INDEX(รายงานสรุปยอดขายรายวันตามสมาชิก!$G$6:$G$13266,MATCH(1,IF(รายงานสรุปยอดขายรายวันตามสมาชิก!$A$6:$A$13266=$F5,IF(รายงานสรุปยอดขายรายวันตามสมาชิก!$E$6:$E$13266=H$6,1,)),0))

ไม่ทราบว่า =H$6 คืออะไรครับ