10 สูตรในการแก้ไขข้อมูลผลิตภัณฑ์จำนวนมากใน Excel

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

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

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

เอาล่ะ มาเริ่มกันเลย

สูตรที่ 1: ลบเลขศูนย์นำหน้าสำหรับชื่อผลิตภัณฑ์และคำอธิบาย

หากคุณใช้เลขศูนย์นำหน้าสำหรับหมายเลขผลิตภัณฑ์ใน excel คุณต้องลบออก ขออภัย Excel ไม่มีวิธีง่ายๆ ในการลบเลขศูนย์นำหน้าออกจากชื่อและคำอธิบายของผลิตภัณฑ์

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

การลบการจัดรูปแบบที่มีอยู่ออกจากเซลล์เป็นเพียงการกำจัดศูนย์นำหน้า

ขั้นตอนแรก: เลือกเซลล์ที่มีจำนวนเต็มที่เริ่มต้นด้วยศูนย์

ขั้นตอนที่สอง: คลิกแท็บ 'บ้าน'

ขั้นตอนที่สาม: คลิกดรอปดาวน์รูปแบบตัวเลขในกลุ่มตัวเลข

ขั้นตอนที่สี่: คลิก 'ทั่วไป'

สูตรที่ 2: คำนวณราคาต่อหน่วย (ราคารวม/ปริมาณ) และต้นทุนต่อหน่วย (ต้นทุนรวม/ปริมาณ)

คุณกำลังดำเนินธุรกิจ คุณมีผลิตภัณฑ์ที่มีราคา ขนาด และปริมาณต่างกัน คุณต้องการคำนวณต้นทุนรวมของผลิตภัณฑ์ของคุณภายใต้สถานการณ์นี้

Excel มีตัวเลือกในการคำนวณต้นทุนต่อหน่วยและราคาต่อหน่วยสำหรับแต่ละผลิตภัณฑ์ ราคาต่อหน่วยคือต้นทุนรวมต่อหน่วยวัด ซึ่งสามารถคำนวณได้โดยการหารต้นทุนทั้งหมดด้วยปริมาณของผลิตภัณฑ์นั้น นี้จะคำนวณราคาต่อหน่วย ต้นทุนต่อหน่วยคือราคาต่อหน่วยคูณด้วยปริมาณของผลิตภัณฑ์นั้น

สูตรต้นทุนต่อหน่วย = ต้นทุนรวม / หน่วยที่ผลิตหรือซื้อทั้งหมด

สูตรราคาต่อหน่วย = ราคารวม/ จำนวนหน่วยที่ผลิต

ดูวิธีตั้งค่าราคาสำหรับผู้ค้าปลีกและผู้ ขาย

สูตร 3: แบ่งค่าเซลล์ด้วยเครื่องหมายจุลภาค ทวิภาค สแลช ขีดกลาง หรือตัวคั่นอื่นๆ

เมื่อคุณพยายามทำงานกับข้อมูลใน Excel ข้อมูลของคุณมักจะถูกจัดเก็บไว้ในคอลัมน์เดียว แต่คุณต้องการทำให้พร้อมใช้งานในหลายคอลัมน์ ตัวอย่างเช่น ฉันมีรายชื่อลูกค้าที่เก็บไว้ในคอลัมน์ A แต่ฉันต้องการหมายเลขโทรศัพท์ของพวกเขาในคอลัมน์ B และ C

หรือสมมติว่า ฉันมีชุดคำและตัวเลขในเวิร์กชีต Excel ที่ฉันต้องการแบ่งออกเป็นสองคอลัมน์ เป้าหมายคือให้ครึ่งแรกของสตริงคั่นด้วยเส้นประแล้วแยกออกเป็นเซลล์แยกกัน

ตัวอย่างเช่น หากคุณมีสตริงของคำและตัวเลขต่อไปนี้: Shoes-123 คุณจะจบลงด้วยสองคอลัมน์: 'รองเท้า' และ '123'

ไวยากรณ์: =SPLIT(A2,”-”)

อย่างไรก็ตาม ปัญหาเกิดขึ้นเมื่อสตริงมีขีดคั่นหลายอัน เช่น กบยาวสีแดง ในกรณีนี้ การใช้ฟังก์ชันแยกอย่างง่ายจะไม่ทำงาน แต่คุณจะต้องใช้ฟังก์ชันแยกขวา ซ้าย หรือกลาง ตามรายการด้านล่าง:

=ซ้าย(B1,2)

หรือ

=ขวา(B2,4)

หรือ
=กลาง(B2,5,2)

สูตรที่ 4: การใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาค่า

เคยเกิดขึ้นกับคุณไหมว่าคุณต้องการค้นหาแอตทริบิวต์ทั้งหมดที่มีแอตทริบิวต์เดียว ฉันต้องการที่อยู่อีเมล ที่อยู่บ้าน อายุ และเพศของใครบางคน ฉันสามารถทำได้ด้วย VLOOKUP สูตรเดียว ฉันสามารถใช้ฟังก์ชัน VLOOKUP เพื่อดึงหมายเลขโทรศัพท์ของลูกค้าตามชื่อของพวกเขา วิธีนี้เหมาะอย่างยิ่งเมื่อคุณมีข้อมูลจำนวนมากที่คุณต้องเข้าถึงอย่างรวดเร็ว

VLOOKUP เป็นตัวย่อสำหรับ 'Vertical Lookup' เป็นฟังก์ชันที่บอกให้ Excel ค้นหาค่าเฉพาะในคอลัมน์ (เรียกว่าอาร์เรย์ของตาราง) เพื่อคืนค่าจากคอลัมน์อื่นในแถวเดียวกัน

ฟังก์ชัน VLOOKUP ประกอบด้วยสี่ส่วน:

• ความคุ้มค่าที่คุณต้องการ
• ค่าที่คุณกำลังมองหาและช่วงที่คุณกำลังมองหา;
• จำนวนของคอลัมน์ที่มีค่าส่งคืนภายในช่วงที่คุณเลือก
• สำหรับค่าที่ตรงกันทุกประการกับค่าที่คุณต้องการ ให้ใช้ 0 หรือ FALSE สำหรับการจับคู่ที่ใกล้เคียง ให้ใช้ 1 หรือ TRUE

ไวยากรณ์: VLOOKUP([value], [range], [column number], [false or true])

สูตร 5: การใช้สูตรเพื่อกรองค่าว่าง

งานที่ต้องใช้เวลามากที่สุดงานหนึ่งคือการล้างข้อมูลผลิตภัณฑ์ในร้านค้าอีคอมเมิร์ซของคุณ บางครั้งหากไม่ทำอย่างถูกต้องก็อาจทำให้สูญเสียยอดขายได้

ในส่วนนี้ คุณจะได้เรียนรู้วิธีใช้สูตรเพื่อกรองค่าว่างในข้อมูลผลิตภัณฑ์จำนวนมาก

คอลัมน์ "สูตร" จะคำนวณสูตรง่ายๆ ที่ตรวจสอบการมีอยู่ของค่า หากมีค่าอยู่ก็จะส่งคืน ถ้าไม่เช่นนั้นจะส่งคืนค่าว่าง ด้วยวิธีนี้ คุณสามารถใช้คอลัมน์สูตรเป็นตัวกรอง โดยที่ค่าว่างใดๆ จะถูกแปลงเป็นศูนย์

คอลัมน์ "สูตร" จะคำนวณสูตรง่ายๆ ที่ตรวจสอบการมีอยู่ของค่า หากมีค่าอยู่ก็จะส่งคืน ถ้าไม่เช่นนั้น จะส่งกลับค่าศูนย์

ไวยากรณ์: =FILTER(data,(rng1<>””)*(rng2<>””)*(rng3<>””))

สูตร 6: การใช้สูตรเพื่อเลือกเฉพาะแถวที่สนใจ

การเลือกเฉพาะแถวที่สนใจใน excel สำหรับข้อมูลผลิตภัณฑ์จำนวนมากเป็นหนึ่งในงาน Excel ที่พบบ่อยที่สุด ฉันจะแสดงวิธีใช้สูตรเพื่อเลือกเฉพาะแถวที่สนใจใน excel

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

เริ่มต้นด้วยการวาดเซลล์รอบๆ ผลิตภัณฑ์ทั้งหมดของคุณโดยใช้เมาส์หรือ Ctrl + Shift + แป้นลูกศรลง

คุณสามารถใช้ฟังก์ชัน IF ได้

=IF(และ(A2=”คะแนน”,A2>=90),1””)

– โดยที่ A2 เป็นคอลัมน์ที่มีรายการคะแนนและ =AND(A2=”คะแนน”,A2>=90) – ตรวจสอบค่าของเซลล์และคืนค่า เป็น จริง หากรูปแบบ 'คะแนน' ปรากฏในเซลล์ และถ้า ค่าเซลล์มากกว่า 90

สูตร 7: การใช้สูตรเพื่อเปลี่ยนวันที่เป็นข้อความหรือตัวเลข

หากคุณกำลังใช้ Excel สำหรับงานประเภทใดก็ตามที่เกี่ยวข้องกับการป้อนข้อมูลจำนวนมาก คุณจำเป็นต้องรู้วิธีใช้สูตรเพื่อเปลี่ยนวันที่เป็นข้อความหรือตัวเลข สถานการณ์ทั่วไปที่คุณต้องใช้คือเมื่อนำเข้าข้อมูลผลิตภัณฑ์จากไฟล์ CSV

ตัวอย่างเช่น เมื่อคุณนำเข้าข้อมูลผลิตภัณฑ์ มักจะมีบางเซลล์ที่มีวันที่ สิ่งที่จะเกิดขึ้นคือ Excel จะให้ข้อผิดพลาดกับคุณโดยพูดว่า "ไม่สามารถแปลงค่าประเภทนี้ได้" เพื่อหลีกเลี่ยงปัญหานี้ เพียงใช้สูตรใดสูตรหนึ่งต่อไปนี้เพื่อเปลี่ยนวันที่เป็นข้อความหรือตัวเลข เพื่อที่ Excel จะไม่มีปัญหาในการนำเข้าไฟล์ CSV เหล่านั้น:

ไวยากรณ์: =VALUE(ข้อความ)

ไวยากรณ์: =DATEVALUE(date_text)

สูตร 8: การใช้สูตรเพื่อค้นหารายการที่ซ้ำกันในข้อมูลผลิตภัณฑ์จำนวนมาก

ไม่ใช่เรื่องแปลกที่ข้อมูลทางธุรกิจจะมีบันทึกที่ซ้ำกัน ซึ่งอาจเป็นปัญหาในการรายงาน หากต้องการตรวจสอบว่ามีระเบียนที่ซ้ำกันในแผ่นงาน Excel ของคุณหรือไม่ คุณสามารถใช้เครื่องมือสูตร คุณยังสามารถเรียนรู้วิธีลบรายการที่ซ้ำกันโดยใช้ฟังก์ชันตัวกรองใน Microsoft excel

อย่างไรก็ตาม การค้นหารายการซ้ำในข้อมูลผลิตภัณฑ์จำนวนมากโดยใช้สูตรจะง่ายกว่าเสมอ คุณสามารถใช้สูตร COUNTIF ในสเปรดชีต excel เพื่อรับการตอบสนอง 'TRUE' หรือ 'FALSE' เพื่อตรวจหาค่าที่ซ้ำกัน โดยที่:

จริง = ค่าที่ซ้ำกัน

เท็จ = ค่าที่ไม่ซ้ำ

เพียงเปลี่ยน 'A' ในสูตรให้เป็นตัวอักษรของคอลัมน์เซลล์ที่คุณต้องการหาค่าที่ซ้ำกัน

ไวยากรณ์: =COUNTIF(A:A,A1)>1

สูตรที่ 9: การใช้สูตรนับจำนวนสินค้าที่มีคุณสมบัติบางอย่าง

ฉันต้องการรวบรวมและแสดงจำนวนผลิตภัณฑ์ที่มีคุณสมบัติบางอย่างของผลิตภัณฑ์ ตัวอย่างเช่น ฉันต้องการแอตทริบิวต์หนึ่งรายการ "มีส่วนลด" และแสดงจำนวนผลิตภัณฑ์ที่มีแอตทริบิวต์นี้

สูตร Excel นี้ช่วยให้คุณคำนวณจำนวนผลิตภัณฑ์ทั้งหมดที่ตรงกับคุณลักษณะบางอย่าง

ในการนับจำนวนผลิตภัณฑ์ที่ตรงกับแอตทริบิวต์บางอย่าง คุณสามารถใช้สูตรต่อไปนี้ใน Excel

ตัวอย่างเช่น สมมติว่าคุณต้องการทราบจำนวนรวมของผลิตภัณฑ์ที่เป็นสีน้ำเงินด้วยราคาระหว่าง $10 ถึง $20 ในกรณีนี้ ตารางของคุณจะมีลักษณะดังนี้:

เลือกคอลัมน์ 'สี' และใช้

=COUNTIF(B2:B8”สีน้ำเงิน”,C2:C8”)

สูตร 10: การใช้สูตรเพื่อแปลงชื่อผลิตภัณฑ์เป็น URL ของผลิตภัณฑ์

คุณมีรายการผลิตภัณฑ์ใน excel ตอนนี้คุณต้องการทำให้ชื่อผลิตภัณฑ์แต่ละรายการเป็น URL ของผลิตภัณฑ์ เป็นไปได้ไหม? ใช่แล้ว! สิ่งนี้เรียกว่าการอ้างอิง/การอ้างอิงเซลล์แบบไดนามิก ซึ่งสามารถทำได้โดยใช้ฟังก์ชัน INDEX และ MATCH

ใน Excel คุณสามารถใช้สูตรง่ายๆ เพื่อสร้าง URL จากชื่อผลิตภัณฑ์ โดยปกติ แนวทางนี้จะมีประโยชน์เมื่อคุณต้องการสร้างลิงก์ไปยังผลิตภัณฑ์โดยใช้ชื่อผลิตภัณฑ์เป็นข้อความลิงก์ และคุณไม่จำเป็นต้องติดตามลิงก์ใดๆ ที่สร้างขึ้นด้วยตนเอง

นี่เป็นเพียงกระบวนการสองขั้นตอน ซึ่งต้องใช้สองสูตร:

ขั้นตอนที่ 1: ลบตัวพิมพ์ใหญ่ทั้งหมดออกจากชื่อและแทนที่ช่องว่างทั้งหมดด้วย "-"

ไวยากรณ์: =LOWER(SUBSTITUTE(B2,” “,”-“))

ขั้นตอนที่ 2: ลบอักขระพิเศษทั้งหมดออกจากชื่อผลิตภัณฑ์

ไวยากรณ์: =REGEXREPLACE(B2,”[^A-Za-z1-9-]+”,””)

ใช้ PIM เพื่อแก้ไขข้อมูลผลิตภัณฑ์จำนวนมากได้อย่างง่ายดาย

คุณเคยมีสถานการณ์ต่อไปนี้หรือไม่?

1. คุณมีข้อมูลผลิตภัณฑ์จำนวนมากในแผ่นงาน Excel

2. แต่ข้อมูลของคุณยังไม่สมบูรณ์และจำเป็นต้องอัปเดตด้วยข้อมูลจากแหล่งอื่น

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

4. เมื่อคุณอัปเดตข้อมูลผลิตภัณฑ์ คุณต้องอัปเดตไฟล์ Excel โดยใช้โค้ด VBA แต่จะทำอย่างรวดเร็วได้ยาก

ใน Shopify เมื่อคุณเพิ่มสินค้าจากสเปรดชีต Excel เช่น จากไฟล์ CSV ข้อมูลสินค้าที่คุณเพิ่มด้วยตนเองจะหายไปในการแปลหากไม่ได้อยู่ในรูปแบบที่ถูกต้อง ตัวอย่างเช่น หากคุณเพิ่มผลิตภัณฑ์และใช้ชื่อฟิลด์หรือโครงสร้างที่ไม่ถูกต้อง ข้อมูลผลิตภัณฑ์ของคุณจะสูญหายและคุณจะต้องเริ่มต้นใหม่อีกครั้ง

ถ้าฉันบอกคุณว่าคุณสามารถกำจัดปัญหาที่น่ารำคาญและคุกคามนี้ได้ทันทีและสำหรับทั้งหมด? ถ้าฉันบอกคุณว่าคุณจะไม่ต้องเผชิญกับสถานการณ์ที่ระบุไว้ข้างต้นล่ะ ก็มีวิธี

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

ในความเป็นจริง มีเหตุผลมากมายที่จะทิ้งสเปรดชีตสำหรับ PIM โชคดีที่เราได้ครอบคลุมไปแล้ว โปรดอ่านเพิ่มเติมเกี่ยวกับเรื่องนี้ได้ที่นี่

ชำระเงินบล็อกของเราเกี่ยวกับ 7 เหตุผลในการออกจากสเปรดชีตให้กับ PIM

แล้วทำไมถึงไม่มีล่ะ? ท้ายที่สุด PIM ก็มีชัยในการแทนที่สเปรดชีต มีไว้เพื่อให้การจัดการข้อมูลทั้งหมดง่ายขึ้น

บทสรุป

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

มันไม่ได้ทำให้แตกต่างสิ่งที่เป็นกรณี เมื่อคลื่นเริ่มสั่นคลอน PIM อาจเป็นเครื่องมือที่น่าเชื่อถือและมีประโยชน์มากในการช่วยให้คุณควบคุมได้ ใช้ประโยชน์สูงสุดจากพลังอำนาจอันทรงพลังของการผสานรวม PIM-Shopify

ฉันหวังว่าโพสต์นี้จะเป็นประโยชน์ในการแสดงวิธีแก้ไขข้อมูลจำนวนมากใน excel ที่ง่ายและรวดเร็ว หากคุณมีเคล็ดลับของคุณเองโปรดทิ้งไว้ในส่วนความคิดเห็นด้านล่าง