คัดลอกแถวระหว่างชีตใน Excel ตามเกณฑ์ – 6 วิธีง่ายๆ

ดาวน์โหลดสมุดงานฝึกฝน

คุณสามารถดาวน์โหลดสมุดงานและฝึกฝนกับมันได้

6 วิธีในการคัดลอกแถวจากชีตหนึ่งไปยังอีกชีตหนึ่งตามเกณฑ์ใน Excel

คุณจะค้นพบ 6 วิธีที่ง่ายมากสำหรับการคัดลอกแถวใน Excel ทุกวิธีมีประโยชน์มาก คุณสามารถเลือกวิธีใดก็ได้เพื่อทำงานเสร็จสิ้น โดยไม่ล่าช้า 让我们一起来逐个检查它们，以便根据标准将行从一张工作表复制到另一张工作表在 Excel 中。

1. ใช้ตัวเลือกตัวกรอง (Filter) ของ Excel เพื่อคัดลอกแถวจากชีตหนึ่งไปยังอีกชีตหนึ่ง

เพื่อสาธิตกระบวนการนี้ ให้พิจารณาชุดข้อมูลของผลไม้บางชนิดพร้อม ราคาต่อหน่วย, น้ำหนัก, และ ราคารวม ตารางเต็มนี้บันทึกอยู่ในชีตชื่อ Filter Op. ในสมุดงานของเรา ตอนนี้ เราจะคัดลอกแถวจาก Filter Op. และบันทึกไว้ในชีต Result1 โดยใช้ตัวเลือก Filtering และ Copying

ขั้นตอน:

• ขั้นแรก เลือกข้อมูล
• ที่สอง ไปที่แท็บ Data จากริบบอน
• ที่สาม คลิกที่ตัวเลือก Filter ภายใต้กลุ่ม Sort & Filter

• เลือกคอลัมน์ที่ต้องการที่คุณจะคัดลอกแถว ที่นี่ เราเลือกคอลัมน์ Fruits
• จากนั้น เลือกแถวที่คุณต้องการคัดลอกจากแผ่นงานนี้ เราเลือกแถว Mango ที่นี่สำหรับตัวอย่าง จะมีการคัดลอกเฉพาะแถวของรายการที่เลือก หากคุณต้องการคัดลอกแถวทั้งหมดของคอลัมน์นี้ คุณอาจเลือกตัวเลือก Select All

• คัดลอก ข้อมูลทั้งหมดโดยการเลือกข้อมูลและกด Ctrl+C

• เพิ่มเติม สร้างแผ่นงานใหม่โดยคลิกที่เครื่องหมาย + (plus) ด้านล่าง หรือใช้ลัดปุ่มแป้นพิมพ์ SHIFT+F11
• วางข้อมูลที่คัดลอกโดยกด Ctrl+V ลงในแผ่นงานใหม่ Result1
• สุดท้าย ข้อมูลที่เลือกทั้งหมดจะถูกคัดลอกจาก Filter Op. ไปยัง Result1

อ่านเพิ่มเติม: Excel VBA: คัดลอกค่าเซลล์และวางในเซลล์อื่น

2. ใช้คุณสมบัติขั้นสูง (Advanced Filter) เพื่อทำซ้ำแถวจากชีตหนึ่งไปยังอีกชีตหนึ่ง

เพื่อแสดงกระบวนการนี้ เราจะพิจารณาตัวอย่างเดียวกันที่แสดงใน วิธี 1 แต่ที่นี่จะใช้ Advanced Filter สำหรับการคัดลอกแถวจาก Sheet3 ไปยัง Sheet4 และเกณฑ์ทดสอบของเราจะเป็นผลไม้ทั้งหมดที่ ราคารวม มากกว่า 150 ดังนั้น เราจะคัดลอกแถวจาก Sheet3 ไปยัง Sheet4 โดยที่ราคารวมมากกว่า 150

ขั้นตอน:

• เริ่มต้นด้วยการไปที่ชีต Result1.1 และเลือก Advanced ภายใต้แท็บ Data

• หลังจากนั้น เลือกตัวเลือก Copy to another location

• จากนั้น เลือกกล่อง List range และไปที่ชีต Advanced และคัดลอกชุดข้อมูลทั้งหมด

• เพิ่มเติม เลือกเซลล์ Criteria range

• ตอนนี้ เลือกตัวเลือก Copy to ซึ่งจะเลื่อนไปยังชีต Result1.1 อัตโนมัติ และเลือกเซลล์ใดๆ ของแผ่นงานนั้น

• ถัดไป กดปุ่ม Ok

• ดังนั้น แถวจะถูกคัดลอกจากชีต Advanced ไปยัง Result1.1 ตามเกณฑ์ที่ระบุ

อ่านเพิ่มเติม: แมโครคัดลอกและวางจากแผ่นงานหนึ่งไปยังอีกแผ่นงานหนึ่ง (15 วิธี)

3. คัดลอกแถวจากชีตหนึ่งไปยังอีกชีตหนึ่งโดยใช้สูตรอาร์เรย์ (Array Formula)

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

เป้าหมายของเราคือการคัดลอกแถวตาม Shop Names ไปยังแผ่นงานใหม่

ขั้นตอน:

• ใน البداية สร้างแผ่นงานใหม่ด้วย Shop Names

• จากนั้น ไปที่แผ่นงานใหม่ใดๆ เช่น Rooted จากนั้นเลือกเซลล์ B4 และใส่สูตรด้านล่าง แล้วกด CTRL+SHIFT+ENTER หากคุณใช้ MS Excel 365 ให้กด Enter เพียงอย่างเดียว

คำอธิบายสูตร

ในสูตรนี้ เราใช้ฟังก์ชัน Excel บางอย่าง ที่นี่จะอภิปรายฟังก์ชันทั้งหมดที่ใช้ในสูตรอาร์เรย์

ROWS(array)

ฟังก์ชัน ROWS รับอาร์เรย์หรือการอ้างอิงถึงช่วงเซลล์และคืนจำนวนแถวในการอ้างอิงหรืออาร์เรย์ตามช่วงที่เราให้

COLUMN([reference])

โดยการส่งการอ้างอิงเซลล์ในพารามิเตอร์ของ ฟังก์ชัน COLUMN เราจะได้หมายเลขคอลัมน์เฉพาะตามการอ้างอิงเซลล์ที่เราให้

SMALL(array, n)

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

INDEX(array, row_num, [column_num])

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

IFERROR(value, value_if_error)

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

• เพิ่มเติม คัดลอกสูตรลงและขวาเพื่อรับแถวที่ตรงตามเงื่อนไขทั้งหมด

• ทำทางเดียวกันสำหรับแผ่นงานอื่นๆ

• คุณสามารถตรวจสอบได้โดยการเปลี่ยนค่าใดๆ ในชีต Array Formula ว่าค่าจะเปลี่ยนโดยอัตโนมัติหรือไม่

• ไปที่แผ่นงาน Rooted และดูการเปลี่ยนแปลง

อ่านเพิ่มเติม: Excel VBA เพื่อคัดลอกแถวไปยังแผ่นงานอื่นตามเกณฑ์

การอ่านที่คล้ายกัน

• วิธีคัดลอกแถวสลับกันใน Excel (4 วิธี)
• [แก้ไขแล้ว]: คลิกขวาคัดลอกและวางไม่ทำงานใน Excel (11 วิธีแก้ไข)
• วิธีคัดลอกแถวใน Excel ด้วยตัวกรอง (6 วิธีเร็ว)
• Excel VBA เพื่อคัดลอกข้อมูลจากสมุดงานอื่นโดยไม่ต้องเปิด
• วิธีคัดลอกหลายเซลล์ไปยังแผ่นงานอื่นใน Excel (9 วิธี)

4. ใช้ฟังก์ชันรวมเพื่อทำซ้ำแถว

มาดูอีกวิธีหนึ่งในการคัดลอกแถวที่แสดงในแผ่นงานอื่นโดยอัตโนมัติ สำหรับวิธีนี้ เราจะพิจารณาชุดข้อมูลเดียวกันที่ใช้ใน วิธี 4 ตอนนี้เราจะเรียงลำดับรายการผลไม้ตาม Shop Names และตรวจสอบว่า Total Price มากกว่า $130 หรือไม่ หากเราเพียงเลือก Shop Name ใดๆ จากรายการแบบเลื่อนลงและกด Enter แล้วแถวที่ตรงตามเงื่อนไขทั้งหมดจะถูกคัดลอกจากชีต Functions และแสดงในชีต Result2

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ G5 ในชีต Functions และใส่สูตรด้านล่าง:

• จากนั้น กดปุ่ม Enter จากแป้นพิมพ์

• ลากไอคอน Fill Handle ลงเพื่อคัดลอกสูตรข้ามช่วง หรือ เพื่อ AutoFill ช่วง ให้ ดับเบิลคลิก ที่เครื่องหมายบวก (+)

• สุดท้าย เราสามารถเห็นผลลัพธ์ได้

คำอธิบายสูตร

ที่นี่ฉันใช้ฟังก์ชัน Excel ที่แตกต่างกัน ฉันได้อภิปรายรายละเอียดทุกฟังก์ชันด้านล่าง:

MAX(number1, [number2], ...)

ฟังก์ชัน MAX รับตัวเลขในพารามิเตอร์และคืนค่าสูงสุดในบรรดานั้น

AND (logical1, [logical2], ...)

ฟังก์ชัน AND เป็นฟังก์ชันตรรกะที่รับเงื่อนไขตรรกะสองขึ้นไปเป็นพารามิเตอร์และคืน True หากเงื่อนไขทั้งหมดเป็นจริง มิฉะนั้น คืน False

IF (logical_condition, [value_if_true], [value_if_false])

นี่คือฟังก์ชันเงื่อนไขอีกตัวหนึ่งคือ ฟังก์ชัน IF ใน Excel ที่รับเงื่อนไขตรรกะและคืนค่าตามเงื่อนไขว่าเป็น True หรือ False

• เพิ่มเติม ไปที่ชีต Result2 และใส่สูตรในเซลล์ B7

• กด Enter

คำอธิบายสูตร

ที่นี่มีเพียงฟังก์ชัน MATCH ที่เป็นใหม่ที่เราใช้มาจนถึงตอนนี้

MATCH(lookup_value, lookup_array, [match_type])

ใน ฟังก์ชัน MATCH ส่วนแรกในพารามิเตอร์ใช้เป็นค่าการค้นหา (lookup value) ซึ่งเป็นค่าที่เราต้องการค้นหา ส่วนถัดไปเก็บช่วงอาร์เรย์ของเซลล์ที่เราจะค้นหาค่าการค้นหา และสุดท้าย match type เป็นค่า -1, 0, 1 ซึ่งกำหนดว่า Excel จับคู่ lookup_value กับค่าใน lookup_array อย่างไร

• เพิ่มเติม คัดลอกอาร์เรย์ลงและขวา
• ตอนนี้ เลือก Shop Name ใดๆ และจะแสดง Shop Name และชื่อ Fruit เมื่อราคารวมของผลไม้ Shop Name ที่ตรงกัน มากกว่า $130

อ่านเพิ่มเติม: Excel VBA: คัดลอกช่วงไปยังสมุดงานอื่น

5. ใช้ฟังก์ชัน FILTER ของ Excel เพื่อคัดลอกแถวจากชีตหนึ่งไปยังอีกชีตหนึ่ง

คุณอาจใช้ ฟังก์ชัน FILTER เพื่อกรองข้อมูลหลากหลายตามเกณฑ์ที่คุณให้ มาทำตามขั้นตอนเพื่อใช้ฟังก์ชันนี้ในการคัดลอกแถวจากหนึ่งไปยังอีกหนึ่งตามเกณฑ์ เราจะใช้ชุดข้อมูลต่อไปนี้ เราจะนำข้อมูลจากชีต FILTER และคัดลอกไปยังชีต Result3

ขั้นตอน:

• ขั้นแรก เราเลือกเซลล์ที่เราต้องการดูผลลัพธ์
• และใส่สูตรลงในเซลล์นั้น ในกรณีของเรา เซลล์คือ B5 จากชีต Result3

• เพิ่มเติม กด Enter จากแป้นพิมพ์
• และ นั่นคือทั้งหมด! แถวทั้งหมดที่ตรงตามเกณฑ์จะถูกคัดลอกจากชีต FILTER ไปยังชีต Result3

6. ใช้ Excel VBA สำหรับการคัดลอกแถวจากชีตหนึ่งไปยังอีกชีตหนึ่ง

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

ขั้นตอน:

• ในตอนแรก ไปที่แท็บ Developer
• จากนั้น เลือก ActiveX Control ภายใต้ตัวเลือก Insert

• เปลี่ยนข้อความปุ่มและแบบอักษรโดยใช้ตัวเลือก Properties

• คลิกที่ปุ่ม มันจะพาคุณไปที่หน้าต่าง VBA เขียนโค้ดแบบนี้:

โค้ด:

Private Sub CommandButton1_Click() a = Worksheets("VBA").Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To a If Worksheets("VBA").Cells(i, 4).Value > 150 Then Worksheets("VBA").Rows(i).Copy Worksheets("Result4").Activate b = Worksheets("Result4").Cells(Rows.Count, 1).End(xlUp).Row Worksheets("Result4").Cells(b + 1, 1).Select ActiveSheet.Paste Worksheets("VBA").Activate End If Next Application.CutCopyMode = False ThisWorkbook.Worksheets("VBA").Cells(1, 1).Select End Sub

• หลังจากนั้น รันโค้ดโดยคลิกที่ปุ่ม RubSub หรือกดลัดปุ่มแป้นพิมพ์ F5 หรือ คลิกที่ปุ่ม

คำอธิบายโค้ด VBA

ที่นี่บรรทัดสำคัญของโค้ดได้รับการอธิบาย

• เรานับจำนวนแถวทั้งหมดจาก VBA และเก็บไว้ในตัวแปร a
• ใช้เงื่อนไข IF เราตรวจสอบ Total Price ของแถวผลไม้แต่ละแถว
• อีกครั้ง นับจำนวนแถวของ Result4 และเก็บไว้ในตัวแปร b
• เลือกค่าที่ตรงตามเงื่อนไขโดยเพิ่มค่าของ b

• หลังจากนั้น เราคลิกที่ปุ่ม Run ของ VBA
• สุดท้าย ไปที่ชีต Result4 แล้วคุณจะเห็นแถวที่คัดลอกจากชีตหนึ่งไปยังอีกชีตหนึ่งตามเกณฑ์ใน Excel

อ่านเพิ่มเติม: วิธีคัดลอกแถวโดยอัตโนมัติใน Excel ไปยังแผ่นงานอื่น (4 วิธี)

สรุป

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

บทความที่เกี่ยวข้อง

• วิธีแสดงข้อความจากเซลล์อื่นใน Excel (4 วิธีที่เหมาะสม)
• รหัส VBA เพื่อเปรียบเทียบสองชีต Excel และคัดลอกความแตกต่าง
• วิธีใช้ VBA เพื่อวางค่าเท่านั้นโดยไม่มีรูปแบบใน Excel
• คัดลอกและวางไม่ทำงานใน Excel (9 เหตุผลและวิธีแก้)
• วิธีคัดลอกหลายแถวใน Excel โดยใช้ Macro (4 ตัวอย่าง)