Snasui Excel Forum

คลังคำตอบแห่งความรู้จากคนไทย เพื่อโลกที่ต้องการเข้าใจในสิ่งเล็ก ๆ อย่างลึกซึ้ง
https://www.snasui.com/

เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

https://www.snasui.com/viewtopic.php?t=7964

Page 1 of 1

เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Thu Dec 25, 2014 6:00 pm
by Totem
:D เรียนอาจารย์และเพื่อนสมาชิก

ถามปัญหาดังนี้

เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

แบบที่ 1

ตาราง 1,2,3 เหมือนกัน ให้นับ
ตัวอย่าง
a1,c3 2 นับได้ มีทั้ง 3 ตารางไม่ว่าจะอยู่ในพื้นที่ใดๆ
และให้แสดงสีที่ต้องการ

ตาราง 2,3 เหมือนกัน ให้นับ มีทั้ง 2 ตารางไม่ว่าจะอยู่ในพื้นที่ใดๆ
b2 1 นับได้
และให้แสดงสีที่ต้องการ

แบบที่ 2
ตาราง 1,2,3 เหมือนกันในแถวคอลัมน์เดียวกัน ให้นับ
ตัวอย่าง
a1,d4 2 นับได้ คอลัมน์ I,L
และให้แสดงสีที่ต้องการ

ตาราง 2,3 เหมือนกันในแถวคอลัมน์เดียวกัน ให้นับ
b2,C3,E5 3 นับได้ คอลัมน์ J,K,M
และให้แสดงสีที่ต้องการ

ขอบคุณครับ
compare.xlsx

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Thu Dec 25, 2014 8:39 pm
by snasui
:D คำถามคือ

ในตารางใด ๆ จะมีค่าซ้ำเกิดขึ้นได้หรือไม่ เช่นในตารางที่ 1 มี a1 ซ้ำกัน 3 ตัว และไม่มีในตารางอื่น หากมีกรณีเช่นนี้ จะนับอย่างไรครับ :?:

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Fri Dec 26, 2014 11:07 am
by Totem
snasui wrote::D คำถามคือ

ในตารางใด ๆ จะมีค่าซ้ำเกิดขึ้นได้หรือไม่ เช่นในตารางที่ 1 มี a1 ซ้ำกัน 3 ตัว และไม่มีในตารางอื่น หากมีกรณีเช่นนี้ จะนับอย่างไรครับ :?:
:D แบบที่ 1

ถ้ามี a1 แล้วในทุกตาราง 1,2,3 จะไม่มี a1 ซ้ำอีก มีได้ตัวเดียวเท่านั้นในทุกตาราง

แบบที่ 2

ถ้ามี a1
ตัวอย่าง a1 มีในช่อง I2 แล้ว ในช่อง I1,I3:I12 จะไม่มี a1 อีก เป็นเช่นนี้ในตารางที่ 2
และ 3

- a1 มีในช่อง I2 , I18 , I28 นับ 1 ครั้ง และ
- a1 อาจมีอีกในคอลัมน์ J2:J12 , K2:K12 , L2:L12 , M2:M12 ได้ ให้นับในแนวคอลัมน์
ยกตัวอย่างในคอลัมน์ J เช่น a1 มีในช่อง J2 ในตารางที่1 , ช่อง J16 ในตารางที่2 , ช่อง J25
ในตารางที่3 แล้ว ให้นับเป็น 1 ครั้ง ครับ

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Fri Dec 26, 2014 2:34 pm
by snasui
:D ลองดูตัวอย่างการใช้ Conditional Formatting ตามไฟล์แนบ ซึ่งผมทำตัวอย่างมาให้สำหรับแบบที่ 1 ส่วนแบบที่ 2 ลองประยุกต์จากแบบที่ 1 และลองทำมาเองดูก่อน ติดตรงไหนค่อยถามกันต่อครับ

การดู Conditional Formatting ที่กำหนดไว้แล้วให้คลิกที่ B3 > เข้าเมนู Home > Conditional Formatting > Manage Rules > เลือก Rule ที่สนใจ > คลิกปุ่ม Edit Rule > สังเกตการกำหนดค่า

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Mon Dec 29, 2014 3:01 pm
by Totem
snasui wrote::D ลองดูตัวอย่างการใช้ Conditional Formatting ตามไฟล์แนบ ซึ่งผมทำตัวอย่างมาให้สำหรับแบบที่ 1 ส่วนแบบที่ 2 ลองประยุกต์จากแบบที่ 1 และลองทำมาเองดูก่อน ติดตรงไหนค่อยถามกันต่อครับ

การดู Conditional Formatting ที่กำหนดไว้แล้วให้คลิกที่ B3 > เข้าเมนู Home > Conditional Formatting > Manage Rules > เลือก Rule ที่สนใจ > คลิกปุ่ม Edit Rule > สังเกตการกำหนดค่า
:D ได้คำตอบในส่วน Conditional Formatting แล้วครับ ขอบคุณครับ

ขอถามเพิ่มเติม หากต้องการทราบจำนวนที่เหมือนกัน ตาราง 1,2,3 แล้วนับออกมา
ตัวอย่าง
ตาราง 1 ได้ a1 ช่อง B3 ,c3 ช่อง D5
ตาราง 2 ได้ a1 ช่อง C15 ,c3 ช่อง F18
ตาราง 3 ได้ a1 ช่อง D27 ,c3 ช่อง B30

นับได้ทั้งหมดเท่ากับ 2

ตอบ ช่อง c54 = 2

ต้องการทราบนับจำนวนที่เหมือนกัน ตาราง 2,3 แล้วนับออกมา
ตัวอย่าง
ตาราง 2 ได้ b2 ช่อง E15
ตาราง 3 ได้ b2 ช่อง F28

นับได้ทั้งหมดเท่ากับ 1

ตอบ ช่อง c63 = 1
compare_1.xlsx

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Sat Jan 03, 2015 6:18 am
by snasui
:D ให้แสดงผลที่ผลที่ชีทใด เซลล์ใดครับ

สำหรับการนับข้อมูลที่กระจัดกระจาย ไม่เป็น Database ไม่เหมาะกับการใช้สูตร ให้นับด้วย VBA แทน ลองเขียนมาก่อน ติดตรงไหนค่อยถามกันต่อครับ

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Sun Jan 04, 2015 6:36 pm
by Totem
snasui wrote::D ให้แสดงผลที่ผลที่ชีทใด เซลล์ใดครับ

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

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Mon Jan 05, 2015 1:38 pm
by Totem
:D เรียนอาจารย์และเพื่อนสมาชิก

ช่วยแก้ไขปรับ code ครับ

Code: Select all

Sub count()

Dim LR1 As Long, LR2 As Long, i As Long
Dim Cnt1 As Integer, Cnt2 As Integer


LR1 = Sheets(1).Cells(Rows.count, 2).End(xlUp).Row

For i = 1 To LR1
    
    LR2 = Sheets(1).Cells(Rows.count, 9).End(xlUp).Row + 1
    
    Cnt1 = Application.WorksheetFunction.CountIf _
        (Sheets(1).Range("B3:F" & LR1), Sheets(1).Cells(i, 2))
    
     
    Cnt2 = Application.WorksheetFunction.CountIf _
        (Sheets(1).Range("A1:A" & LR2), Sheets(1).Cells(i, 2))
    
    If Cnt1 > 2 And Cnt2 = 0 Then
        If Sheets(1).Cells(LR2, 9) = "" Then
            Sheets(1).Cells(LR2, 9) = Sheets(1).Cells(i, 2)
            Sheets(1).Cells(LR2, 10) = Cnt1
        Else
            Sheets(1).Cells(LR2 + 1, 9) = Sheets(1).Cells(i, 2)
            Sheets(1).Cells(LR2 + 1, 10) = Cnt1
        End If
    End If
Next
End Sub
ปัญหาคือ

1.

Code: Select all

1.LR1 = Sheets(1).Cells(Rows.count, 2).End(xlUp).Row
ไม่ครอบคลุมพื้นที่ ที่ต้องการ หากในคอลัมน์ที่ 2 ไม่มีข้อมูลจะไม่นับครับ ต้องการให้อยู่ในช่วง B3:F34
ทำให้ไม่นับ b2
2.code ที่เขียน นับจำนวนแล้วได้ ช่องI2=a1 , ช่องJ2=3 , ช่องI3=c3 , ช่องJ3=3 ซึ่งไม่ได้ต้องการที่ถูกต้อง
คือ
ต้องการให้ได้ข้อมูล
a1 - ช่อง J2 =1
c3 - ช่อง J3 =1
b2 - ช่อง J4 =1

ขอบคุณครับ
compare_2.xlsm

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Tue Jan 06, 2015 8:44 pm
by snasui
:D ดูจากลักษณะตัวอย่างและคำตอบที่ต้องการแล้วสามารถใช้สูตรได้ โดย

ที่ J2 คีย์สูตร

=((COUNTIF($B$4:$F$13,I2)+COUNTIF($B$15:$F$24,I2)+COUNTIF($B$26:$F$35,I2))>1)+0

Enter > Copy ลงด้า่นล่างครับ

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Tue Jan 06, 2015 9:44 pm
by Totem
snasui wrote::D ดูจากลักษณะตัวอย่างและคำตอบที่ต้องการแล้วสามารถใช้สูตรได้ โดย

ที่ J2 คีย์สูตร

=((COUNTIF($B$4:$F$13,I2)+COUNTIF($B$15:$F$24,I2)+COUNTIF($B$26:$F$35,I2))>1)+0

Enter > Copy ลงด้า่นล่างครับ
:D ตัวอย่าง ข้อมูลในช่อง I2:I4 เป็น a1,c3,b2 เป็นข้อมูลบางส่วนครับ
อาจจะเป็น d3 , p3 ฯลฯ จะไม่ทราบแน่นอนว่าเป็นข้อความตัวเลขใด จะปรากฎในพื้นที่ $B$4:$F$13 , $B$15:$F$24 , $B$26:$F$35 สูตรข้างต้นจึงไม่ได้คำตอบที่ต้องการครับ

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Wed Jan 07, 2015 6:03 am
by snasui
:D จากตัวอย่างที่เขียนมาแสดงให้เห็นว่าเป็นการระบุเข้าไปเองครับ

กรณี List ค่าใน I2:I4 ด้วย Code ดูตัวอย่างตามด้านล่างครับ

Code: Select all

Sub test()
    Dim r As Range, rall As Range
    Dim rcheck As Range
    With Sheets("sheet1")
        Set rall = .Range("b3:f34").SpecialCells(xlCellTypeConstants)
        For Each r In rall
            Set rcheck = .Range("i1", .Range("i" & .Rows.count).End(xlUp))
            If Application.CountIf(rcheck, r) = 0 Then
                .Range("i" & .Rows.count).End(xlUp).Offset(1, 0) = r.Value
            End If
        Next r
    End With
End Sub

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Wed Jan 07, 2015 11:55 am
by Totem
snasui wrote::D จากตัวอย่างที่เขียนมาแสดงให้เห็นว่าเป็นการระบุเข้าไปเองครับ

กรณี List ค่าใน I2:I4 ด้วย Code ดูตัวอย่างตามด้านล่างครับ

Code: Select all

Sub test()
    Dim r As Range, rall As Range
    Dim rcheck As Range
    With Sheets("sheet1")
        Set rall = .Range("b3:f34").SpecialCells(xlCellTypeConstants)
        For Each r In rall
            Set rcheck = .Range("i1", .Range("i" & .Rows.count).End(xlUp))
            If Application.CountIf(rcheck, r) = 0 Then
                .Range("i" & .Rows.count).End(xlUp).Offset(1, 0) = r.Value
            End If
        Next r
    End With
End Sub
:D ใช้ได้ตามต้องการครับ ขอบคุณครับ

Re: เปรียบเทียบซ้ำ2ตาราง,3ตารางแล้วนับจำนวนและแสดงสี

Posted: Wed Jan 07, 2015 1:09 pm
by วังวู ช่ง
snasui wrote::D จากตัวอย่างที่เขียนมาแสดงให้เห็นว่าเป็นการระบุเข้าไปเองครับ

กรณี List ค่าใน I2:I4 ด้วย Code ดูตัวอย่างตามด้านล่างครับ

Code: Select all

Sub test()
    Dim r As Range, rall As Range
    Dim rcheck As Range
    With Sheets("sheet1")
        Set rall = .Range("b3:f34").SpecialCells(xlCellTypeConstants)
        For Each r In rall
            Set rcheck = .Range("i1", .Range("i" & .Rows.count).End(xlUp))
            If Application.CountIf(rcheck, r) = 0 Then
                .Range("i" & .Rows.count).End(xlUp).Offset(1, 0) = r.Value
            End If
        Next r
    End With
End Sub
:D :thup: :cp:
All times are UTC+07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited