salary ที่เป็น text ให้รวมจำนวนเป็นตัวเลข

[Totem]

เรียนอาจารย์และเพื่อนสมาชิก

ต้องการรวม salary ที่เป็น text ให้รวมจำนวนเป็นตัวเลข และแสดงรายการตรงตามชื่อคนนั้นๆ

ขออธิบายดังนี้

กรณีที่ 1. เมื่อ คำนำหน้า ชื่อ นามสกุล เลขที่ ใน columun A to column D ตรงกับ columun V to column Y
ให้นำรายการข้อมูลการขึ้นเงินเดือนจาก column Z to column AI มาวางใน columun E to column N
ตรงตามคนๆนั้น
กรณีที่ 2. ใน column O เป็นการรวมจำนวนการขึ้นเงินเดือนแต่ละปีตั้งแต่ columun E to column N มาแสดงใน column O
กรณีที่ 3. ใน column P เป็นการนำ % ที่อยู่ในปีนั้นๆ ตั้งแต่ columun E to column N มาแสดงใน column P

ข้อสังเกตุ ข้อมูลได้มาจาก column Z to column AI เป็น text


ขอบคุณครับ

salary.xlsx

[norkaz]

E3

=INDEX($Z$3:$AI$13,LOOKUP(99,ROW($A$3:$A$13)/($A3&$B3&$C3&$D3=$V$3:$V$13&$W$3:$W$13&$X$3:$X$13&$Y$3:$Y$13))-ROW($A$2),COLUMNS($E3:E3))

Copy ไปขวา และลงล่าง


O3

=SUM(IF(E3:N3="-",0,--IFERROR(MID(E3:N3,SEARCH("%",E3:N3)+2,9),E3:N3)))

Ctrl + Shift + Enter
Copy ลงล่าง


P3

=IFERROR(LOOKUP(1,--LEFT(E3:N3,SEARCH("%",E3:N3))),"")

Copy ลงล่าง

Norkaz

[Totem]

จากสูตร =INDEX($Z$3:$AI$13,LOOKUP(99,ROW($A$3:$A$13)/($A3&$B3&$C3&$D3=$V$3:$V$13&$W$3:$W$13&$X$3:$X$13&$Y$3:$Y$13))-ROW($A$2),COLUMNS($E3:E3))

ไปปรับใช้กับข้อมูลจริง ขึ้น #N/A ครับ
กำลังหาอยู่ว่า ขึ้น #N/A ได้อย่างไร ยังไม่เจอครับงง

[norkaz]

จากสูตร =INDEX($Z$3:$AI$13,LOOKUP(99,ROW($A$3:$A$13)/($A3&$B3&$C3&$D3=$V$3:$V$13&$W$3:$W$13&$X$3:$X$13&$Y$3:$Y$13))-ROW($A$2),COLUMNS($E3:E3))

ไปปรับใช้กับข้อมูลจริง ขึ้น #N/A ครับ
กำลังหาอยู่ว่า ขึ้น #N/A ได้อย่างไร ยังไม่เจอครับงง

แนบไฟล์นั้นมาดูครับ

Norkaz

[Totem]

จากสูตร =INDEX($Z$3:$AI$13,LOOKUP(99,ROW($A$3:$A$13)/($A3&$B3&$C3&$D3=$V$3:$V$13&$W$3:$W$13&$X$3:$X$13&$Y$3:$Y$13))-ROW($A$2),COLUMNS($E3:E3))

ไปปรับใช้กับข้อมูลจริง ขึ้น #N/A ครับ
กำลังหาอยู่ว่า ขึ้น #N/A ได้อย่างไร ยังไม่เจอครับงง

แนบไฟล์นั้นมาดูครับ

Norkaz

เจอแล้วครับ ใส่วงเล็บไม่ครบ ได้ตามต้องการครับ

ขอถาม (MID(E3:N3,SEARCH("%",E3:N3)+2,9)
+2 เพราะอะไร
กับ 9
ยังไม่เข้าใจครับ

[norkaz]

จากสูตร =INDEX($Z$3:$AI$13,LOOKUP(99,ROW($A$3:$A$13)/($A3&$B3&$C3&$D3=$V$3:$V$13&$W$3:$W$13&$X$3:$X$13&$Y$3:$Y$13))-ROW($A$2),COLUMNS($E3:E3))

ไปปรับใช้กับข้อมูลจริง ขึ้น #N/A ครับ
กำลังหาอยู่ว่า ขึ้น #N/A ได้อย่างไร ยังไม่เจอครับงง

แนบไฟล์นั้นมาดูครับ

Norkaz

เจอแล้วครับ ใส่วงเล็บไม่ครบ ได้ตามต้องการครับ

ขอถาม SEARCH("%",E3:N3)+2
+2 เพราะอะไร ยังไม่เข้าใจครับ

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

เช่นถ้าไม่เว้นเคาะวรรค ต้อง บวก 1 หรือ ตามสถานการณ์ ที่โจทย์กำหนด

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

บางครั้ง มี รั่ว-ซึม-หลง คงไม่ว่ากัน

ยินดีที่สอบถามมาครับ

Norkaz

[norkaz]

...

เพิ่มเติม เลข ,9 น่าจะหมายถึง MID

MID( ...., ตั้งแต่ตัวที่,...,เอามากี่ตัว ...)

ตรงเอามากี่ตัวตรงนั้น... ที่ใส่ 9 เพราะ ให้เอามา 9 ตัว ตั้งแต่ตำแหน่ง ที่ +2 ด้วยคาดว่า ตัวเลขที่ต้องการ หลัง % คง ไม่เกิน 9 ตัว ถ้าเกิน ก็บวกเผื่อๆ ไปเช่น 20

ส่วนใหญ่ การกำหนดเลข เผื่อไป มักนิยม เลข 9 ไม่ได้หมายถึง การเล่นไสยศาสตร์ แต่อย่างใด แต่ในความรู้สึก ของผู้คนทั่วๆไป โดยเฉพาะ คนเขียนสูตร มักเข้าใจกันว่า (หรืออาจจะเป็นผมคนเดียว) เลข 9 หรือ 99 หรือ 999 หรือ 9^9 คือเลขที่มากๆๆ

ในความเป็นจริง แล้วจะใส่เลขอะไรก็ได้เช่น 4178 เป็นต้น

Norkaz

[Totem]

แนบไฟล์นั้นมาดูครับ

Norkaz

เจอแล้วครับ ใส่วงเล็บไม่ครบ ได้ตามต้องการครับ

ขอถาม SEARCH("%",E3:N3)+2
+2 เพราะอะไร ยังไม่เข้าใจครับ

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

เช่นถ้าไม่เว้นเคาะวรรค ต้อง บวก 1 หรือ ตามสถานการณ์ ที่โจทย์กำหนด

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

บางครั้ง มี รั่ว-ซึม-หลง คงไม่ว่ากัน

ยินดีที่สอบถามมาครับ

Norkaz

ขอบคุณครับ

มีปัญหาเพิ่มเติมในการปรับรายงาน ขออธิบายดังนี้

ในช่อง A6 , A7 หรือ A ใดๆ จะแบ่งตามแผนก หน่วยงานเป็นช่วงๆ
เมื่อรวมจำนวนปีใดๆ เช่น ปีที่ 1 ใน column E และ column อื่นๆ ตรงแถวสุดท้ายที่ แถวที่ 10
เกิดการรวมที่ผิดพลาด ขึ้น #VALUE! อยากให้ปรับสูตรแล้วรวมได้ค่าตัวเลข
เมื่อกระทบยอด ROW 10 กับ Column O จะได้จำนวนตัวเลขเท่ากันครับ

salary_Ans1.xlsx

[norkaz]

..

ตัวอย่างสูตร

E10

=SUM(IFERROR(IFERROR(--MID(E3:E9,SEARCH("%",E3:E9)+2,9),--E3:E9),0))

Ctrl + Shift + Enter

Norkaz

[Totem]

แนบไฟล์นั้นมาดูครับ

Norkaz

เจอแล้วครับ ใส่วงเล็บไม่ครบ ได้ตามต้องการครับ

ขอถาม SEARCH("%",E3:N3)+2
+2 เพราะอะไร ยังไม่เข้าใจครับ

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

เช่นถ้าไม่เว้นเคาะวรรค ต้อง บวก 1 หรือ ตามสถานการณ์ ที่โจทย์กำหนด

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

บางครั้ง มี รั่ว-ซึม-หลง คงไม่ว่ากัน

ยินดีที่สอบถามมาครับ

Norkaz

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

555ครับ จากสูตรที่ให้มา ผมลองทำให้ข้อมูล ใน column Z - column AI ตรง หลัง % เป็นเว้น 2 เคาะวรรค
สูตรที่่ให้มาก็สามารถบวกรวมตัวเลขยังถูกต้องเหมือนเดิมครับ ดังนั้น +2 ก็ไม่ต้องบวกเพิ่ม

[norkaz]

เจอแล้วครับ ใส่วงเล็บไม่ครบ ได้ตามต้องการครับ

ขอถาม SEARCH("%",E3:N3)+2
+2 เพราะอะไร ยังไม่เข้าใจครับ

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

เช่นถ้าไม่เว้นเคาะวรรค ต้อง บวก 1 หรือ ตามสถานการณ์ ที่โจทย์กำหนด

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

บางครั้ง มี รั่ว-ซึม-หลง คงไม่ว่ากัน

ยินดีที่สอบถามมาครับ

Norkaz

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

555ครับ จากสูตรที่ให้มา ผมลองทำให้ข้อมูล ใน column Z - column AI ตรง หลัง % เป็นเว้น 2 เคาะวรรค
สูตรที่่ให้มาก็สามารถบวกรวมตัวเลขยังถูกต้องเหมือนเดิมครับ ดังนั้น +2 ก็ไม่ต้องบวกเพิ่ม

..

บางครั้งการเขียนสูตร การใช้สูตร ต้องสวดมนต์ พึ่งโชคชะตา ก่อน Submit ให้บังเอิญ ไปตรงล็อก ของผู้ใช้งาน ที่หลากหลายรูปแบบ ทำให้ไม่ลำบาก ในการต้องเขียนเพิ่มแต่อย่างใด

Norkaz

[Totem]

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

เช่นถ้าไม่เว้นเคาะวรรค ต้อง บวก 1 หรือ ตามสถานการณ์ ที่โจทย์กำหนด

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

บางครั้ง มี รั่ว-ซึม-หลง คงไม่ว่ากัน

ยินดีที่สอบถามมาครับ

Norkaz

ที่ บวกไป 2 เพราะหลัง % เว้น 1 เคาะวรรค ต้องการ บวกเพื่อให้ไปเจอตำแหน่งของตัวเลข
การทำแบบนี้ ไม่สามารถทำได้กับ โจทย์ทุกข้อ ขึ้นกับตำแหน่งของตัวเลข ที่อยู่หลัง % หรือ ค่าที่ ไปค้น (SEARCH หรือ FIND)

555ครับ จากสูตรที่ให้มา ผมลองทำให้ข้อมูล ใน column Z - column AI ตรง หลัง % เป็นเว้น 2 เคาะวรรค
สูตรที่่ให้มาก็สามารถบวกรวมตัวเลขยังถูกต้องเหมือนเดิมครับ ดังนั้น +2 ก็ไม่ต้องบวกเพิ่ม

..

บางครั้งการเขียนสูตร การใช้สูตร ต้องสวดมนต์ พึ่งโชคชะตา ก่อน Submit ให้บังเอิญ ไปตรงล็อก ของผู้ใช้งาน ที่หลากหลายรูปแบบ ทำให้ไม่ลำบาก ในการต้องเขียนเพิ่มแต่อย่างใด

Norkaz

ครับ

[Totem]

ขอเพิ่มเติมครับ


เพิ่มเติม จำนวนปีใน column E ถึง N เรียงจาก ปีที่ 10 ไป ถึง ปีที่ 1 เรียงปี มาก ไป น้อย
แต่ข้อมูลใน column Z ถึง AI เรียงจาก ปีที่ 1 ไป ถึง ปีที่ 10 เรียงปี น้อย ไป มาก

ต้องการให้ จำนวนปีใน column E ถึง N เรียงจาก ปีที่ 10 ไป ถึง ปีที่ 1 เรียงปี มากไปน้อย

salary_Ans3.xlsx

[norkaz]

..

E3

=IFERROR(INDEX($Z$3:$AI$100,LOOKUP(99,ROW($A$3:$A$21)/($A3&$B3&$C3&$D3=$V$3:$V$15&$W$3:$W$15&$X$3:$X$15&$Y$3:$Y$15))-ROW($A$2),--MID(E$2,SEARCH(" ",E$2)+1,2)),"")

Norkaz

[Totem]

..

E3

=IFERROR(INDEX($Z$3:$AI$100,LOOKUP(99,ROW($A$3:$A$21)/($A3&$B3&$C3&$D3=$V$3:$V$15&$W$3:$W$15&$X$3:$X$15&$Y$3:$Y$15))-ROW($A$2),--MID(E$2,SEARCH(" ",E$2)+1,2)),"")

Norkaz

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

[Bo_ry]

E3
=IFERROR(INDEX(Z3:AI13,MATCH(D3:D9,Y3:Y13,),RIGHT(E2:N2,2)),"")

[Totem]

E3
=IFERROR(INDEX(Z3:AI13,MATCH(D3:D9,Y3:Y13,),RIGHT(E2:N2,2)),"")

เป็นสูตรที่กระชับและนำไปประยุกต์เพิ่มเติมได้ดีมากครับ ได้ตามต้องการครับ ขอบคุณมากครับ คุณ Bo_ry