การจัดรูปแบบตามเงื่อนไขหลักในสมุดงาน:รูปแบบทริกเกอร์จากข้อมูลภายนอก

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

ในบทช่วยสอนนี้ เราจะแสดงวิธีทริกเกอร์การจัดรูปแบบตามเงื่อนไขตามค่าสมุดงานภายนอก

สมมติว่าคุณติดตามยอดขายรายไตรมาสจริงในไฟล์เดียวและเป้าหมายการขายรายไตรมาสในอีกไฟล์หนึ่ง ในชีตข้อมูลจริง คุณต้องการเน้นยอดขายจริงใดๆ ที่ต่ำกว่าเป้าหมาย โดยดึงเป้าหมายที่ถูกต้องจากไฟล์ภายนอก

วิธีที่ 1:คอลัมน์ตัวช่วยเหลือพร้อมการอ้างอิงภายนอก

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

ขั้นตอนที่ 1:เตรียมสมุดงานของคุณ

ขั้นแรก สร้างและบันทึกสมุดงานทั้งสองด้วยข้อมูลตัวอย่างด้านบน:

• สร้าง “Sales Target.xlsx” และป้อนข้อมูลเป้าหมาย
• บันทึกลงบนเดสก์ท็อปของคุณหรือโฟลเดอร์เฉพาะ
• สร้าง “Actual Sales.xlsx” และป้อนข้อมูลการขายจริง
• บันทึกไว้ในตำแหน่งเดียวกัน

ขั้นตอนที่ 2:สร้างคอลัมน์ตัวช่วยเหลือด้วยการอ้างอิงภายนอก

• ใน “Actual Sales.xlsx” เพิ่มคอลัมน์ตัวช่วย (เริ่มต้นในคอลัมน์ G):
• เลือกเซลล์ G2 และแทรกสูตรต่อไปนี้

=[SalesTarget.xlsx]Quarterly_Targets!B2

• ลากสูตรไปทางขวาเพื่อป้อนสูตรอัตโนมัติในเซลล์ H2, I2 และ J2

• หากต้องการอัปเดตค่า ให้เลือก Sales Target.xlsx ไฟล์

• เลือกเซลล์ G2:J2
• ลากสูตรลงเพื่อป้อนสูตรอัตโนมัติในเซลล์ที่เหลือ

ขั้นตอนที่ 3:ใช้การจัดรูปแบบตามเงื่อนไขโดยใช้คอลัมน์ตัวช่วยเหลือ

ตอนนี้ใช้การอ้างอิงภายในสำหรับการจัดรูปแบบตามเงื่อนไข

• เลือกช่วงเซลล์ (B2:B6)
• ไปที่หน้าแรก แท็บ>> เลือก การจัดรูปแบบตามเงื่อนไข>> เลือก กฎใหม่ .
• เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ .
• ป้อนสูตรต่อไปนี้:

• คลิก รูปแบบ>> เลือกสีเติมสีแดงอ่อน
• คลิก ตกลง .

เพิ่มกฎเพิ่มเติม:

ทำซ้ำสำหรับแต่ละไตรมาสตามความจำเป็น

ไตรมาสที่ 2:

• ป้อนสูตรต่อไปนี้:

• คลิก รูปแบบ>> เลือกสีเติมสีฟ้าอ่อน
• คลิก ตกลง .

ไตรมาสที่ 3:

• ป้อนสูตรต่อไปนี้:

• คลิก รูปแบบ>> เลือกสีเติมสีเขียวอ่อน
• คลิก ตกลง .

ไตรมาสที่ 4:

• ป้อนสูตรต่อไปนี้:

• คลิก รูปแบบ>> เลือกสีเติมสีม่วงอ่อน
• คลิก ตกลง .

ขั้นตอนที่ 4:ซ่อนคอลัมน์ตัวช่วยเหลือ (ไม่บังคับ)

• เลือกคอลัมน์ G:J
• คลิกขวา>> เลือก ซ่อน .

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

วิธีที่ 2:ใช้โซลูชัน Power Query

Power Query มอบโซลูชันที่มีประสิทธิภาพสำหรับผู้ใช้ที่มี Excel 365 หรือ Excel 2016+

ขั้นตอนที่ 1:นำเข้าข้อมูลภายนอกด้วย Power Query

• เปิดสมุดงาน “ยอดขายจริง.xlsx”
• ไปที่ข้อมูล แท็บ>> เลือก รับข้อมูล>> เลือก จากไฟล์>> เลือก จากสมุดงาน .
• เรียกดูเพื่อเลือกไฟล์ “Sales Target.xlsx”
• เลือกตาราง “เป้าหมายรายไตรมาส”
• คลิก นำเข้า .

• ในนาวิเกเตอร์ หน้าต่าง>> เลือกแผ่นข้อมูล
• คลิก แปลงข้อมูล .

• ในตัวแก้ไข Power Query:
  • เปลี่ยนชื่อคอลัมน์ให้ตรงกับความต้องการของคุณ (Target_Q1, Target_Q2 ฯลฯ)
  • ไปที่หน้าแรก แท็บ>> ปิดและโหลดไปที่ .

• • เลือก ตาราง>> เลือก แผ่นงานใหม่ .
  • คลิก ตกลง .

ขั้นตอนที่ 2:ใช้การจัดรูปแบบตามเงื่อนไข

ตอนนี้ใช้การจัดรูปแบบตามเงื่อนไขมาตรฐานกับข้อมูลที่นำเข้าเช่นเดียวกับในโซลูชันที่ 1 แต่อ้างอิงเฉพาะข้อมูลภายในเท่านั้น

• เลือกช่วงเซลล์ (B2:B6)
• ไปที่หน้าแรก แท็บ>> เลือก การจัดรูปแบบตามเงื่อนไข>> เลือก กฎใหม่ .
• เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ .
• ป้อนสูตรต่อไปนี้:

=B2 <Quarterly_Targets!$B2

• คลิก รูปแบบ>> เลือกสีเติมสีแดงอ่อน
• คลิก ตกลง .

• เพิ่มกฎเพิ่มเติมสำหรับส่วนที่เหลือของควอเตอร์

ไตรมาสที่ 2:

=C2 <Quarterly_Targets!$C2

ไตรมาสที่ 3:

=D2 <Quarterly_Targets!$D2

ไตรมาสที่ 4:

=E2 <Quarterly_Targets!$E2

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

• ใน รีเฟรชทุกๆ>> แทรก 5 นาที
• คลิก ตกลง .

วิธีนี้จะรีเฟรชข้อมูลภายนอกโดยอัตโนมัติและหลีกเลี่ยงข้อจำกัดในการอ้างอิง

วิธีที่ 3:มาโคร VBA สำหรับการทำงานอัตโนมัติเต็มรูปแบบ

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

หากต้องการเปิดตัวแก้ไข VBA:

• เปิดสมุดงานการขายจริงของคุณ
• ไปที่นักพัฒนาซอฟต์แวร์ แท็บ>> เลือก Visual Basic . หรือกด Alt + F11 .
• ในหน้าต่างโครงการ คลิกขวาที่สมุดงานของคุณ
• เลือก แทรก >> เลือก โมดูล .

• คัดลอกและวางโค้ด VBA ต่อไปนี้

รหัส VBA:

Sub HighlightSalesBelowTarget()
 Dim targetFilePath As String
 targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- Update this to your file path
 
 Dim wbTarget As Workbook
 Dim wsTarget As Worksheet
 Dim wsActual As Worksheet
 Dim i As Long, j As Long
 Dim salesValue As Variant, targetValue As Variant
 
 Set wsActual = ThisWorkbook.Sheets("Performance_Data")
 Set wbTarget = Workbooks.Open(targetFilePath, ReadOnly:=True)
 Set wsTarget = wbTarget.Sheets("Quarterly_Targets")
 
 ' Data rows: 2 to 6, columns: 2 (B/Q1) to 5 (E/Q4)
 For i = 2 To 6 ' Rows: products
 For j = 2 To 5 ' Columns: Q1-Q4
 salesValue = wsActual.Cells(i, j).Value
 targetValue = wsTarget.Cells(i, j).Value
 If IsNumeric(salesValue) And IsNumeric(targetValue) Then
 If salesValue < targetValue Then
 wsActual.Cells(i, j).Interior.Color = RGB(255, 199, 206) ' Light red
 Else
 wsActual.Cells(i, j).Interior.Pattern = xlNone ' No color
 End If
 End If
 Next j
 Next i
 wbTarget.Close SaveChanges:=False
 MsgBox "Highlighting complete.", vbInformation
End Sub

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

บันทึกและเรียกใช้:

• บันทึกสมุดงานของคุณเป็นไฟล์ที่เปิดใช้งานมาโคร (.xlsm)
• ไปที่นักพัฒนาซอฟต์แวร์ แท็บ>> เลือก มาโคร .
• เลือก เน้นการขายด้านล่างเป้าหมาย>> คลิก เรียกใช้ .

เอาต์พุต:

สิ่งที่ใช้ไม่ได้ผล:การอ้างอิงภายนอกโดยตรง &ช่วงที่ตั้งชื่อ

Excel บางเวอร์ชันแสดงคำเตือน “คุณไม่สามารถใช้การอ้างอิงไปยังสมุดงานอื่นสำหรับเกณฑ์การจัดรูปแบบตามเงื่อนไข”

• อ้างอิงสมุดงานภายนอกโดยตรง (เช่น =[Sales_Targets.xlsx]Quarterly_Targets!B2) ไม่ได้รับอนุญาตในกฎการจัดรูปแบบตามเงื่อนไข Excel จะเกิดข้อผิดพลาด
• ช่วงที่มีชื่อ ที่กำหนดไว้ในสมุดงานภายนอกไม่สามารถอ้างอิงในการจัดรูปแบบตามเงื่อนไขของสมุดงานอื่นได้
• แม้แต่การใช้ฟังก์ชันทางอ้อมหรือฟังก์ชันที่คล้ายกันก็ไม่สามารถใช้ได้กับไฟล์ในบริบทนี้

ไม่มีวิธีดั้งเดิมโดยตรงในการใช้ค่าภายนอกในกฎการจัดรูปแบบตามเงื่อนไข

คำแนะนำ

• สำหรับธุรกิจส่วนใหญ่: ใช้ Power Query เพื่อนำเข้าข้อมูลภายนอก มีความแข็งแกร่ง รองรับการรีเฟรช และเก็บตรรกะทั้งหมดไว้ในสมุดงานเล่มเดียว
• สำหรับการตรวจสอบเฉพาะกิจหรือการตรวจสอบด่วน: ใช้คอลัมน์ตัวช่วยที่มีการอ้างอิงภายนอก หากคุณไม่สนใจที่จะเปิดทั้งสองไฟล์ไว้
• สำหรับโซลูชันอัตโนมัติและต่อเนื่อง: ใช้ VBA สำหรับการทำงานอัตโนมัติและการจัดรูปแบบ โดยเฉพาะชุดข้อมูลขนาดใหญ่

บทสรุป

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

การทริกเกอร์การจัดรูปแบบตามเงื่อนไขตามค่าจากสมุดงานภายนอกไม่สามารถทำได้ใน Excel