Select Page

ใช้ Excel นับคำที่กำหนด ว่า Lisa ร้องคำว่า Lalisa ในเพลงกี่รอบ?

คลิปนี้ผมทำพิเศษมาฉลองความฮิตถล่มทลายของเพลง Lalisa ของ Lisa BLACKPINK โดยเฉพาะเลยครับ โดยผมจะสอนวิธีใช้ Excel ช่วยนับว่าในเนื้อเพลง Lalisa มีคำว่า “Lalisa กี่คำ?” ถ้าอยากรู้มาดูกันเลย!

ใครอยากดู MV เพลงไปดูได้ที่นี่ https://youtu.be/awkkyBH2zEo และนี่คือส่วนของเนื้อเพลงจากคลิปนั้นครับ (จะได้ copy ไปลองได้ง่ายๆ จาก description นี้เลย)

หลักการในคลิป

  • ในคลิปผมใช้วิธีนับว่าเพลงเต็มๆ มีกี่ตัวอักษรด้วย LEN เช่น
    =LEN(A1)
  • จากนั้นใช้ SUBSTITUTE ตัดคำ Lalisa ที่ต้องการจะนับออก
    =SUBSTITUTE(A1,”Lalisa”,””)
  • แล้วค่อยดูว่าหายไปกี่ตัวอักษร แล้วเอาไปหารด้วย 6 (จำนวนตัวอักษรของ Lalisa) เพื่อนับว่าหายไปกี่คำ
=(LEN(A1)-SUBSTITUTE(A1,"Lalisa",""))/LEN("Lalisa")

วิธีการอื่นที่น่าสนใจ

ทำคล้ายๆ กัน แต่แทนที่จะจัดคำว่า Lalisa ทิ้งตอนทำ SUBSTITUTE ให้แทนด้วยคำว่าที่จำนวนตัวอักษรต่างกัน 1 ตัว เช่น Lalisa1, Lalisa., Manobal หรืออะไรก็ได้ แล้วค่อยเอามาลบกัน เช่น

=SUBSTITUTE(A1,"Lalisa","Lalisa1")-LEN(A1)

เนื้อเพลง LISA – LALISA

내 뒷모습만 봐도 알잖아

어두워질 때 분홍빛이나

새하얀 조명이 날 깨우면

번쩍번쩍 세상을 흔들어

머리를 하얗게 비운채

시끄런 감정은 태울래

Burn burn burn

그때 난 네 갈증을 해소할 샴페인

Sip sip 나를 들이켜

그래 더 저 높이 내 body 받들어

Want you to ring the alarm

세상에게 알려 내 이름에다 입맞춰

Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

Just Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

Baby get the megaphone

Put it on speaker

I said I can’t hear you

So you need to speak up

Put that shiz on stereo

Everyone else on very low

Protect it like a barrier

Promise there’s nothing scarier

Than me if anybody coming gunnin for my man

Gonna catch a case

Gun up in my hand

Bam bam bam

Hit after hit though

Rocks in my wrist so I call em the flintstones

Ring the alarm

세상에게 알려 내 이름에다 입맞춰

Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

Just Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

누구와도 비교 못해 그래봤자 you gon’ still love me

You need some L.A.L.I.S.A

지금 너의 두 눈 앞에 서있는 내 이름을 기억해

Love you some L.A.L.I.S.A

You cannot see me 어떻게 하는지 다 보여줘도

태국에서 한국 거쳐 여기까지 went for the throat

Being the greatest of all time ain’t fantasy

새까만 핑크빛 왕관 belongs to we

Lalisa Lalisa Lalisa 하늘 위 당당해

Lalisa Lalisa Lalisa 저들은 날 원해

Lalisa Lalisa Lalisa catch me if you can

Lalisa Lalisa Lalisa Lalisa Lalisa

Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

Just Say Lalisa love me Lalisa love me

Call me Lalisa love me Lalisa love me

알잖아 attitude

뭘 더 어쩌라구

The loudest in the room

สูตรแยกคํานําหน้าชื่อ Excel แม้เขียนติดกันก็ใช้ได้

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

แต่ไม่ต้องห่วง คลิปนี้จะสอนคุณเขียนสูตรเพื่อแก้ปัญหาดังกล่าวเอง มาดูกันเลยว่าทำยังไง

สร้าง Dropdown List หลายชั้น เพื่อกรอกข้อมูลจังหวัด อำเภอ ตำบล รหัสไปรษณีย์

ในคลิปนี้จะสอนการสร้าง Dropdown List หลายชั้น เพื่อกรอกข้อมูลจังหวัด อำเภอ ตำบล รหัสไปรษณีย์ ด้วย Excel 365 ครับ

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

ซึ่งเพื่อนๆ จะได้เรียนรู้ฟังก์ชันที่เป็น Dynamic Array ของ Excel 365 มากมาย เช่น UNIQUE, SORT, FILTER, TRANSPOSE การอ้างอิงด้วย # เป็นต้น

ดูเสร็จ ให้ดูคลิปนี้ต่อนะครับ มีแก้ไขส่วนที่คลิปบนมีความผิดพลาดเล็กน้อย เพราะผมลืมนึกไปว่า ในประเทศไทย ชื่ออำเภอ/ตำบล อาจจะซ้ำกันก็ได้ ทำให้เลขรหัสไปรณีย์ที่แสดงขึ้นมามันเยอะเกินความจริงไป ในคลิปนี้ผมจึงขอมาแสดงแนวทางแก้ไขสูตรให้ใช้ได้สมบูรณ์มากขึ้นครับ

สรุปแล้วสูตรที่ใช้ทั้งหมด มีดังนี้

ชื่อจังหวัด :
=SORT(UNIQUE(PostCodeTable[ProvinceThai]))

ชื่ออำเภอ : =TRANSPOSE(SORT(UNIQUE(FILTER(PostCodeTable[DistrictThaiShort],PostCodeTable[ProvinceThai]=H5,”กรุณาเลือกจังหวัด”)))) ชื่อตำบล : =TRANSPOSE(SORT(UNIQUE(FILTER(PostCodeTable[TambonThaiShort],(PostCodeTable[DistrictThaiShort]=Sheet1!I5)*(PostCodeTable[ProvinceThai]=Sheet1!H5),”กรุณาเลือกอำเภอ”))))

รหัสไปรษณีย์ :
=TRANSPOSE(SORT(UNIQUE(FILTER(PostCodeTable[PostCode],(PostCodeTable[TambonThaiShort]=Sheet1!J5)*(PostCodeTable[DistrictThaiShort]=Sheet1!I5)*(PostCodeTable[ProvinceThai]=Sheet1!H5),”กรุณาเลือกตำบล”))))

โหลดไฟล์ประกอบได้ที่ : https://github.com/ThepExcel/YouTube/blob/main/address-postal.xlsx

วิธีรวมข้อมูลใน Power Query กรณีหัวตารางชื่อไม่ตรงกัน

ปกติแล้ว เวลารวมข้อมูลหลายๆ ชีท หรือหลายๆ ไฟล์เข้าด้วยกัน เครื่องมือ Power Query จะใช้หลักการยึดจากชื่อหัวตารางเป็นหลัก ซึ่งถ้าหัวตารางเหมือนกันก็จะเอาอีกตารางมามาต่อแถวในคอลัมน์นั้นๆ ได้อย่างง่ายดาย (โดยไม่ได้คำนึงถึงลำดับการเรียงคอลัมน์แต่อย่างใด) แต่ถ้าหากเราอยู่ในสถานการณ์ที่ชื่อคอลัมน์ดันไม่เหมือนกัน แต่มีการเรียงเหมือนกันแทนล่ะ?

เช่น คนส่งข้อมูลให้เราดันมีการเปลี่ยนชื่อคอลัมน์ในไฟล์ต้นฉบับกลางคันด้วยเหตุผลบางอย่าง (แต่ลำดับการเรียงคอลัมน์ยังคงเหมือนเดิม) แบบนี้เราจะทำยังไงดี?? เช่น ข้อมูลอุบัติเหตุบนทางพิเศษในนี้ https://data.go.th/dataset/vachara

คลิปนี้มีคำตอบครับ รับรองว่าได้ความรู้เพียบเช่นเคย !

Matrix คืออะไร? (กันแน่) : ตอนที่ 2

Matrix คืออะไร? (กันแน่) : ตอนที่ 2

หลังจากในตอนที่แล้วเราได้เรียนรู้ไปแล้วว่า Matrix คือการสั่ง Transform vector แบบนึง ในตอนนี้เราจะมาเรียนรู้ต่อว่า เรื่องอื่นๆ เช่น การเปลี่ยนมิติให้ Vector, DET, Rank หรือ INVERSE คืออะไรกันแน่? ถ้าใครที่ยังไม่ได้อ่านตอนแรก แนะนำให้กลับไปอ่านก่อนนะครับ

การ Transform เปลี่ยนมิติ ด้วย Matrix ที่ไม่ใช่จตุรัส

ก่อนหน้านี้เรามีการ Transform vector ไปยัง vector ผลลัพธ์ที่มีมิติเท่ากันมาโดยตลอด แล้วถ้าเราต้องการ Transform แบบไม่เท่ากัน เช่น จาก vector 2 มิติ เป็น 3 มิติล่ะ?

เปลี่ยน จาก 2 มิติ เป็น 3 มิติ

เราสามารถใช้ Matrix ลักษณะ 3×2 เพื่อช่วยแปลงได้ โดยการตีความจะเหมือนเดิมเลยว่า

  • ฝั่งซ้ายคือจะเปลี่ยน i (1,0) ไปที่ไหน
  • ฝั่งขวาคือจะเปลี่ยน j (0,1) ไปไหน
Matrix คืออะไร? (กันแน่) : ตอนที่ 2 1
Matrix คืออะไร? (กันแน่) : ตอนที่ 2 2

เปลี่ยนจาก 3 มิติ เป็น 2 มิติ

เราใช้ Matrix 2×3 มาช่วยได้ โดยแต่ละตัวจะบอกว่าให้ i j k ย้ายไปที่ไหนแทน ในแกน 2 มิติ xy

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 3
Matrix คืออะไร? (กันแน่) : ตอนที่ 2 4

จากตัวอย่างทั้งสองอันแปลว่าเราสามารถเปลี่ยนมิติให้กับ vector ได้ด้วย Matrix เช่นกันครับ

Determinant (DET) คืออะไร?

สมมติว่าเราจะ Transform ให้แกน x ขยายไป 2 เท่า และแกน y ขยายไป3 เท่า เราสามารถใช้ Matrix นี้ในการ Transform ได้

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 5

และถ้าเราถามว่าหลังจากการ Transform ไปแล้วขนาดของผลลัพธ์ใหญ่เป็นกี่เท่าของต้นฉบับ?

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 6

จะเห็นว่าเดิมมันเป็นพื้นที่ 1×1 หน่วย ได้ถูกขยายให้เป็น 2×3 หน่วย นั่นคือขนาดใหญ่เป็น 6 เท่าจากเดิมนั่นเอง (ถ้าเป็น 3 มิติก็วัดว่าปริมาตรอันใหม่เป็นกี่เท่าของปริมาตรเดิม) ซึ่ง เลข 6 ก็คือ Determinant หรือ DET ของ Matrix นั้นๆ นั่นเองครับ

  • โดยที่ถ้า DET เป็น 0 จะหมายถึง แกนใหม่มันถูกบีบให้กลายเป็นเส้นตรงเดียวกัน หรือกลายเป็นจุด ทำให้ไม่มีพื้นที่นั่นเอง
  • และถ้า DET ติดลบ ก็จะแปลว่ามีการพลิกด้านจากแกนเดิมด้วย

ซึ่งใน Excel เราสามารถใช้ MDETERM เพื่อคำนวณหา DET ได้เลย ดังนี้

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 7

Rank ของ Matrix คืออะไร?

Rank คือจำนวนมิติของผลลัพธ์ในการ Transform ด้วย Matrix เช่น

  • ถ้าเรา Transform ด้วย Matrix 2×2 ผลลัพธ์ที่ได้อาจจะมีมิติ (Rank) คือ 2 (ระนาบ) ,1 (เส้น) ,0 (จุด)
    • ซึ่งถ้า DET ไม่ใช่ 0 ก็จะมี Rank 2 นั่นเอง (สูงสุดเท่าที่จะเป็นได้)
  • ถ้าเรา Transform ด้วย Matrix 3×3 ผลลัพธ์ที่ได้อาจจะมีมิติ (Rank) คือ 3 (3 มิติ) , 2 (ระนาบ) ,1 (เส้น) ,0 (จุด)
    • ซึ่งถ้า DET ไม่ใช่ 0 ก็จะมี Rank 3 นั่นเอง (สูงสุดเท่าที่จะเป็นได้)

Inverse Matrix คืออะไร?

ถ้า Matrix ปกติ (A) คือตัวที่สามารถ Transform vector จากแกน i j ปกติให้เปลี่ยนเป็น ij แกนใหม่ได้ ….

ถ้างั้น Inverse ของ Matrix A ( ซึ่งจะเรียกว่า A-1) ก็คือ Matrix ที่สามารถ Transform vector ที่ถูกแปลงมาจาก A ให้กลับให้ไปอยู่ในแกน i j ปกตินั่นเองครับ (ทำงานกลับกัน)

เช่น ใน Excel สามารถใช้ MINVERSE ช่วยได้แบบนี้ครับ

ลองดูตัวอย่างง่ายๆ ก่อน นั่นคือ Matrix เดิมมีการหมุนทวนเข็ม 90 องศา ดังนั้น Matrix Inverse ของตัวนั้นก็คือการหมุนตามเข็ม 90 องศานั่นเองครับ

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 8

หรือลองใส่การ Transform มั่วๆ แบบนี้ มันก็หา Inverse ได้ (ถ้า DET ไม่ใช่ 0)

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 9
จะเห็นว่าเราสามารถใช้ MINVERSE สร้าง Matrix ที่จะ Transform Vector กลับไปยังแกนเดิมได้นั่นเอง

อย่างไรก็ตาม การ Transform บางกรณี ก็จะไม่สามารถหา Inverse ได้ เนื่องจากแกนใหม่อาจถูกบีบจนเป็นเส้นตรง หรือเป็นจุด ซึ่งสังเกตได้ว่า DET เป็น 0 ก็จะหา Inverse ไม่ได้ไปด้วยนั่นเอง

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 10
ในตัวอย่างนี้ i ถูกเปลี่ยนให้ไปที่ (1,3) j ไปที่ (2,6) ซึ่งเป็นเส้นเดียวกับ (1,3) แค่ขนาดไม่เท่ากัน vector ผลลัพธ์ก็จะอยู่ในเส้นตรงนี้ด้วย ด้วยแกนที่เป็นเส้นเดียวกันแบบนี้ (DET=0) ทำให้พอจะย้ายแกนกลับไปที่ i j ปกติ จึงทำไม่ได้นั่นเอง

ซึ่งเจ้า Inverse นี่เองที่ช่วยให้เราแก้สมการหลายตัวแปรด้วย Matrix ได้ ด้วยหลักการที่ว่า

A-1*A = A*A-1 = Identity Matrix (I)

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 11
A-1*A = A*A-1 = Identity Matrix (I)

และ Identity Matrix คูณอะไรก็ได้ตัวเดิม (เพราะเราไม่ได้ย้าย ij ไปไหน)

I*A = A*I = A

Matrix คืออะไร? (กันแน่) : ตอนที่ 2 12
I*A = A*I = A

จากทั้งสองเรื่องข้างบนจะได้ว่า

  • A * x = b
  • A-1* A * x = A-1*b
  • x = A-1*b

x = A-1*b

นั่นคือเราสามารถแก้สมการได้ด้วยการเอา Inverse Matrix ไปคูณกับ Vector ผลลัพธ์ของสมการนั่นเอง ซึ่งผมได้ลองทำให้ดูแล้วในบทความข้างล่างนี้

อย่างไรก็ตามการแก้สมการด้วยวิธีนี้จะมีปัญหากรณีที่ค่าผลลัพธ์สมการเป็น 0 หรือ อยู่ในรูปแบบ Ax=0 ซึ่งเวลาเอา Inverse ไปคูณกับ (0,0,0) ซึ่งเป็นจุด มันก็ได้จุด 0 อยู่ดี ไม่มีประโยชน์อะไร ดังนั้นเราจะต้องแก้สมการด้วยวิธีอื่นครับ ซึ่งต้องรอตอนต่อๆ ไปครับ

ตอนต่อไป

สำหรับตอนนี้จะขอจบเพียงเท่านี้ เดี๋ยวตอนต่อๆ ไปจะเรียนเรื่อง Dot Product, Cross Product, กฏ Cramer, Eigenvectors และ Eigenvalues ครับ