Page 1 of 1
ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 9:44 am
by ajsudkedt
จากข้อมูลในชีต input1
นำมาจับคู่กับข้อมูลในชีต input2
แล้วให้ได้ผลลัพธ์ในชีต output
ต้องเขียนสูตรอย่างไร
ตามภาพ
ขอบคุณครับ
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 9:45 am
by ajsudkedt
จากข้อมูลในชีต input1
นำมาจับคู่กับข้อมูลในชีต input2
แล้วให้ได้ผลลัพธ์ในชีต output
ต้องเขียนสูตรอย่างไร
ตามไฟล์แนบ
ขอบคุณครับ
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 11:45 am
by norkaz
...
ในการใช้งานจริง ควรปรับฐานข้อมูลให้อยู่ในรูปตารางที่ง่ายในการนำไปใช้ เช่น
ชีท input2 ควรแยก รหัส คำ นำหน้าชื่อ ชื่อ นามสกุล ออกมาจากกัน อาจจะใช้ วิธีการ
ง่ายๆ เช่น Text to columns ไปช่วยเป็นต้น หรือใช้สูตร เช่น ชีท input2
C2
=LEFT(B2,3)
D2
=LOOKUP(99,SEARCH({"นาย","นาง","นางสาว"},B2),{"นาย","นาง","นางสาว"})
E2
=LEFT(MID(B2,8+LEN(D2),99),SEARCH(" ",MID(B2,8+LEN(D2),99))-1)
F2
=TRIM(SUBSTITUTE(MID(B2,8+LEN(D2),99),E2,""))
========
ที่ชีท output
F2
=COUNT(INDEX(--(SEARCH(MID(input1!C2:F2,4,3),input2!$B$2:$B$150)>0),0))
Copy ลงไป
A2
=INDEX(input1!A:A,AGGREGATE(15,6,ROW($F$2:$F$13)/(COLUMN($A2:$Z2)<=$F$2:$F$13),ROWS(G$2:G2)))
B2:Dxx
=IFERROR(VLOOKUP(LOOKUP(999,SEARCH(input2!$C$2:$C$150,VLOOKUP($A2,input1!$A$2:$F$4,2+COUNTIF($A$2:$A2,$A2),0)),input2!$C$2:$C$150),input2!$C$2:$F$150,COLUMNS($A2:B2),0),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 12:39 pm
by norkaz
...
ถ้าไม่จัดฐานข้อมูล ใช้จากโจทย์เดิม
ตัวอย่างสูตร ชีท output
F2 (Helper)
=COUNT(INDEX(SEARCH(MID(input1!C2:F2,4,3),input2!$B$2:$B$150),0))
A2
=INDEX(input1!A:A,AGGREGATE(15,6,ROW($F$2:$F$16)/(COLUMN($A$2:$L$2)<=$F$2:$F$16),ROWS($A$2:A2)))
B2
=IFERROR(LOOKUP(99,SEARCH({"นาย","นาง","นางสาว"},INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2)))),{"นาย","นาง","นางสาว"}),"")
C2
=IFERROR(LEFT(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+8,99),SEARCH(" ",MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+8,99))-1),"")
D2
=IFERROR(TRIM(SUBSTITUTE(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+8,99),C2,"")),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 1:50 pm
by ajsudkedt
ต้องขออภัยด้วยครับ
ในชีท input2
ในคอลัมน์ B
จำนวนเคาะวรรค
ระหว่างเลข กับ คำนำหน้าชื่อ
จะต้องห่างกันแค่ 2 เคาะวรรค
จะต้องปรับสูตรอย่างไรครับ
ตามภาพแล้วไฟล์แนบครับ
ขอบคุณครับ
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 2:21 pm
by norkaz
...
ปรับที่ +8 ให้เป็น +6
F2 - Helper
=COUNT(INDEX(SEARCH(MID(input1!C2:F2,4,3),input2!$B$2:$B$150),0))
A2
=INDEX(input1!A:A,AGGREGATE(15,6,ROW($F$2:$F$16)/(COLUMN($A$2:$L$2)<=$F$2:$F$16),ROWS($A$2:A2)))
B2
=IFERROR(LOOKUP(99,SEARCH({"นาย","นาง","นางสาว"},INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2)))),{"นาย","นาง","นางสาว"}),"")
C2
=IFERROR(LEFT(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99),SEARCH(" ",MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99))-1),"")
D2
=IFERROR(TRIM(SUBSTITUTE(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99),C2,"")),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 4:20 pm
by norkaz
...
อีกวิธี ไม่ต้องใช้ Helper ปรับสูตรที่ A2 ส่วน B2, C2, D2 ใช้สูตรเดิม
A2
=IFERROR(INDEX(input1!A:A,AGGREGATE(15,6,ROW(input1!$C$2:$C$99)/(--MID(input1!$C$2:$F$99,4,1)>=0),ROWS($A$2:A2))),"")
B2
=IFERROR(LOOKUP(99,SEARCH({"นาย","นาง","นางสาว"},INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2)))),{"นาย","นาง","นางสาว"}),"")
C2
=IFERROR(LEFT(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99),SEARCH(" ",MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99))-1),"")
D2
=IFERROR(TRIM(SUBSTITUTE(MID(INDEX(input2!$B$2:$B$150,INDEX(MATCH(INDEX(MID(INDEX(input1!$C$2:$F$4,MATCH($A2,input1!$A$2:$A$4,0),0),4,3),0),INDEX(LEFT(input2!$B$2:$B$150,3),0),0),COUNTIF($A$2:$A2,$A2))),LEN($B2)+6,99),C2,"")),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 5:49 pm
by ajsudkedt
หากมีการเพิ่มข้อมูลในชีท input1
ต้องปรับสูตรอย่างไร
ตามภาพและไฟล์แนบครับ
ขอบคุณครับ
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 7:17 pm
by norkaz
...
A2
=IFERROR(INDEX(input1!A:A,AGGREGATE(15,6,ROW(input1!$C$2:$C$999)/(--MID(input1!$C$2:$F$999,4,1)>=0),ROWS($A$2:A2))),"")
B2
=IFERROR(LOOKUP(999,SEARCH({"นาย","นาง","นางสาว"},LOOKUP(999,SEARCH(MID(VLOOKUP(A2,input1!$A$2:$F$999,2+COUNTIF($A$2:A2,A2),0),4,3),LEFT(input2!$B$2:$B$999,3)),input2!$B$2:$B$999)),{"นาย","นาง","นางสาว"}),"")
C2
=IFERROR(MID(VLOOKUP(A2,input1!$A$2:$F$999,COUNTIF($A$2:A2,A2)+2,0),9,999),"")
D2
=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(MID(LOOKUP(999,SEARCH(MID(VLOOKUP(A2,input1!$A$2:$F$999,COUNTIF($A$2:A2,A2)+2,0),4,3),input2!$B$2:$B$999),input2!$B$2:$B$999),6,999),B2,""),C2,"")),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
Re: ขอสูตรจัดรายชื่อตามรูปแบบที่กำหนดครับ
Posted: Fri Apr 14, 2023 10:42 pm
by ajsudkedt
norkaz wrote: Fri Apr 14, 2023 7:17 pm
...
A2
=IFERROR(INDEX(input1!A:A,AGGREGATE(15,6,ROW(input1!$C$2:$C$999)/(--MID(input1!$C$2:$F$999,4,1)>=0),ROWS($A$2:A2))),"")
B2
=IFERROR(LOOKUP(999,SEARCH({"นาย","นาง","นางสาว"},LOOKUP(999,SEARCH(MID(VLOOKUP(A2,input1!$A$2:$F$999,2+COUNTIF($A$2:A2,A2),0),4,3),LEFT(input2!$B$2:$B$999,3)),input2!$B$2:$B$999)),{"นาย","นาง","นางสาว"}),"")
C2
=IFERROR(MID(VLOOKUP(A2,input1!$A$2:$F$999,COUNTIF($A$2:A2,A2)+2,0),9,999),"")
D2
=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(MID(LOOKUP(999,SEARCH(MID(VLOOKUP(A2,input1!$A$2:$F$999,COUNTIF($A$2:A2,A2)+2,0),4,3),input2!$B$2:$B$999),input2!$B$2:$B$999),6,999),B2,""),C2,"")),"")
** เพื่อนสมาชิกกรุณา คลิกโฆษณาหน้าเพจ เพื่อเป็นการช่วยกันสนับสนุนฟอรั่มครับ
Norkaz
กราบขอบพระคุณท่านมาก ๆ ครับ
สูตรที่ท่านให้มา ได้ผลดั่งต้องการเลยครับ