Import Data (ใช้กับ google sheet ได้อย่างเดียว)
มักใช้กับกรณีที่เป็นลิงค์ที่เราโหลดไฟล์ csv มาได้ ให้กด “คลิกขวา → Coppy Link → แปะลิงค์ใน google sheet”
ใช้สูตร =importdata(กดที่เซลล์ที่นำลิงค์มาใส่)
การ Import Data จาก HTML
ตัวอย่าง https://www.w3schools.com/html/html_tables.asp
ต้องการนำไฟล์บริเวณที่ไฮไลท์ ให้ คลิกขวาที่ไฮไลท์(บริเวณตัวหนังสือ)→Inspect(ตรวจสอบ)→เลื่อนไปที่ Table
กลับมาที่ sheets พิมพ์ =importhtml(B2, “table”, 1) *B2 เป็นช่องที่เราเอาลิงค์มาดูที่รูปบนสุด
แปลความคือ จงเข้าไปที่เว็บไซต์จากลิงค์ที่ช่อง B2 ที่เป็น table ต้องการเป็น table ที่ 1
COUNTIFS การสรุปผลข้อมูล
เช่น ต้องการรู้ว่าแบรนด์ขายอะไรไปได้กี่อันบ้าง
ตัวอย่างคือ
หาแบรนด์ AMD ที่อยู่ใน column C
=COUNTIF(C:C, “AMD”) หาแบรนด์ AMD ที่เป็นรุ่น Ryzen 5 =COUNTIFS(C:C, “AMD”, D:D, “Ryzen 5”)
หาแบรนด์ AMD ที่มียอดขายมากกว่า(Quantity) 300
=COUNTIFS(C:C, “AMD”, E:E, “>300”)
หาแบรนด์ AMD ที่ถูกขายก่อนวันที่ 5 มิย. 2021 =COUNTIFS(C:C, “AMD”, B:B, “<2021-06-05”)
SUMIFS หาผลรวมของสินค้าใดสินค้าหนึ่ง
ตัวอย่าง คือ ต้องการหา ผลรวมของ AMD โมเดล Ryzen 5 ตอนใส่สูตรควรใส่คอลัมที่เป็นตัวเลขเป็นอันแรกจากตัวอย่างคือคอลัม E
=SUMIFS(E:E, C:C, “AMD”, D:D, “Ryzen 5”)
Filter เพื่อให้ข้อมูลแสดงให้เห็นแต่สิ่งที่ต้องการ
1.highlight ข้อมูลของเราก่อน หรือ กด Ctrl+A (ไม่เอาชื่อ table ด้านบนนะที่เป็น A,B,C,D,E อะ)
กดปุ่ม Create a filter
ก็จะมี Dropdown ตรงแต่ละหัวข้อขึ้นมา ให้กดตรงหัวข้อที่ต้องการ
แล้วติ๊กอันที่เราไม่ต้องการออก
Filter วันที่
1.กดที่ Dropdown ตรงหัวข้อที่เราตั้ง DATE ก่อน
2.กดตรง Filter by condition
3.กดตรง Dropdown ที่ None
4.ถ้าเลือก Date is before จะเป็นการ Filter วันที่ก่อนวันที่เรากำหนด
5.จากข้อ 4 เราเลือกเป็น ก่อนวันที่เรากำหนด ต่อมาก็ กดที่ exact date จะเป็นการกำหนดวันที่ก่อนที่เราจะ Filter พิมพ์ ปีค.ศ. เดือน วัน
Filter มีสูตรด้วยนะ คืออยากให้ cell เดิมมีเหมือนเดิมแล้วแสดงผล cell ใหม่
1.ให้ coppy หัวข้อมาก่อน
2.จากตัวอย่างคือต้องการตั้งแต่ cell A3 ถึง cell E3 ไปข้างล่าง (การกดไปข้างล่าง กด Ctrl+Shift+ลูกศรลง)
3.เมื่อกดจากข้อ 2 แล้ว ตามตัวอย่างจะลงไปถึงบรรรทัดที่ E22 สูตรก็จะเปลี่ยนเป็น E22
4.แล้วถ้าต้องการเลือกเป็น Intel ก็ให้กดที่ช่อง C3 กด Ctrl+Shift+ลูกศรลง
ก็จะได้เป็นสูตรดังนี้ =FILTER(A3:E22, C3:C22=“Intel”)
5.กรณีที่ต้องการเพิ่มอีกว่าเป็นของ model อะไร สมมติว่าเป็น Core i3 แบบนี้จะหมายถึง “และ” คือต้อง เป็นทั้ง Intel และ Core i3
ก็จะได้เป็นสูตรดังนี้ =FILTER(A3:E22, C3:C22=“Intel”, D3:D22=”Core i3”)
6.กรณีเป็นการทำ FILTER แบบ “หรือ” คือ มีที่เป็น intel ทั้งหมด รวมถึง มีที่เป็น Core i3 ทั้งหมด
ก็จะได้เป็นสูตรดังนี้ =FILTER( (A3:E22, C3:C22=“Intel”)+ (D3:D22=”Core i3”) )
Sort Sheets เรียงน้อยไปมาก หรือ มากไปน้อย
กรณีใช้ข้อมูลเดิมฟิลเตอร์เลย
1.กด highlight ข้อมูล
2.กดที่เครื่องหมายรูป กรวย
3.จะมีให้กด dropdown
กด A→Z(เรียงจากน้อยไปมาก),Z→A(เรียงจากมากไปน้อย)
กรณีไม่ยุ่งกับข้อมูลเดิมเลย
1.ให้ coppy และ past หัวข้อมาก่อน
2.ตัวอย่าง ถ้าพิมพ์ =SORT(A3:E33, 5, TRUE) ความหมาย คือ ข้อมูลในแถว A3 ถึง E33 ต้องการเรียงโดยให้แถวที่ 5 เรียงจากน้อยไปมาก
3.แบบ SORT ซ้อน SORT
ตัวอย่าง ถ้าพิมพ์ =SORT(A3:E33, 3, TRUE, 5, FASE) ความหมายคือ ข้อมูลในแถว A3 ถึง E33 เรียงแถวที่ 3 จากน้อยไปมาก แล้วค่อยเรียงแถวที่ 5 จากมากไปน้อย
Vlookup นิยมใช้มากๆ
แบบปกติ
ตัวอย่าง มี 2 ตารางนะ
โจทย์คือ ต้องการนำ Quantity ไปคูณกับราคา
พิมพ์หัวข้อ model กับ price เพิ่มเข้ามา
ใน Model Name
=vlookup(D3, A26:C31, 2, FALSE) ความหมายคือ ให้ดูว่าในที่เขียนตรง Model แถวที่ D3 โมเดลนั้นตรงกับโมเดลชื่อเต็มว่าอะไรใน ช่อง A26 ถึง C31 แล้วให้แสดงผงแถวที่ 2 ส่วน FALSE คือต้องตรงตามเงื่อนไขเท่านั้นถึงโชว์
กดปุ่ม F4 คือ lock cell ตรง A26:C31
ใน Price
=vlookup(D3, A26:C31, 3, FALSE)
เทคนิคไม่ต้องจำ cell
ให้ตั้งชื่อ NAMERAGE ไปที่ data→namerange→ด้านขวามือเปลี่ยนเป็นชื่อที่ต้องการ ครั้งนี้ในช่วงของ A26:C31 เปลี่ยนเป็น PRODUCT←(แนะนำเป็นตัวพิมพ์ใหญ่ให้หมด)
Vlookup แบบ approximate match โชว์ตัวเลขไม่เปะ เช่น >500 บาท, ช่วง 250-300 บาท
โจทย์คือต้องการเปลี่ยนราคา ตรง C26 ถึง C31 ให้เป็น Range ราคา ตรง Price Band และ ได้ตั้งค่าตรงคอลัมที่ A35:B37 ตั้งชื่อ NAMERAGE ว่า PRICE_BANDS (สำหรับคนที่จำไม่ได้ว่า NAMERAGE ตั้งค่ายังไง ไปที่ data→namerange→ด้านขวามือเปลี่ยนเป็นชื่อที่ต้องการ)
ตรง TRUE คือ approximate match เพราะตรง $200 $300 $550 $150 ไม่ได้มีอยู่ใน Price ตรง Price Band
Index and Match
มักนำไปทำเป็น แอปเล็กๆ เพื่อดึงข้อมูลที่ต้องการ ตามตัวอย่างข้างล่างว่า แค่กด Dropdown ตรง โมเดล(ID)ที่เท่าไหร่ และ กด Dropdown เลือก Column ว่าต้องเป็นข้อมูลอะไร ขายได้จำนวนเท่าไหร่
ตัวอย่าง ข้อมูล
เนี่ย ก็ออกมาเป็นแอปแบบนี้ได้เลย
