บทความนี้จะอธิบายเกี่ยวกับไวยากรณ์ของสูตรและการใช้ฟังก์ชัน DAVERAGE ใน Microsoft Excel
คำอธิบาย
หาค่าเฉลี่ยของค่าต่างๆ ในฟิลด์ (คอลัมน์) ของเรคคอร์ดในรายการหรือฐานข้อมูลที่ตรงกับเงื่อนไขที่คุณระบุ
ไวยากรณ์
DAVERAGE(database, field, criteria)
ไวยากรณ์ของฟังก์ชัน DAVERAGE มีอาร์กิวเมนต์ดังนี้
-
ฐานข้อมูล คือช่วงของเซลล์ที่ประกอบขึ้นเป็นรายการหรือฐานข้อมูล ฐานข้อมูลคือรายการของข้อมูลที่เกี่ยวข้องซึ่งแถวของข้อมูลที่เกี่ยวข้องคือระเบียน และคอลัมน์ของข้อมูลคือเขตข้อมูล แถวแรกของรายการมีป้ายชื่อสําหรับแต่ละคอลัมน์
-
สนาม ระบุว่าจะใช้คอลัมน์ใดในฟังก์ชัน ใส่ป้ายชื่อคอลัมน์ที่อยู่ระหว่างเครื่องหมายอัญประกาศคู่ เช่น "อายุ" หรือ "ผลตอบแทน" หรือตัวเลข (ไม่มีเครื่องหมายอัญประกาศ) ที่แสดงตําแหน่งของคอลัมน์ภายในรายการ: 1 สําหรับคอลัมน์แรก 2 สําหรับคอลัมน์ที่สอง และอื่นๆ
-
เกณฑ์ คือช่วงของเซลล์ที่มีเงื่อนไขที่คุณระบุ คุณสามารถใช้ช่วงใดก็ได้สําหรับอาร์กิวเมนต์ criteria ตราบใดที่ช่วงนั้นมีป้ายชื่อคอลัมน์อย่างน้อยหนึ่งป้าย และมีอย่างน้อยหนึ่งเซลล์อยู่ใต้ป้ายชื่อคอลัมน์ที่คุณระบุเงื่อนไขสําหรับคอลัมน์นั้น
ข้อสังเกต
-
คุณสามารถใช้ช่วงใดเป็นอาร์กิวเมนต์ criteria ก็ได้ ตราบเท่าที่ช่วงนั้นมีป้ายชื่อของคอลัมน์อย่างน้อยหนึ่งป้าย และมีอย่างน้อยหนึ่งเซลล์อยู่ใต้ป้ายชื่อคอลัมน์ที่ใช้ระบุเงื่อนไข
ตัวอย่างเช่น ถ้าช่วง G1:G2 ประกอบด้วยป้ายชื่อคอลัมน์ Income ในเซลล์ G1 และจำนวนตัวเลข 10,000 ในเซลล์ G2 คุณก็สามารถกำหนดช่วงเป็น MatchIncome และใช้ชื่อนั้นเป็นอาร์กิวเมนต์ของเกณฑ์ในฟังก์ชันฐานข้อมูลได้
-
ถึงแม้ว่าช่วงเงื่อนไขจะอยู่ที่ใดก็ได้บนเวิร์กชีต แต่อย่าวางช่วงเงื่อนไขไว้ใต้รายการ ถ้าคุณเพิ่มข้อมูลเพิ่มเติมลงในรายการ ข้อมูลใหม่จะถูกเพิ่มลงในแถวแรกด้านล่างรายการ ถ้าแถวด้านล่างรายการไม่ว่างเปล่า Excel จะไม่สามารถเพิ่มข้อมูลใหม่ได้
-
ตรวจสอบให้แน่ใจว่าช่วงของเกณฑ์ไม่ได้ซ้อนทับรายการข้อมูล
-
เมื่อต้องการดำเนินการทั้งคอลัมน์ในฐานข้อมูล ให้เพิ่มบรรทัดว่างไว้ใต้ป้ายชื่อคอลัมน์ภายในช่วง criteria
ตัวอย่าง
คัดลอกข้อมูลตัวอย่างในตารางต่อไปนี้ และวางในเซลล์ A1 ของเวิร์กชีต Excel ใหม่ สำหรับสูตรที่จะแสดงผลลัพธ์ ให้เลือกสูตร กด F2 แล้วกด Enter ถ้าคุณต้องการ คุณสามารถปรับความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมดได้
|
ต้นไม้ |
ความสูง |
อายุ |
ผลตอบแทน |
ผลกำไร |
ความสูง |
|---|---|---|---|---|---|
|
=แอปเปิล |
>10 |
<16 |
|||
|
=แพร์ |
|||||
|
ต้นไม้ |
ความสูง |
อายุ |
ผลตอบแทน |
ผลกำไร |
|
|
Apple |
18 |
20 |
14 |
105 |
|
|
Pear |
1.2 |
1.2 |
10 |
96 |
|
|
Cherry |
1.3 |
14 |
9 |
105 |
|
|
Apple |
14 |
15 |
10 |
75 |
|
|
Pear |
9 |
8 |
8 |
76.8 |
|
|
Apple |
8 |
9 |
6 |
45 |
|
|
สูตร |
คำอธิบาย |
ผลลัพธ์ |
|||
|
=DAVERAGE(A4:E10,"ผลตอบแทน",A1:B2) |
ผลตอบแทนโดยเฉลี่ยของต้นแอปเปิ้ลที่มีความสูงเกิน 10 ฟุต |
1.2 |
|||
|
=DAVERAGE(A4:E10, 3, A4:E10) |
อายุเฉลี่ยของต้นไม้ทุกต้นในฐานข้อมูล |
1.3 |
ตัวอย่างของเงื่อนไข
-
การพิมพ์เครื่องหมายเท่ากับในเซลล์จะระบุว่าคุณต้องการใส่สูตร เมื่อต้องการแสดงข้อความที่มีเครื่องหมายเท่ากับ ให้ล้อมรอบข้อความและเครื่องหมายเท่ากับด้วยเครื่องหมายอัญประกาศคู่ ดังนี้
"=Davolio"
คุณยังสามารถทําเช่นนั้นได้ถ้าคุณกําลังใส่นิพจน์ (การรวมสูตร ตัวดําเนินการ และข้อความ) และคุณต้องการแสดงเครื่องหมายเท่ากับแทนที่จะให้ Excel ใช้ในการคํานวณ ตัวอย่างเช่น
=''= รายการ ''
เมื่อรายการคือข้อความหรือค่าที่คุณต้องการค้นหา ตัวอย่างเช่น:
|
สิ่งที่คุณพิมพ์ลงในเซลล์ |
สิ่งที่ Excel ประเมินและแสดง |
|---|---|
|
="=Davolio" |
=Davolio |
|
="=3000" |
=3000 |
-
เมื่อกรองข้อมูลข้อความ Excel จะไม่แยกความแตกต่างระหว่างอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็ก แต่คุณสามารถใช้สูตรเพื่อค้นหาแบบตรงตามตัวพิมพ์ใหญ่-เล็กได้ สําหรับตัวอย่าง ให้ดูที่ การกรองข้อความโดยใช้การค้นหาแบบตรงตามตัวพิมพ์ใหญ่-เล็ก ในบทความนี้
ส่วนต่อไปนี้แสดงตัวอย่างของเกณฑ์ที่มีความซับซ้อน
หลายเกณฑ์ในหนึ่งคอลัมน์
ตรรกะบูลีน: (พนักงานขาย = "Davolio" OR พนักงานขาย = "Buchanan")
เมื่อต้องการค้นหาแถวต่างๆ ที่ตรงกับหลายเกณฑ์สำหรับหนึ่งคอลัมน์ ให้พิมพ์เกณฑ์ลงไปตรงด้านล่างของแต่ละเกณฑ์ในแต่ละแถวของช่วงเกณฑ์นั้น
ในช่วงข้อมูลต่อไปนี้ (A6:C10) ช่วงเกณฑ์ (B1:B3) แสดงแถวต่างๆ ที่มี "Davolio" หรือ "Buchanan" ในคอลัมน์พนักงานขาย (A8:C10)
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
2 |
=Davolio |
||
|
3 |
=Buchanan |
||
|
4 |
|||
|
5 |
|||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
10 |
พืชผัก |
Davolio |
$6544 |
หลายเกณฑ์ในหลายคอลัมน์ซึ่งเกณฑ์ทั้งหมดต้องเป็นจริง
ตรรกะบูลีน (ชนิด = "พืชผัก" AND ยอดขาย > 1000)
เมื่อต้องการค้นหาแถวต่างๆ ที่ตรงกับหลายเกณฑ์ในหลายคอลัมน์ ให้พิมพ์เกณฑ์ทั้งหมดในแถวเดียวกันกับช่วงเกณฑ์
ในช่วงข้อมูลต่อไปนี้ (A6:C10) ช่วงเกณฑ์ (A1:C2) แสดงแถวทั้งหมดที่มี "พืชผัก" ในคอลัมน์ชนิดและมีมูลค่ามากกว่า $1,000 ในคอลัมน์ยอดขาย (A9:C10)
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
2 |
=พืชผัก |
>1000 |
|
|
3 |
|||
|
4 |
|||
|
5 |
|||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
10 |
พืชผัก |
Davolio |
$6544 |
หลายเกณฑ์ในหลายคอลัมน์ซึ่งเกณฑ์ใดเกณฑ์หนึ่งต้องเป็นจริง
ตรรกะบูลีน: (ชนิด = "พืชผัก" OR พนักงานขาย = "Davolio")
เมื่อต้องการค้นหาแถวต่างๆ ที่ตรงกับหลายเกณฑ์ในหลายคอลัมน์ซึ่งเกณฑ์ใดเกณฑ์หนึ่งต้องเป็นจริง ให้พิมพ์เกณฑ์ในแต่ละแถวของช่วงเกณฑ์
ในช่วงข้อมูลต่อไปนี้ (A6:C10) ช่วงเกณฑ์ (A1:B3) แสดงแถวทั้งหมดที่มี "พืชผัก" ในคอลัมน์ชนิดหรือ "Davolio" ในคอลัมน์พนักงานขาย (A8:C10)
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
2 |
=พืชผัก |
||
|
3 |
=Davolio |
||
|
4 |
|||
|
5 |
|||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
10 |
พืชผัก |
Davolio |
$6544 |
ชุดของเกณฑ์หลายชุดโดยที่แต่ละชุดประกอบด้วยเกณฑ์สำหรับหลายคอลัมน์
ตรรกะบูลีน ( (พนักงานขาย = "Davolio AND ยอดขาย > 3000) OR (พนักงานขาย = "Buchanan" AND ยอดขาย > 1500) )
เมื่อต้องการค้นหาแถวต่างๆ ที่ตรงกับหลายชุดของเกณฑ์โดยที่แต่ละชุดของเกณฑ์ประกอบด้วยเกณฑ์สำหรับหลายคอลัมน์ ให้พิมพ์แต่ละชุดของเกณฑ์ในแต่ละแถว
ในช่วงข้อมูลต่อไปนี้ (A6:C10), ช่วงของเกณฑ์ (B1:C3) แสดงแถวต่างๆ ที่มีทั้ง "Davolio" ในคอลัมน์พนักงานขาย และมูลค่าที่มากกว่า 3,000 ดอลลาร์ในคอลัมน์ยอดขาย หรือแสดงแต่ละแถวที่มี "Buchanan" ในคอลัมน์พนักงานขาย และมูลค่าที่มากกว่า $1,500 ในคอลัมน์ยอดขาย (A9:C10)
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
2 |
=Davolio |
>3000 |
|
|
3 |
=Buchanan |
>1500 |
|
|
4 |
|||
|
5 |
|||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
10 |
พืชผัก |
Davolio |
$6544 |
หลายชุดของเกณฑ์โดยที่แต่ละชุดของเกณฑ์ประกอบด้วยเกณฑ์สำหรับหนึ่งคอลัมน์
ตรรกะบูลีน ( (ยอดขาย > 6000 AND ยอดขาย < 6500 ) OR (ยอดขาย < 500) )
เมื่อต้องการค้นหาแถวต่างๆ ที่ตรงกับหลายชุดของเกณฑ์โดยที่แต่ละชุดของเกณฑ์ประกอบด้วยเกณฑ์สำหรับหนึ่งคอลัมน์ ให้พิมพ์เกณฑ์ทั้งหมดในแถวเดียวกันกับช่วงเกณฑ์ ให้ใส่ส่วนหัวของคอลัมน์แบบเดียวกันไว้ในหลายๆ คอลัมน์
ในช่วงข้อมูลต่อไปนี้ (A6:C10) ช่วงของเกณฑ์ (C1:D3) แสดงแถวต่างๆ ที่มีมูลค่าระหว่าง 6,000 และ 6,500 และมูลค่าที่น้อยกว่า 500 ในคอลัมน์ยอดขาย (A8:C10)
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
ยอดขาย |
|
2 |
>6000 |
<6500 |
||
|
3 |
<500 |
|||
|
4 |
||||
|
5 |
||||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
|
10 |
พืชผัก |
Davolio |
$6544 |
เกณฑ์ที่จะค้นหาค่าข้อความที่มีอักขระบางตัวร่วมกันแต่ไม่ใช่ทั้งหมด
เมื่อต้องการค้นหาค่าข้อความที่มีอักขระบางตัวร่วมกันแต่ไม่ใช่ทั้งหมด ให้ทำตามขั้นตอนใดขั้นตอนหนึ่ง หรือมากกว่าดังต่อไปนี้
-
พิมพ์อักขระอย่างน้อยหนึ่งตัวโดยไม่มีเครื่องหมายเท่ากับ (=) เพื่อค้นหาแถวที่มีค่าข้อความในคอลัมน์ที่ขึ้นต้นด้วยอักขระเหล่านั้น ตัวอย่างเช่น ถ้าคุณพิมพ์ข้อความ Dav เป็นเงื่อนไข Excel จะค้นหา "Davolio," "David," และ "Davis"
-
ใช้อักขระตัวแทน
สามารถใช้อักขระตัวแทนต่อไปนี้เป็นเกณฑ์การเปรียบเทียบได้
|
ให้ใช้ |
เมื่อต้องการค้นหา |
|---|---|
|
? (เครื่องหมายคำถาม) |
อักขระตัวเดียวตัวใดก็ได้ตัวอย่างเช่น sm?th จะค้นหา "smith" และ "smyth" |
|
* (เครื่องหมายดอกจัน) |
อักขระจำนวนเท่าใดก็ได้ตัวอย่างเช่น *east จะค้นหา "Northeast" และ "Southeast" |
|
~ (เครื่องหมายตัวหนอน) ตามด้วย ?, * หรือ ~ |
เครื่องหมายคำถาม เครื่องหมายดอกจัน หรือเครื่องหมายตัวหนอนตัวอย่างเช่น fy91~? จะค้นหา "fy91?" |
ในช่วงข้อมูลต่อไปนี้ (A6:C10) ช่วงของเกณฑ์ (A1:B3) แสดงแถวต่างๆ ที่มี "เนื้อ" เป็นอักขระแรกในคอลัมน์ชนิด หรือแถวต่างๆ ที่มีอักขระตัวที่สองเป็น "ร" ในคอลัมน์พนักงานขาย (A7:C9)
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
2 |
เนื้อ |
||
|
3 |
=?u* |
||
|
4 |
|||
|
5 |
|||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
10 |
พืชผัก |
Davolio |
$6544 |
เกณฑ์ที่ได้สร้างไว้เป็นผลลัพธ์ของสูตร
คุณสามารถใช้ค่าจากการคํานวณที่เป็นผลลัพธ์ของ สูตร เป็นเกณฑ์ของคุณได้ โปรดอย่าลืมประเด็นสําคัญต่อไปนี้:
-
สูตรต้องประเมินค่าเป็น TRUE หรือ FALSE
-
เนื่องจากว่าคุณกำลังใช้สูตร ให้ใส่สูตรที่ต้องการ และอย่าพิมพ์นิพจน์ในรูปแบบต่อไปนี้
=''= รายการ ''
-
อย่าใช้ป้ายชื่อคอลัมน์สำหรับป้ายชื่อของเกณฑ์ ให้ปล่อยป้ายชื่อของเกณฑ์ว่างเปล่า หรือจะใช้ป้ายชื่อที่ไม่ใช่ป้ายชื่อคอลัมน์ในช่วงก็ได้ (ในตัวอย่างด้านล่าง ค่าเฉลี่ยจากการคำนวณ และการตรงกันพอดี)
ถ้าคุณใช้ป้ายชื่อคอลัมน์ในสูตรแทนที่จะเป็นการอ้างอิงเซลล์แบบสัมพัทธ์หรือชื่อช่วง Excel จะแสดงค่าความผิดพลาด เช่น #NAME? หรือ #VALUE! ในเซลล์ที่มีเงื่อนไข ในเซลล์ที่มีเงื่อนไข คุณอาจละเลยข้อผิดพลาดนี้ได้ เพราะไม่มีผลกระทบต่อวิธีการกรองช่วง
-
สูตรที่คุณใช้เป็นเกณฑ์จะต้องใช้ การอ้างอิงสัมพัทธ์ ในการอ้างอิงไปยังเซลล์ที่เกี่ยวข้องในแถวแรก (ในตัวอย่างด้านล่าง C7 และ A7)
-
การอ้างอิงอื่นๆ ในสูตรจะต้องเป็นการอ้างอิงแบบสัมบูรณ์
ส่วนย่อยต่อไปนี้จะแสดงตัวอย่างของเงื่อนไขที่ระบุซึ่งเป็นผลลัพธ์ของสูตร
การกรองค่าที่มากกว่าค่าเฉลี่ยของค่าทั้งหมดในช่วงข้อมูล
ในช่วงข้อมูลต่อไปนี้ (A6:D10) ช่วงเงื่อนไข (D1:D2) แสดงแถวต่างๆ ที่มีค่าในคอลัมน์ยอดขายมากกว่าค่าเฉลี่ยของค่ายอดขายทั้งหมด (C7:C10) ในสูตร "C7" อ้างอิงไปยังคอลัมน์ที่ถูกกรอง (C) ของแถวแรกของช่วงข้อมูล (7)
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
ค่าเฉลี่ยจากการคำนวณ |
|
2 |
=C7>AVERAGE($C$7:$C$10) |
|||
|
3 |
||||
|
4 |
||||
|
5 |
||||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
|
10 |
พืชผัก |
Davolio |
$6544 |
การกรองข้อความโดยใช้การค้นหาแบบตรงตามตัวพิมพ์ใหญ่-เล็ก
ในช่วงข้อมูล (A6:D10) ช่วงเงื่อนไข (D1:D2) แสดงแถวต่างๆ ที่มี "Produce" ในคอลัมน์ ชนิด โดยใช้ฟังก์ชัน EXACT เพื่อทําการค้นหาแบบตรงตามตัวพิมพ์ใหญ่-เล็ก (A10:C10) ในสูตร "A7" อ้างอิงไปยังคอลัมน์ที่ถูกกรอง (A) ของแถวแรกของช่วงข้อมูล (7)
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
ชนิด |
พนักงานขาย |
ยอดขาย |
การตรงกันพอดี |
|
2 |
=EXACT(A7, "Produce") |
|||
|
3 |
||||
|
4 |
||||
|
5 |
||||
|
6 |
ชนิด |
พนักงานขาย |
ยอดขาย |
|
|
7 |
เครื่องดื่ม |
Suyama |
$5122 |
|
|
8 |
เนื้อสัตว์ |
Davolio |
$450 |
|
|
9 |
พืชผัก |
Buchanan |
$6328 |
|
|
10 |
พืชผัก |
Davolio |
$6544 |
