Snasui Excel Forum

Posted: **Fri Jun 18, 2021 12:12 pm**

เรียนอาจารย์และเพื่อนสมาชิก
ขอวิธีตัดชื่อตำแหน่งและระดับ มี 2 กรณี อธิบายดังนี้

กรณีที่ 1 คนปฏิบัติอยู่ ไม่รวมอัตราว่าง
1.ให้นำชื่อตำแหน่งใน column L มาวางไว้ ใน column C
2.ให้นำตำแหน่งระดับใน column M ตัดมาเพียงระดับ มาวางไว้ ใน column D
ข้อแตกต่าง ชื่อตำแหน่งใน column M จะเหมือนกัน ชื่อตำแหน่งใน column M เสมอ
แต่ต่อท้ายด้วยระดับเพิ่มขึ้นมา
3.ให้นำจำนวนคน ใน column N มาวางไว้ ใน column E ตามจำนวนคนที่ปฏิบัติงานอยู่

กรณีที่ 2 ปฎิบัติงานและรวมอัตราว่าง
1.จะเป็นการเพิ่มตำแหน่งอัตราว่างเข้าไป โดยพิจารณาจาก column P กับ column G เมื่อตรงกันแล้ว
ให้ column H ขึ้นว่า (ว่าง) ตรงกับชื่อตำแหน่งนั้นๆ
2.หาก column G ไม่มีตำแหน่ง แต่ column G มีตำแหน่ง
เนื่องจากเป็นตำแหน่งใหม่หรือเป็นตำแหน่งที่ว่างไม่มีคนปฏิบัติงานเลย
ก็ในนำตำแหน่งใน column G ที่ว่างนั้น ๆ ไป วางไปที่ columun G
3.ให้นำจำนวนคน ใน column N , column G มาวางไว้ ใน column I ตามจำนวนคนที่ปฏิบัติงานอยู่และจำนวนที่มีอัตราว่าง

หมายเหตุ ข้อมูลใน column L ถึง column G อาจจะไม่ได้จัดเรียงมาก็ได้ ตำแหน่ง ระดับ อาจจะสลับไปมา

ขอบคุณครับ

ตัดชื่อตำแหน่งและระดับ.xlsx

Posted: **Fri Jun 18, 2021 5:36 pm**

ต้องมี Function LET ไม่งั้นสูตรจะยาวมาก

ไม่รวมว่าง

=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(ROWS(M5:M19)),MATCH(L5:L10&"*",M5:M19,),L5:L10,""),SUBSTITUTE(M5:M19,LOOKUP(SEQUENCE(ROWS(M5:M19)),MATCH(L5:L10&"*",M5:M19,),L5:L10),),N5:N19)

รวมว่าง

Code: Select all

=LET(p,L5:L10,l,M5:M19,nl,N5:N19,v,P5:P10,nv,Q5:Q10,a,SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,p,v)&"</m></x>","//m[not(. = preceding::m/.)]")),
s,SEQUENCE(ROWS(a)),c,COUNTIFS(l,a&"*")+COUNTIFS(v,a),t,SEQUENCE(SUM(c),,0),m,MMULT(--(s>TRANSPOSE(s)),c),
pp,XLOOKUP(t,m,a,""),pa,LOOKUP(t,m,a),ll,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,l,v&"ๆ")&"</m></x>","//m"),
nn,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,nl,nv)&"</m></x>","//m"),
CHOOSE({1,2,3},pp,SUBSTITUTE(SUBSTITUTE(SORT(ll),pa,),"ๆ","(ว่าง)"),SORTBY(nn,ll)))

Posted: **Mon Jun 21, 2021 10:38 am**

Bo_ry wrote: Fri Jun 18, 2021 5:36 pm ต้องมี Function LET ไม่งั้นสูตรจะยาวมาก

ไม่รวมว่าง

=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(ROWS(M5:M19)),MATCH(L5:L10&"*",M5:M19,),L5:L10,""),SUBSTITUTE(M5:M19,LOOKUP(SEQUENCE(ROWS(M5:M19)),MATCH(L5:L10&"*",M5:M19,),L5:L10),),N5:N19)

กรณีไม่รวมว่าง ได้ตามต้องการ ขอบคุณครับ

Posted: **Mon Jun 21, 2021 10:41 am**

Function LET

Code: Select all

=LET(p,L5:L10,l,M5:M19,nl,N5:N19,v,P5:P10,nv,Q5:Q10,a,SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,p,v)&"</m></x>","//m[not(. = preceding::m/.)]")),
s,SEQUENCE(ROWS(a)),c,COUNTIFS(l,a&"*")+COUNTIFS(v,a),t,SEQUENCE(SUM(c),,0),m,MMULT(--(s>TRANSPOSE(s)),c),
pp,XLOOKUP(t,m,a,""),pa,LOOKUP(t,m,a),ll,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,l,v&"ๆ")&"</m></x>","//m"),
nn,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,nl,nv)&"</m></x>","//m"),
CHOOSE({1,2,3},pp,SUBSTITUTE(SUBSTITUTE(SORT(ll),pa,),"ๆ","(ว่าง)"),SORTBY(nn,ll)))

นำ Function LET เข้ามาใช้ใน excel อย่างไรครับ

ขึ้น =_xlfn.LET(_xlpm.p,L5:L10,_xlpm.l,M5:M19,_xlpm.n,N5:N19,_xlpm.s,SEQUENCE(ROWS(_xlpm.l)),_xlpm.m,MATCH(_xlpm.p&"*",_xlpm.l,),CHOOSE({1,2,3},XLOOKUP(_xlpm.s,_xlpm.m,_xlpm.p,""),SUBSTITUTE(_xlpm.l,LOOKUP(_xlpm.s,_xlpm.m,_xlpm.p),),_xlpm.n))

ไม่พอสูตรใน excel

Posted: **Mon Jun 21, 2021 1:49 pm**

ยกเลิกก่อน

Posted: **Mon Jun 21, 2021 3:53 pm**

ขออธิบายปัญหาเพิ่มเติมจากเดิม เมื่อนำไปใช้แล้วมีไม่ตรงดังนี้

เมื่อนำสูตรไปใช้แล้ว ตัวอย่างใน Sheet1 ไม่ได้เรียง ทำให้สูตร เรียงข้อมูลไม่ตรงตามตำแหน่ง และไม่ตรงกัน และใน column D ระดับอยากให้เรียงตามระดับที่ต้องการ (ถ้าไม่เรียงระดับก็ไม่เป็น แต่ถ้าเรียงระดับจะดีมากครับ คงมาเรียงเอง) โดยได้อธิบายไว้ใน column A และ B แล้ว

ผมได้ลองทำใน Sheet1 ข้อมูลเรียงตามตัวอักษร ใน column L และ column M ทำให้ รายงานใน column C และ column D ตรงตามตำแหน่งและระดับได้ แต่ บางตำแหน่งไม่เรียงระดับให้ (ถ้าไม่เรียงระดับก็ไม่เป็น แต่ถ้าเรียงระดับจะดีมากครับ คงมาเรียงเอง)

มีที่ไม่ได้อยู่ตรงตำแหน่งผู้อำนวยการ ที่ column L ที่ช่อง C73 ต้องเป็น
ผู้อำนวยการเฉพาะด้าน(แพทย์)
ตรงตำแหน่งเป็นค่าว่าง ที่ column L ที่ช่อง C75 ต้องเป็น
ผู้อำนวยการ
และ ที่ช่อง C14 เป็น เจ้าพนักงานวิทยาศาสตร์
ที่ช่อง D14 เป็น ชำนาญงาน

การที่คลุมพื้นที่ข้อมูลมากกว่า ข้อมูลที่มีอยู่ใน ใน column L และ colunm M จึงทำให้ ในSheet1 ไม่ได้เรียง และ Sheet1 ข้อมูลเรียงตามตัวอักษร column D ระดับไม่ตัดตำแหน่งออกไป ให้เหลือเพียงระดับ เช่น ในช่องระดับ เจ้าหน้าทีธุรการปฏิบัติงาน ที่ถูกต้องเป็น ปฏิบัติงาน

หมายเหตุ มีการคลุมข้อมูลให้มากกว่าข้อมูลที่มีอยู่เพื่ออนาคตอาจมีมากขึ้นกว่าเดิมได้ ใน column L และ
colunm M

ตัดชื่อตำแหน่งและระดับ1.xlsx

Posted: **Mon Jun 21, 2021 7:45 pm**

C5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

H5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

I5
=SUBSTITUTE(SUBSTITUTE(SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,M5:M200,FILTER(P5:P200,Q5:Q200)&"ๆ")&"</m></x>","//m")),LOOKUP(ROW(H5#),ROW(H5#)/(H5#>""),H5#),),"ๆ","(ว่าง)")

J5
=SORTBY(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,N5:N200,Q5:Q200)&"</m></x>","//m"),FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,M5:M200,FILTER(P5:P200,Q5:Q200)&"ๆ")&"</m></x>","//m"))

Function Let ต้อง Update MS365 Insiders Channel

Availability Notes
LET is now available to Office 365 Subscribers in the Insiders Channel (Beta and Current Channel Preview) and will be available to users of other channels later this year. I’ll update this blog as LET becomes available to more channels.

https://techcommunity.microsoft.com/t5/ ... -p/1233572

Posted: **Mon Jun 21, 2021 8:18 pm**

อีกแนวทางทำข้อมูลให้เป็น Database แล้วสรุปข้อมูลด้วย PivotTable ช่วยครับ

ตัวอย่างสูตรที่จะนำข้อมูลเดิมมาทำเป็น Database

ที่ U5 คีย์
=INDEX($M$5:$M$100,ROWS(U$5:U5))&""
Enter > Copy ลงด้านล่าง
ที่ T5 คีย์
=IFERROR(IF(U5<>"",LOOKUP(2,1/(ISNUMBER(SEARCH($L$5:$L$50,U5))),$L$5:$L$50),INDEX($P$5:$P$10,COUNTIFS(U$5:U5,""))),"")
Enter > Copy ลงด้านล่าง
ที่ V5 คีย์
=IF(T5="","",IF(U5<>"",INDEX($N$5:$N$100,ROWS(V$5:V5)),INDEX($Q$5:$Q$10,COUNTIFS(U$5:U5,""))))
Enter > Copy ลงด้านล่าง

จากนั้นนำ Database มาทำรายงาน สามารถกรองเอาเฉพาะตำแหน่งระดับเฉพาะที่ว่าง ไม่ว่าง หรือทั้งหมดได้จากคอลัมน์ Z ตามตัวอย่างในไฟล์แนบครับ

Posted: **Tue Jun 22, 2021 9:48 am**

Bo_ry wrote: Mon Jun 21, 2021 7:45 pm C5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

H5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

I5
=SUBSTITUTE(SUBSTITUTE(SORT(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,M5:M200,FILTER(P5:P200,Q5:Q200)&"ๆ")&"</m></x>","//m")),LOOKUP(ROW(H5#),ROW(H5#)/(H5#>""),H5#),),"ๆ","(ว่าง)")

J5
=SORTBY(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,N5:N200,Q5:Q200)&"</m></x>","//m"),FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,M5:M200,FILTER(P5:P200,Q5:Q200)&"ๆ")&"</m></x>","//m"))

Function Let ต้อง Update MS365 Insiders Channel

Availability Notes
LET is now available to Office 365 Subscribers in the Insiders Channel (Beta and Current Channel Preview) and will be available to users of other channels later this year. I’ll update this blog as LET becomes available to more channels.

https://techcommunity.microsoft.com/t5/ ... -p/1233572

ยังไม่ตรงตามต้องการ อธิบายดังนี้

กรณี จากสูตร ใน column C ที่ถูกต้อง แถบสี ใส่ตำแหน่งที่ถูกต้องให้เห็นใน column B
ใน column D ที่ถูกต้อง แถบสี ใส่ตำแหน่งที่ถูกต้องให้เห็นใน column F

กรณี จากสูตร ใน column H ที่ถูกต้อง แถบสี ใส่ตำแหน่งที่ถูกต้องให้เห็นใน column G
ใน column I ที่ถูกต้อง แถบสี ใส่ตำแหน่งที่ถูกต้องให้เห็นใน column K

ขอบคุณครับ

ตัดชื่อตำแหน่งและระดับ1 (1).xlsx

Posted: **Tue Jun 22, 2021 4:14 pm**

C5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),LOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200))),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

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

ชำนาญการ
ชำนาญการพิเศษ

จะให้เรียงตามใจฉันแบบนี้
ชำนาญการพิเศษ
ชำนาญการ

ก็ต้องสร้าง custom list แล้วกดเรียงเอง ซึ่งใช้สูตรไม่ได้ ต้องไป VBA

พอเป็น VBA ก็ต้องไปอ่าน
ข้อ 5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ

Posted: **Wed Jun 23, 2021 9:37 am**

snasui wrote: Mon Jun 21, 2021 8:18 pm อีกแนวทางทำข้อมูลให้เป็น Database แล้วสรุปข้อมูลด้วย PivotTable ช่วยครับ

ตัวอย่างสูตรที่จะนำข้อมูลเดิมมาทำเป็น Database

ที่ U5 คีย์
=INDEX($M$5:$M$100,ROWS(U$5:U5))&""
Enter > Copy ลงด้านล่าง

ที่ T5 คีย์
=IFERROR(IF(U5<>"",LOOKUP(2,1/(ISNUMBER(SEARCH($L$5:$L$50,U5))),$L$5:$L$50),INDEX($P$5:$P$10,COUNTIFS(U$5:U5,""))),"")
Enter > Copy ลงด้านล่าง

ที่ V5 คีย์
=IF(T5="","",IF(U5<>"",INDEX($N$5:$N$100,ROWS(V$5:V5)),INDEX($Q$5:$Q$10,COUNTIFS(U$5:U5,""))))
Enter > Copy ลงด้านล่าง

จากนั้นนำ Database มาทำรายงาน สามารถกรองเอาเฉพาะตำแหน่งระดับเฉพาะที่ว่าง ไม่ว่าง หรือทั้งหมดได้จากคอลัมน์ Z ตามตัวอย่างในไฟล์แนบครับ

ขอบคุณครับ จะนำไปเป็นแนวทางครับ

Posted: **Wed Jun 23, 2021 9:37 am**

Bo_ry wrote: Tue Jun 22, 2021 4:14 pm C5
=CHOOSE({1,2,3},XLOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200)),""),
SUBSTITUTE(SORT(FILTER(M5:M200,N5:N200)),LOOKUP(SEQUENCE(COUNTA(M5:M200)),MATCH(SORT(UNIQUE(L5:L200))&"*",SORT(M5:M200),),SORT(UNIQUE(L5:L200))),),FILTER(SORTBY(N5:N200,M5:M200),N5:N200))

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

ชำนาญการ
ชำนาญการพิเศษ

จะให้เรียงตามใจฉันแบบนี้
ชำนาญการพิเศษ
ชำนาญการ

ก็ต้องสร้าง custom list แล้วกดเรียงเอง ซึ่งใช้สูตรไม่ได้ ต้องไป VBA

พอเป็น VBA ก็ต้องไปอ่าน
ข้อ 5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ

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

ชำนาญการ
ชำนาญการพิเศษ

ไม่เป็นไร

แต่มีในส่วนนี้
ที่ไม่ตรงกันอยู่ดังนี้
ที่ถูกต้อง B15 , F15
1 ตำแหน่ง ระดับ
ผู้อำนวยการเฉพาะด้าน(แพทย์) อำนวยการระดับอำนวยการระดับสูง
ผู้อำนวยการอำนวยการระดับต้น

ในตำแหน่ง มีชื่อที่คล้ายกันแต่ไม่เหมือนกัน
เจ้าพนักงานวิทยาศาสตร์
เจ้าพนักงานวิทยาศาสตร์การแพทย์

ที่ถูกต้อง ที่ B79
2 ตำแหน่ง ระดับ
เจ้าพนักงานวิทยาศาสตร์การแพทย์ ชำนาญงาน

ที่ถูกต้อง ที่ D83 , D84
3 ตำแหน่ง ระดับ
พยาบาลวิชาชีพ ชำนาญการพิเศษ
ปฎิบัติการ

ไม่ถูกต้อง ไม่ตัดตำแหน่งออก ในช่องระดับ
พยาบาลวิชาชีพชำนาญการพิเศษ
พยาบาลวิชาชีพปฏิบัติการ

ตัดชื่อตำแหน่งและระดับ2.xlsx

Snasui Excel Forum

ตัดชื่อตำแหน่งและระดับ

ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ

Re: ตัดชื่อตำแหน่งและระดับ