Snasui Excel Forum

คลังคำตอบแห่งความรู้จากคนไทย เพื่อโลกที่ต้องการเข้าใจในสิ่งเล็ก ๆ อย่างลึกซึ้ง
http://www.snasui.com/

ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

http://www.snasui.com/viewtopic.php?t=16373

Page 1 of 1

ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Mon Nov 11, 2019 2:22 pm
by knan
ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ค่ะ
คือเป็นแบบนี้ค่ะ
sheet ที่ 5 มีข้อมูลรวมกัน
ทุกภาคตั้งแต่ช่อง F3 ถึง F351968 แต่ เราจะแยกเฉพาะ ภาคเหนือ ก่อนค่ะ
ต้องใช้สูตรยังไงคะ?
ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Mon Nov 11, 2019 4:03 pm
by Supachok
แนบไฟล์มากจะง่ายกว่า

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sat Nov 16, 2019 10:30 pm
by knan
Supachok wrote: Mon Nov 11, 2019 4:03 pm แนบไฟล์มากจะง่ายกว่า
ขอโทษด้วยนะคะที่หายไป พอดีป่วยแอดมิทที่ โรงพยาบาลค่ะ
แนบไฟลล์มาแล้วนะคะ รบกวนด้วยนะคะ ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sat Nov 16, 2019 11:13 pm
by snasui
:D การเขียนสูตรดึงข้อมูลมาเรียงเฉพาะที่เข้าเงื่อนไขจากข้อมูลปริมาณหลักแสนบรรทัดคิดว่าไม่เหมาะครับ

ทางเลือกหนึ่งคือใช้การกรองข้อมูลไปเฉพาะที่ต้องการ ส่วนที่เหลือเช่นการนับ การรวม ค่อยใช้สูตรหาคำตอบครับ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 12:21 pm
by knan
snasui wrote: Sat Nov 16, 2019 11:13 pm :D การเขียนสูตรดึงข้อมูลมาเรียงเฉพาะที่เข้าเงื่อนไขจากข้อมูลปริมาณหลักแสนบรรทัดคิดว่าไม่เหมาะครับ

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

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 12:53 pm
by snasui
:D เนื่องากไม่ได้ระบุคำตอบมาด้วยว่าการแยกอายุและอาชีพให้แสดงผลลัพธ์เป็นอย่างไร ผมจึงสมมุติว่าให้แสดงเครื่องหมายถูกในช่องนั้น ๆ ที่ตรงกับอายุและอาชีพของชื่อและนามสกุลนั้น ๆ ในโอกาสถัดไปขอให้ระบุคำตอบลงมาด้วยมือจะได้เห็นว่าคำตอบเป็นแบบไหน อย่างไร จะได้ตอบได้ตรงประเด็น

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

ที่ชีตรายบุคคล
  1. เซลล์ K1 คีย์คำว่า ช่วงอายุ
  2. เซลล์ L1 คีย์สูตร
    Today()
    Enter
  3. เซลล์ K2 คีย์สูตร
    =LOOKUP(DATEDIF(I2,$L$1,"y"),{0,50,55,58},{"อายุ < 50 ปี","อายุ 50-54 ปี","อายุ 55-58 ปี","อายุ 59-60 ปี"})
    Enter > Copy ลงด้านล่าง
ที่ชีตแยกข้อมูล
  1. คอลัมน์ B:G เป็นข้อมูลที่ได้จากการกรองตามภาค
  2. เซลล์ H3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--(ราบบุคคล!$K$2:$K$50=H$2)),"\P;;;")
    Enter > Copy ไปด้านขวาถึง K2 > Copy ลงด้านล่าง
  3. เซลล์ L3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--ISNUMBER(SEARCH(L$2,ราบบุคคล!$B$2:$B$50))),"\P;;;")
    Enter > Copy ไปด้านขวาถึง P2 > Copy ลงด้านล่าง
สูตรนี้สามารถใช้ได้กับทุก Version ทั้งนี้เพราะว่าแนบไฟล์ที่เป็นนามสกุล .xls ซึ่งเป็นของ Version 2003- มีข้อจำกัดว่าใช้ฟังก์ชั่นใหม่ ๆ ไม่ได้ หากในการทำงานจริงมีการใช้โปรแกรม Version ที่สูงกว่า 2003 กรุณาแนบไฟล์ทีเ่ป็นนามสกุล .xlsx มา จะทำให้ตอบด้วยสูตรที่ทำงานกับข้อมูลปริมาณมากได้ดีกว่า Sumproduct ครับ

ตัวอย่างคำตอบตามไฟล์แนบครับ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 1:10 pm
by knan
snasui wrote: Sun Nov 17, 2019 12:53 pm :D เนื่องากไม่ได้ระบุคำตอบมาด้วยว่าการแยกอายุและอาชีพให้แสดงผลลัพธ์เป็นอย่างไร ผมจึงสมมุติว่าให้แสดงเครื่องหมายถูกในช่องนั้น ๆ ที่ตรงกับอายุและอาชีพของชื่อและนามสกุลนั้น ๆ ในโอกาสถัดไปขอให้ระบุคำตอบลงมาด้วยมือจะได้เห็นว่าคำตอบเป็นแบบไหน อย่างไร จะได้ตอบได้ตรงประเด็น

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

ที่ชีตรายบุคคล
  1. เซลล์ K1 คีย์คำว่า ช่วงอายุ
  2. เซลล์ L1 คีย์สูตร
    Today()
    Enter
  3. เซลล์ K2 คีย์สูตร
    =LOOKUP(DATEDIF(I2,$L$1,"y"),{0,50,55,58},{"อายุ < 50 ปี","อายุ 50-54 ปี","อายุ 55-58 ปี","อายุ 59-60 ปี"})
    Enter > Copy ลงด้านล่าง
ที่ชีตแยกข้อมูล
  1. คอลัมน์ B:G เป็นข้อมูลที่ได้จากการกรองตามภาค
  2. เซลล์ H3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--(ราบบุคคล!$K$2:$K$50=H$2)),"\P;;;")
    Enter > Copy ไปด้านขวาถึง K2 > Copy ลงด้านล่าง
  3. เซลล์ L3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--ISNUMBER(SEARCH(L$2,ราบบุคคล!$B$2:$B$50))),"\P;;;")
    Enter > Copy ไปด้านขวาถึง P2 > Copy ลงด้านล่าง
สูตรนี้สามารถใช้ได้กับทุก Version ทั้งนี้เพราะว่าแนบไฟล์ที่เป็นนามสกุล .xls ซึ่งเป็นของ Version 2003- มีข้อจำกัดว่าใช้ฟังก์ชั่นใหม่ ๆ ไม่ได้ หากในการทำงานจริงมีการใช้โปรแกรม Version ที่สูงกว่า 2003 กรุณาแนบไฟล์ทีเ่ป็นนามสกุล .xlsx มา จะทำให้ตอบด้วยสูตรที่ทำงานกับข้อมูลปริมาณมากได้ดีกว่า Sumproduct ครับ

ตัวอย่างคำตอบตามไฟล์แนบครับ

ไฟล์ .xlsx ค่ะ ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 1:16 pm
by snasui
:D นำสิ่งที่ผมตอบไปใส่ลงไปในไฟล์ล่าสุดแล้วแจ้งปัญหามาด้วยว่าติดปัญหาตรงไหน อย่าง หากยังไม่ติดปัญหาข้อให้ใช้วิธีการเดิมไปก่อนครับ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 6:27 pm
by knan
snasui wrote: Sun Nov 17, 2019 1:16 pm :D นำสิ่งที่ผมตอบไปใส่ลงไปในไฟล์ล่าสุดแล้วแจ้งปัญหามาด้วยว่าติดปัญหาตรงไหน อย่าง หากยังไม่ติดปัญหาข้อให้ใช้วิธีการเดิมไปก่อนครับ
ในsheet แยกข้อมูล ตรงช่วงอายุ ขึ้นแต่ช่อง อายุ < 50 ปี ค่ะ
ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 6:37 pm
by snasui
:D ต้องขึ้นกี่่ช่อง แต่ละช่องเป็นค่าใด คิดอย่างไรจึงได้ค่าเท่านั้น กรุณาอธิบายมาอย่างละเอียดครับ

ที่ผมทำไปเพียงแต่การคาดเดา กรุณาคีย์ตัวอย่างคำตอบที่ถูกต้อง พร้อมวิธีการคิด จะได้คำนวณเปรียบเทียบได้ครับ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 10:40 pm
by knan
snasui wrote: Sun Nov 17, 2019 6:37 pm :D ต้องขึ้นกี่่ช่อง แต่ละช่องเป็นค่าใด คิดอย่างไรจึงได้ค่าเท่านั้น กรุณาอธิบายมาอย่างละเอียดครับ

ที่ผมทำไปเพียงแต่การคาดเดา กรุณาคีย์ตัวอย่างคำตอบที่ถูกต้อง พร้อมวิธีการคิด จะได้คำนวณเปรียบเทียบได้ครับ
ขอโทษด้วยนะคะที่อธิบายไม่ละเอียดค่ะ คือ ช่วงอายุจะแบ่งเป็น 4 ช่องค่ะ
ช่องที่ 1 (H) = อายุ < 50 ปี
ช่องที่ 2 (I) = อายุ 50 -54 ปี
ช่องที่ 3 (J) = อายุ 55-58 ปี
ช่องที่ 3 (K) = อายุ 59-60 ปี ค่ะ
คล้ายช่องอาชีพค่ะ ที่ต้องขึ้นทุกช่องค่ะ
ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 10:54 pm
by snasui
:D แก้ดังนี้ครับ
แก้เซลล์ I2 จาก อายุ 50 -54 ปี เป็น อายุ 50-54 ปี สังเกตเครื่องหมาย - จะต้องติดกับตัวเลข 50 ไม่วรรค
แก้เซลล์ J2 จาก อายุ 55-58 ปี เป็น อายุ 55-58 ปี วรรคหลังอายุจะต้องมีแค่เคาะเดียว
แก้เซลล์ K2 จาก อายุ 59-60 ปี เป็น อายุ 59-60 ปี วรรคหลังอายุจะต้องมีแค่เคาะเดียว

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Sun Nov 17, 2019 11:31 pm
by knan
snasui wrote: Sun Nov 17, 2019 10:54 pm :D แก้ดังนี้ครับ
แก้เซลล์ I2 จาก อายุ 50 -54 ปี เป็น อายุ 50-54 ปี สังเกตเครื่องหมาย - จะต้องติดกับตัวเลข 50 ไม่วรรค
แก้เซลล์ J2 จาก อายุ 55-58 ปี เป็น อายุ 55-58 ปี วรรคหลังอายุจะต้องมีแค่เคาะเดียว
แก้เซลล์ K2 จาก อายุ 59-60 ปี เป็น อายุ 59-60 ปี วรรคหลังอายุจะต้องมีแค่เคาะเดียว
ได้แล้วค่ะ ขอบคุณนะคะ ขอบคุณมากๆค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Tue Jan 07, 2020 9:25 pm
by knan
knan wrote: Sun Nov 17, 2019 1:10 pm
snasui wrote: Sun Nov 17, 2019 12:53 pm :D เนื่องากไม่ได้ระบุคำตอบมาด้วยว่าการแยกอายุและอาชีพให้แสดงผลลัพธ์เป็นอย่างไร ผมจึงสมมุติว่าให้แสดงเครื่องหมายถูกในช่องนั้น ๆ ที่ตรงกับอายุและอาชีพของชื่อและนามสกุลนั้น ๆ ในโอกาสถัดไปขอให้ระบุคำตอบลงมาด้วยมือจะได้เห็นว่าคำตอบเป็นแบบไหน อย่างไร จะได้ตอบได้ตรงประเด็น

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

ที่ชีตรายบุคคล
  1. เซลล์ K1 คีย์คำว่า ช่วงอายุ
  2. เซลล์ L1 คีย์สูตร
    Today()
    Enter
  3. เซลล์ K2 คีย์สูตร
    =LOOKUP(DATEDIF(I2,$L$1,"y"),{0,50,55,58},{"อายุ < 50 ปี","อายุ 50-54 ปี","อายุ 55-58 ปี","อายุ 59-60 ปี"})
    Enter > Copy ลงด้านล่าง
ที่ชีตแยกข้อมูล
  1. คอลัมน์ B:G เป็นข้อมูลที่ได้จากการกรองตามภาค
  2. เซลล์ H3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--(ราบบุคคล!$K$2:$K$50=H$2)),"\P;;;")
    Enter > Copy ไปด้านขวาถึง K2 > Copy ลงด้านล่าง
  3. เซลล์ L3 คีย์สูตร
    =TEXT(SUMPRODUCT(--(ราบบุคคล!$G$2:$G$50=$F3),--(ราบบุคคล!$H$2:$H$50=$G3),--ISNUMBER(SEARCH(L$2,ราบบุคคล!$B$2:$B$50))),"\P;;;")
    Enter > Copy ไปด้านขวาถึง P2 > Copy ลงด้านล่าง
สูตรนี้สามารถใช้ได้กับทุก Version ทั้งนี้เพราะว่าแนบไฟล์ที่เป็นนามสกุล .xls ซึ่งเป็นของ Version 2003- มีข้อจำกัดว่าใช้ฟังก์ชั่นใหม่ ๆ ไม่ได้ หากในการทำงานจริงมีการใช้โปรแกรม Version ที่สูงกว่า 2003 กรุณาแนบไฟล์ทีเ่ป็นนามสกุล .xlsx มา จะทำให้ตอบด้วยสูตรที่ทำงานกับข้อมูลปริมาณมากได้ดีกว่า Sumproduct ครับ

ตัวอย่างคำตอบตามไฟล์แนบครับ

ไฟล์ .xlsx ค่ะ ขอบคุณค่ะ
สวัสดีค่ะ วันนี้มีเรื่องรบกวนที่คล้ายกับ ชีทที่แยกข้อมูลในข้อความนี้ค่ะ
จากรูปที่1จะเห็นได้ว่า copy สูตรมาแล้วลากค่ะ ซึ่งในเขตที่ 1,2,3 สามารถใช้สูตรตามข้างบนได้ค่ะ แค่ปรับสูตร [สูตร ==TEXT(SUMPRODUCT(--(เขต3!$G$2:$G$1304=$F3),--(เขต3!$H$2:$H$1304=$G3),--(เขต3!$K$2:$K$1304=H$2)),"\P;;;")]
แต่พอมาถึงรูปที่2 เขตที่ 4 ก็ใช้วิธีการเดิมค่ะ แต่มันขึ้น แบบนี้ค่ะ #DIV/0! [สูตร ==TEXT(SUMPRODUCT(--(เขต4!$G$2:$G$26099=$F3),--(เขต4!$H$2:$H$26099=$G3),--(เขต4!$K$2:$K$26099=H$2)),"\P;;;")] ลองปรับ แก้ เองแล้วค่ะก็ไม่ได้ เลยต้องขอรบกวนแนะนำด้วยค่ะ ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Tue Jan 07, 2020 9:47 pm
by snasui
:D แนบไฟล์ล่าสุดมาด้วยครับ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Tue Jan 07, 2020 10:54 pm
by knan
snasui wrote: Tue Jan 07, 2020 9:47 pm :D แนบไฟล์ล่าสุดมาด้วยครับ
พอดีพยายามแนบแล้วค่ะ แต่มันแจ้งว่าไฟลล์มีขนาดใหญ่เกินไป
เลยส่งลิงค์ที่อัพโหลดมาแทนค่ะ
https://mega.nz/#!hppxAKoL!2JXgbIvxxM7R ... ps_FEBPy40

ขอบคุณค่ะ

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Wed Jan 08, 2020 8:39 am
by logic
ผมพบว่าผิดปกติอยู่ 2 อย่างครับ

อย่างแรกคือ ชีตเขต 4 เซลล์ i17667, i19310, i19450 คีย์ปีไม่ถูกต้อง

อีกอันคือสูตรอ้างอิงไม่ตรงเซลล์ ให้คัดลอก K2 ลงไปใหม่ 😁

Re: ขอคำแนะนำค่ะ ถ้าจะแยกข้อมูลปริมาณมากจากอีก sheet หนึ่ง ไปอีก sheet หนึ่ง ต้องเขียนสูตรอย่างไรดีคะ?

Posted: Wed Jan 08, 2020 3:39 pm
by knan
logic wrote: Wed Jan 08, 2020 8:39 am ผมพบว่าผิดปกติอยู่ 2 อย่างครับ

อย่างแรกคือ ชีตเขต 4 เซลล์ i17667, i19310, i19450 คีย์ปีไม่ถูกต้อง

อีกอันคือสูตรอ้างอิงไม่ตรงเซลล์ ให้คัดลอก K2 ลงไปใหม่ 😁
ขอบคุณค่ะ เดี๋ยวจะตรวจสอบและลองดูอีกรอบนะคะ
All times are UTC+07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited