Snasui Excel Forum

การใช้สูตรในการอ้างอิงไฟล์ใน excel

ยังไม่เคลียร์ครับ

จาก

ในเซลล์ A56 เราสามารถสั่งให้ รหัสวิชา จาก sheet ม.4 ม.5 ม.6 ที่รหัสขึ้นต้นด้วย ค..... มาไว้โดยอัติโนมัติ ได้หรือป่าวคับ โดยไม่ต้องคีย์ใหม่คับ
รบกวน อ. นิดนึงนะคับ

(สิ่งที่ต้องการคือ ใน sheet ที่เป็นกลุ่มสาระวิชา เมื่อ คีย์ ข้อมูล ใน sheet ของ
ม. 1 - 6 แล้ว อยากให้รหัสที่ ที่ขึ้นต้นตามกลุ่มสาระ เช่น ไทย คือ ท คณิต คือ ค
เชื่อมโยงมายัง sheet ของกลุ่มสาระเลย ไม่ทราบว่าจะได้หรือป่าวคับ)

คำถาม

1. ชีทที่เป็นกลุ่มสาระวิชาคือชีทชื่ออะไรครับ
2. รหัสที่ขึ้นต้นตามกลุ่มสาระที่ว่านั้น ได้คีย์ไว้ที่เซลล์ไหนของแต่ละชีทหรือไม่เพื่อที่จะใช้เป็นเงื่อนไขได้ครับ

ตอบคำถามของ อ. ครับ
1. sheet ที่เป็นชื่อกลุ่มสาระคือ sheet ที่ชื่อ "ไทย คือ กลุ่มสาระภาษาไทย" "คณิต คือ กลุ่มสาระคณิตศาสตร์" ครับ
2. ตัวอักษร นำหน้า กลุ่มสาระ คีย์ไว้ ใน sheet ที่ชื่อ "ฐานข้อมูล" ครับ
ขอบคุณครับ

งดใช้ภาษาแชทในฟอรัม ช่วยแก้ด่วนที่สุดเท่าที่จะทำได้ครับ

ครับ ขอโทษครับ

ลองตามไฟล์แนบครับ

ที่ชีท คณิต

1. เซลล์ B48 คีย์ ค
2. เซลล์ D48 คีย์ 4
3. เซลล์ A56 คีย์
   =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56)))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
4. ทำเช่นนี้กับพื้นที่อื่น ๆ และชีทอื่น ๆ ตามต้องการ

ขอบคุณมากครับอาจารย์ ผมจะลองไปศึกษาดูอีกครั้งนึงครับ พอจะได้แนวทางแล้วครับ

รบกวนอาจารย์แปลโค๊ดให้หน่อยได้เปล่าครับ
ขอบคุณครับ

ไม่มีโค๊ดครับ มีแต่สูตรจ้า

ใช้ภาษาผิดไปเพราะเผลอเรอ ขออภัยด้วยครับ

ขอบคุณที่ช่วยแก้ให้ครับ

sutham wrote:รบกวนอาจารย์แปลโค๊ดให้หน่อยได้เปล่าครับ
ขอบคุณครับ

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

จาก =LOOKUP(CHAR(255),CHOOSE({1,2},"",INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56)))))

จะใช้ฟังก์ชั่น Lookup ครอบอยู่บนฟังก์ชั่นอื่น ๆ ซึ่งจะต้องแปลตัวนี้ซึ่งเป็นตัวนอกสุดก่อน โดย Lookup ที่ใช้นี้มีส่วนประกอบอยู่ 2 ส่วนประกอบคือ

1. CHAR(255)
2. CHOOSE({1,2},"",INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56))))

ความหมายคือให้หาผลลัพธ์ที่ได้จาก Char(255) (ซึ่ง Char(255) คืออักขระตัวสุดท้ายที่สามารถมีได้ใน Excel) จาก CHOOSE({1,2},"",INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56))))

ซึ่ง Choose ที่ใช้นี้ไม่ได้ใช้อย่างปกติแต่เป็นการใช้แบบประยุกต์ โดยมี 3 ส่วนประกอบคือ

1. {1,2}
2. ""
3. INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56)))

ความหมายคือให้นำส่วนประกอบที่ 2 และ 3 ของ Choose มาผนวกกันเป็นช่วงข้อมูลใหม่

ส่วน INDEX(ฐานข้อมูล!$C$2:$C$350,SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56))) มีส่วนประกอบ 2 ส่วนประกอบคือ

1. ฐานข้อมูล!$C$2:$C$350
2. SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56))

ความหมายคือจากข้อมูลในส่วนประกอบที่ 1 ให้ลำดับที่ได้จากผลลัพธ์ของช่วงที่ 2 มาแสดง

จากสูตร SMALL(IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)),ROWS(A$56:A56)) มีส่วนประกอบ 2 ส่วนประกอบคือ

1. IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1))
2. ROWS(A$56:A56)

ความหมายคือ จากส่วนประกอบที่ 1 ให้นำค่าที่เล็กที่สุดที่ได้จากผลลัพธ์ของส่วนประกอบที่ 2 มาแสดง

จาก IF(ฐานข้อมูล!$A$2:$A$350=$B$48,IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)) มีแค่ 2 ส่วนประกอบ คือ

1. ฐานข้อมูล!$A$2:$A$350=$B$48
2. IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1)

ความหมายคือ หากส่วนประกอบที่ 1 เป็นจริง ให้นำส่วนประกอบที่ 2 มาแสดง

จาก IF(ฐานข้อมูล!$F$2:$F$350=$D$48,ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1) มี 2 ส่วนประกอบคือ

1. ฐานข้อมูล!$F$2:$F$350=$D$48
2. ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1

ความหมายคือ หากส่วนประกอบที่ 1 เป็นจริง ให้นำส่วนประกอบที่ 2 มาแสดง

สูตร ROW(ฐานข้อมูล!$A$2:$A$350)-ROW(ฐานข้อมูล!$A$2)+1 จะให้ลำดับข้อมูลเรียงจากน้อยไปหามาก เป็น 1, 2, 3, 4,..., 349

ขอถือโอกาส ขโมยความรู้น่ะครับ

ยังเริ่มจาก IF อยู่เลยครับ ยังเข้าใจไม่ถึง 50 % ขอปูพื้นฐานก่อนครับ

รบกวนถาม อ. ต่ออีกนิดครับ
1. เมื่อผมได้ลอง coppy สูตรที่ อ. เขียนไว้ให้ ไปใช้กับชีทอื่นโดยโดยที่ไม่ได้ coppy และ สำเนา ชีท เพราะอะไรการแสดงผลจึงไม่ได้เหมือนกับที่ อ. ทำไว้ให้ครับ
ปล. ผมจะแก้ไขได้อย่างไร อยากหัดทำและต่อยอดสูตรของ อ. ไปอีกครับ
2. เมื่อผมคลิ๊กที่เซลล์ที่ อ. ตั้งสูตรไว้ ผมเห็น อ. ได้ใส่เครื่องหมาย {} ไว้ด้วย (ดูจากในแถบสูตร) แต่พอผมลองคลิ๊กในแถบสูตร เครื่องหมาย {} ก็หายไป พอลองกด enter แล้วผลที่แสดงในเซลล์นั้นก็เปลี่ยนไป เป็นเพราะอะไรครับ และเครื่องหมายดังกล่าว มีส่วนเกี่ยวข้องกับสูตรอย่างไรครับ จะแก้ไขได้อย่างไรบ้างคับ
ขอบคุณครับ

จากคำถามข้อ 1 คงต้องเห็นไฟล์ครับ จึงจะวินิจฉัยได้ว่าเพราะเหตุใด
ส่วนคำถามข้อ 2 เป็นเพราะสูตรนั้นเป็นสูตร Array หากได้อ่านทบทวนจากที่ผมแนะนำไปอย่างช้า ๆ จะทราบว่าต้องกดแป้น 3 แป้นให้รับสูตรคือ กดแป้น Ctrl+Shift ค้างไว้ก่อนแล้วค่อย Enter จะกด Enter อย่างเดียวไม่ได้

ขอบคุณครับ อ. ตอนนี้ผมทำได้แล้ว
แต่รบกวนถาม อ. ต่ออีกนิด ว่าถ้าเราจะให้หาตัวอักษรพร้อมกันทีเดียว 2 ตัวได้หรือเปล่าครับ ปกติสูตรที่ อ. ทำให้หาได้แค่ตัวอักษรตัวเดียว

สูตรนั้นไม่ได้จำกัดว่าตัวอักษรจำนวนกี่ตัวครับ ลองส่งไฟล์ที่เป็นปัญหา ระบุคำตอบที่ต้องการมาด้วยครับ

ขอโทษครับ ลืมแนบไฟล์ไปให้ครับ

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

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

ตอบคำถาม ประมาณนั้นครับ
คือ ในเซลล์ B48 หากเรา คีย์ ค รหัสวิชาที่ขึ้นต้นด้วย ค และมีเลขระดับชั้นตรงกับ D48 ในเซลล์ A56 เป็นต้นไปก็จะปรากฎตามเงื่อนไขที่ตั้งไว้
ทีนี่ผมอยากทราบต่อว่จะมีวิธีการตั้งสูตรอย่างไร ที่จะทำให้ในเซลล์ A56 เป็นต้นไป ให้มีรหัสวิชาทั้งที่ขึ้นต้นด้วย ค ด้วย และ อ ด้วย หรือตัวอื่นๆ ที่ต้องการ โดยที่ระดับชั้นในเซลล์ D48 ยังคงเดิม
(ปล. ผมลองแก้สูตรของ อ. โดยใช้ความรู้พื้นฐานแล้วไม่ได้ เลยโพสมาถามครับ)
ขอบคุณครับ