เติมเซลล์ว่างจนถึงค่าถัดไปใน Excel (3 วิธีง่ายๆ)

หลายครั้ง คุณอาจพบชุดข้อมูล Excel ที่มีข้อมูลเพียงเซลล์เดียว และเซลล์ด้านล่างจะว่างเปล่าจนกว่าจะถึงค่าถัดไป

ดังที่แสดงด้านล่าง:

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

ง่ายต่อการแก้ไข!

ในบทช่วยสอนนี้ ฉันจะแสดงให้คุณเห็นว่าเติมเซลล์อย่างรวดเร็วจนกว่าจะเติมค่าถัดไปใน Excel.

คุณสามารถทำสิ่งนี้ได้อย่างง่ายดายโดยใช้เทคนิคการโต้ตอบพิเศษ Go-To, VBA หรือ Power Query

มาเริ่มกันเลย!

วิธีที่ 1 – กรอกโดยใช้ Go To Special + สูตร

สมมติว่าคุณมีชุดข้อมูลดังที่แสดงด้านล่าง และคุณต้องการกรอกข้อมูลในคอลัมน์ A และ B

ในคอลัมน์ B เป้าหมายคือการเติม "เครื่องพิมพ์" จนกว่าเซลล์ว่างสุดท้ายด้านล่าง จากนั้นเมื่อ "สแกนเนอร์" เริ่มทำงาน ให้เติม "สแกนเนอร์" ในเซลล์ด้านล่างจนกว่าเซลล์จะว่าง

ขั้นตอนด้านล่างใช้ไปที่พิเศษเพื่อเลือกเซลล์ว่างทั้งหมดและกรอกด้วยสูตรง่ายๆ:

1. เลือกข้อมูลที่จะกรอก (A1:D21 ในตัวอย่างของเรา)
2. ไปที่แท็บ "หน้าแรก"

3. ในกลุ่มแก้ไข คลิกไอคอน ค้นหาและเลือก (ซึ่งจะแสดงตัวเลือกเพิ่มเติมในเมนูแบบเลื่อนลง)
4. คลิกที่ตัวเลือก "ไป"

5. ในกล่องโต้ตอบ ไปที่ ที่เปิดขึ้น ให้คลิกปุ่ม พิเศษซึ่งจะเปิดกล่องโต้ตอบ "ไปที่พิเศษ"

6. ในกล่องโต้ตอบไปที่แบบพิเศษ ให้คลิก Blank

7. คลิกตกลง

ขั้นตอนข้างต้นจะเลือกเซลล์ว่างทั้งหมดในชุดข้อมูล (แสดงด้านล่าง)

ในบรรดาเซลล์ว่างที่เลือก คุณจะสังเกตเห็นว่าเซลล์หนึ่งสว่างกว่าเซลล์ที่เหลือเซลล์นี้เป็นเซลล์ที่ใช้งานอยู่ซึ่งเราต้องการป้อนสูตร

อย่ากังวลกับตำแหน่งของเซลล์ในส่วนที่เลือก เนื่องจากวิธีการของเราจะได้ผลในทุกกรณี

ต่อไปนี้เป็นขั้นตอนในการกรอกข้อมูลในเซลล์ว่างที่เลือก:

1. คลิกปุ่มเครื่องหมายเท่ากับ (=) บนแป้นพิมพ์ของคุณซึ่งจะแทรกเครื่องหมายเท่ากับในเซลล์ที่ใช้งานอยู่
2. กดปุ่มลูกศรขึ้นซึ่งจะแทรกการอ้างอิงเซลล์ของเซลล์เหนือเซลล์ที่ใช้งานอยู่ตัวอย่างเช่น ในกรณีของเรา B3 คือเซลล์ที่ใช้งานอยู่ และเมื่อเราทำสองขั้นตอนเหล่านี้ เซลล์จะป้อน =B2 ในเซลล์
3. กดปุ่ม Control ค้างไว้แล้วกด Enter

ขั้นตอนข้างต้นจะแทรกเซลล์ว่างทั้งหมดโดยอัตโนมัติด้วยค่าข้างต้น

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

ขณะนี้ มีอีกสองสิ่งที่คุณต้องระวังเมื่อใช้วิธีนี้

แปลงสูตรเป็นค่า

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

เปลี่ยนรูปแบบวันที่

หากคุณใช้วันที่ในข้อมูลของคุณ (เหมือนที่ฉันทำในข้อมูลตัวอย่าง) คุณจะสังเกตเห็นว่าค่าที่เติมในคอลัมน์วันที่เป็นตัวเลขแทนที่จะเป็นวันที่

หากผลลัพธ์ในคอลัมน์วันที่อยู่ในรูปแบบวันที่ที่ต้องการ แสดงว่าคุณทำได้ดีและไม่ต้องทำอะไรอีก

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

นี่คือขั้นตอนในการทำเช่นนี้:

1. เลือกคอลัมน์ที่มีวันที่
2. คลิกแท็บหน้าแรก
3. ในกลุ่ม Numbers ให้คลิกเมนูแบบเลื่อนลง Format แล้วเลือกรูปแบบวันที่

หากคุณต้องการทำช่องว่างภายในนี้เป็นครั้งคราว ฉันขอแนะนำเทคนิคพิเศษและสูตร Go-To นี้

แม้ว่าจะมีไม่กี่ขั้นตอน แต่ก็เรียบง่ายและให้ผลลัพธ์ในชุดข้อมูล

แต่ถ้าคุณต้องทำสิ่งนี้บ่อยๆ ฉันแนะนำให้คุณดู VBA และวิธีการ Power Query ที่อธิบายไว้ต่อไป

วิธีที่ 2 - กรอกรหัส VBA อย่างง่าย

คุณสามารถใช้โค้ดแมโคร VBA แบบง่าย ๆ เพื่อเติมเซลล์ใน Excel ได้อย่างรวดเร็วจนหมดค่าสุดท้าย

คุณต้องเพิ่มโค้ด VBA ลงในไฟล์เพียงครั้งเดียว จากนั้นจึงนำโค้ดนั้นมาใช้ซ้ำได้หลายครั้งในเวิร์กบุ๊กเดียวกัน หรือแม้แต่ในเวิร์กบุ๊กหลายๆ ไฟล์ในระบบของคุณ

นี่คือรหัส VBA ที่จะวนซ้ำผ่านแต่ละเซลล์ในส่วนที่เลือกและเติมในเซลล์ว่าง:

Sub FillDown() สำหรับแต่ละเซลล์ในส่วนที่เลือก ถ้าเซลล์ = "" จากนั้นให้เซลล์เติมลงท้ายถ้าถัดไปสิ้นสุดย่อย

โค้ดด้านบนใช้ For loop เพื่อวนซ้ำผ่านแต่ละเซลล์ในส่วนที่เลือก

ใน For loop ฉันใช้เงื่อนไข if-then เพื่อตรวจสอบว่าเซลล์นั้นว่างหรือไม่

ถ้าเซลล์ว่างเปล่า เซลล์นั้นจะถูกเติมด้วยค่าจากเซลล์ด้านบน ถ้าไม่ใช่ วง for จะไม่สนใจเซลล์และย้ายไปยังเซลล์ถัดไป

ตอนนี้คุณมีโค้ด VBA แล้ว ให้ฉันแสดงตำแหน่งที่จะใส่โค้ดนี้ใน Excel:

1. เลือกข้อมูลที่จะกรอก
2. คลิกที่แท็บนักพัฒนา

3. คลิกไอคอน Visual Basic (หรือคุณสามารถใช้แป้นพิมพ์ลัด ALT+F11)ซึ่งจะเป็นการเปิดตัวแก้ไข Visual Basic ใน Excel

4. ในตัวแก้ไข Visual Basic ทางด้านซ้าย คุณจะมี Project Explorerหากคุณไม่เห็น ให้คลิกตัวเลือกมุมมองในเมนู จากนั้นคลิก Project Explorer

5. ถ้าคุณมีเวิร์กบุ๊ก Excel หลายรายการเปิดอยู่ Project Explorer จะแสดงชื่อเวิร์กบุ๊กทั้งหมดค้นหาชื่อเวิร์กบุ๊กที่คุณมีข้อมูล
6. คลิกขวาที่วัตถุใดๆ ในเวิร์กบุ๊ก ไปที่ แทรก แล้วคลิก โมดูลซึ่งจะแทรกโมดูลใหม่สำหรับสมุดงานนี้

7. ดับเบิลคลิกที่ "โมดูล" ที่คุณเพิ่งแทรกในขั้นตอนข้างต้นมันจะเปิดหน้าต่างรหัสสำหรับโมดูลนั้น

8. คัดลอกและวางโค้ด VBA ลงในหน้าต่างโค้ด
9. วางเคอร์เซอร์ไว้ที่ใดก็ได้ในโค้ดและเรียกใช้โค้ดมาโครโดยคลิกปุ่มสีเขียวในแถบเครื่องมือหรือใช้แป้นพิมพ์ลัด F5

ขั้นตอนข้างต้นจะเรียกใช้โค้ด VBA และข้อมูลของคุณจะถูกกรอก

ถ้าคุณต้องการใช้โค้ด VBA นี้อีกครั้งในภายหลัง คุณจะต้องบันทึกไฟล์นี้เป็นเวิร์กบุ๊ก Excel ที่เปิดใช้งานมาโคร (ส่วนขยาย .XLSM)

อีกสิ่งหนึ่งที่คุณสามารถทำได้คือเพิ่มมาโครนี้ใน Quick Access Toolbar ซึ่งมองเห็นได้เสมอและคุณสามารถเข้าถึงมาโครนี้ได้ด้วยการคลิกเพียงครั้งเดียว (ในเวิร์กบุ๊กที่มีโค้ดในแบ็กเอนด์)

ดังนั้นในครั้งต่อไปที่คุณมีข้อมูลที่ต้องกรอกข้อมูล คุณสามารถเลือกและคลิกปุ่มมาโครในแถบเครื่องมือด่วนได้

คุณยังสามารถเพิ่มมาโครนี้ลงในเวิร์กบุ๊กแมโครส่วนบุคคลของคุณ แล้วใช้ในเวิร์กบุ๊กใดก็ได้ในระบบของคุณ

คลิกที่นี่เพื่ออ่านเพิ่มเติมเกี่ยวกับเวิร์กบุ๊กมาโครส่วนบุคคลของคุณและวิธีเพิ่มโค้ดลงในสมุดงาน

วิธีที่ 3 - กรอกโดยใช้ Power Query

Power Queryมีฟังก์ชันในตัวที่ให้คุณกรอกได้ด้วยคลิกเดียว

ขอแนะนำเมื่อคุณยังคงใช้ Power Query เพื่อแปลงข้อมูลหรือรวมข้อมูลจากหลายเวิร์กชีตหรือหลายเวิร์กบุ๊ก

เป็นส่วนหนึ่งของเวิร์กโฟลว์ที่มีอยู่ คุณสามารถเติมเซลล์ว่างได้อย่างรวดเร็วโดยใช้ตัวเลือกเติมใน Power Query

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

ด้านล่างฉันมีชุดข้อมูลที่แปลงเป็นตาราง Excel แล้วคุณสามารถทำได้โดยเลือกชุดข้อมูล ไปที่แท็บ แทรก แล้วคลิกไอคอน ตาราง

ต่อไปนี้เป็นขั้นตอนในการใช้ Power Query เพื่อกรอกข้อมูลจนกว่าจะถึงค่าถัดไป:

1. เลือกเซลล์ใดก็ได้ในชุดข้อมูล
2. คลิกแท็บข้อมูล

3. ในกลุ่ม รับและแปลงข้อมูล ให้คลิก จากเวิร์กชีตซึ่งจะเป็นการเปิดตัวแก้ไข Power Queryโปรดทราบว่าเซลล์ว่างจะแสดงค่า "null" ใน Power Query

4. ในตัวแก้ไข Power Query เลือกคอลัมน์ที่คุณต้องการกรอกในการดำเนินการนี้ ให้กดปุ่ม Control ค้างไว้แล้วคลิกส่วนหัวของคอลัมน์ที่คุณต้องการเลือกในตัวอย่างของเรา มันจะเป็นคอลัมน์วันที่และผลิตภัณฑ์
5. คลิกขวาที่ชื่อที่เลือก

6. ไปที่ตัวเลือกเติมแล้วคลิกลงสิ่งนี้จะเติมข้อมูลในเซลล์ว่างทั้งหมดด้วย data

7. คลิกแท็บ ไฟล์ แล้วคลิก ปิดและโหลดซึ่งจะแทรกแผ่นงานใหม่พร้อมตารางผลลัพธ์ในสมุดงาน

แม้ว่าวิธีการนี้อาจฟังดูเกินความจำเป็น แต่ข้อดีอย่างหนึ่งของการใช้ Power Query ก็คือช่วยให้คุณสามารถอัปเดตข้อมูลผลลัพธ์ได้อย่างรวดเร็วเมื่อข้อมูลเดิมเปลี่ยนแปลง

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

แม้ว่าวิธี Power Query จะทำงานได้ดี แต่วิธีที่ดีที่สุดคือใช้เมื่อคุณใช้ Power Query ในเวิร์กโฟลว์อยู่แล้ว

หากคุณมีชุดข้อมูลเดียวที่มีเซลล์ว่างให้กรอก จะสะดวกกว่าถ้าใช้วิธีพิเศษไปที่หรือวิธี VBA (อธิบายไว้ด้านบน)

ดังนั้นคุณสามารถใช้สามวิธีง่ายๆ เหล่านี้เพื่อเติมเซลล์ว่างจนค่าถัดไปใน Excel.

ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์