1/63 ความรู้รอบคอม

วิธีใช้สูตรดึงชื่อใน EXCEL และตั้งค่าคีย์สูตรลัด

ขอบคุณเครดิตจาก เทพเอ็กเซล : Thep Excel 
เขียนโดย คุณ 

ศิระ เอกบุตร

เกร็ดน่ารู้ของ Excel

การอ้างอิงไปยังเวิร์กชีตหลายแผ่นได้โดยสะดวก    ถ้าคุณต้องการวิเคราะห์ข้อมูลในเซลล์หรือช่วงของเซลล์ที่อยู่ตำแหน่งเดียวกันบนเวิร์กชีตหลายๆ แผ่นในเวิร์กบุ๊กหนึ่ง ให้ใช้การอ้างอิงสามมิติ การอ้างอิงสามมิติประกอบด้วยการอ้างอิงเซลล์หรือช่วงเซลล์ที่นำหน้าด้วยช่วงของชื่อเวิร์กชีต Excel จะใช้เวิร์กชีตใดก็ตามที่มีการจัดเก็บอยู่ระหว่างชื่อแรกและชื่อสุดท้ายของการอ้างอิง ตัวอย่างเช่น =SUM(Sheet2:Sheet13!B5) จะบวกทุกค่าในเซลล์ B5 บนทุกเวิร์กชีตตั้งแต่ Sheet 2 ถึง Sheet 13

• คุณสามารถใช้การอ้างอิงสามมิติเพื่ออ้างอิงไปยังเซลล์บนเวิร์กชีตอื่น กำหนดชื่อ และสร้างสูตรโดยใช้ฟังก์ชันต่อไปนี้ ได้แก่ SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA และ VARPA
• การอ้างอิงสามมิติ ใช้ไม่ได้ในสูตรอาร์เรย์
• การอ้างอิงสามมิติ ใช้ไม่ได้กับ ตัวดำเนินการอินเตอร์เซกชัน (ช่องว่างเดี่ยว) หรือในสูตรที่ใช้อินเทอร์เซกชันโดยนัย

สิ่งที่เกิดขึ้นเมื่อคุณย้าย คัดลอก แทรก หรือลบเวิร์กชีต    ตัวอย่างต่อไปนี้อธิบายสิ่งที่เกิดขึ้นเมื่อคุณย้าย คัดลอก แทรก หรือลบเวิร์กชีตที่อยู่ในการอ้างอิงสามมิติ ตัวอย่างนี้ใช้สูตร =SUM(Sheet2:Sheet6!A2:A5) เพื่อบวกค่าของเซลล์ A2 ถึง A5 บนเวิร์กชีตที่ 2 ถึง 6

• แทรกหรือคัดลอก    ถ้าคุณแทรกหรือคัดลอกแผ่นงานระหว่าง Sheet2 ถึง Sheet6 (จุดสิ้นสุดในตัวอย่างนี้) Excel จะรวมค่าทั้งหมดในเซลล์ A2 ถึง A5 จากแผ่นงานที่เพิ่มเข้ามาไว้ในการคำนวณด้วย
• ลบ    ถ้าคุณลบแผ่นงานที่อยู่ระหว่าง Sheet2 ถึง Sheet6 ออก Excel จะเอาค่าจากแผ่นงานเหล่านั้นออกจากการคำนวณ
• ย้าย    ถ้าคุณย้ายแผ่นงานที่อยู่ระหว่าง Sheet2 ถึง Sheet6 ไปยังตำแหน่งที่ตั้งซึ่งอยู่นอกช่วงแผ่นงานที่อ้างอิงถึง Excel จะเอาค่าจากแผ่นงานเหล่านั้นออกจากการคำนวณ
• ย้ายจุดสิ้นสุด    ถ้าคุณย้าย Sheet2 หรือ Sheet6 ไปที่ตำแหน่งที่ตั้งอื่นภายในเวิร์กบุ๊กเดียวกัน Excel จะปรับการคำนวณให้เข้ากับช่วงใหม่ของแผ่นงานที่อยู่ระหว่างแผ่นงานทั้งสอง
• ลบจุดสิ้นสุด    ถ้าคุณลบ Sheet2 หรือ Sheet6 Excel จะปรับการคำนวณให้เข้ากับช่วงของแผ่นงานที่อยู่ระหว่างแผ่นงานทั้งสอง

ขอบคุณเกร็ดน่ารู้ดีๆ จาก https://support.office.com/th-th/article/%E0%B8%A0%E0%B8%B2%E0%B8%9E%E0%B8%A3%E0%B8%A7%E0%B8%A1%E0%B8%82%E0%B8%AD%E0%B8%87%E0%B8%AA%E0%B8%B9%E0%B8%95%E0%B8%A3%E0%B9%83%E0%B8%99-excel-ecfdc708-9162-49e8-b993-c311f47ca173

การทำการอ้างอิงไปยังเซลล์หรือช่วงของเซลล์บนเวิร์กชีตอื่นในเวิร์กบุ๊กเดียวกัน

ในตัวอย่างต่อไปนี้ ฟังก์ชัน AVERAGE จะคำนวณค่าเฉลี่ยสำหรับช่วง B1:B10 บนเวิร์กชีตที่ตั้งชื่อว่า การตลาด ในเวิร์กบุ๊กเดียวกัน

1. อ้างอิงไปยังเวิร์กชีตชื่อ Marketing

2. อ้างอิงไปยังช่วงของเซลล์จาก B1 ถึง B10

3. เครื่องหมายอัศเจรีย์ (!) จะแยกการอ้างอิงเวิร์กชีตจากการอ้างอิงช่วงของเซลล์

หมายเหตุ: ถ้าเวิร์กชีตที่อ้างอิงมีช่องว่างหรือตัวเลขอยู่ในชื่อ คุณจำเป็นต้องเพิ่มเครื่องหมายอัญประกาศเดี่ยวก่อนและหลังชื่อเวิร์กชีต เช่น ='123'!A1 หรือ ='January Revenue'!A1

ใส่เครื่องหมาย $ ในสูตร Excel

ทำไมต้องใส่เครื่องหมาย $ ล่ะ?

เพราะปกติว่าเขียนสูตรที่มี Cell Reference แล้วเรา Copy Cell ช่องนั้นไป Paste ที่อื่น Cell Reference มันจะเลื่อนไปตามทิศทางของการ Copy

เราจึงต้องมีการใส่ $ ลงไปที่ Cell Reference เพื่อไม่ให้มันเลื่อน ซึ่งสามารถเลือกที่ Cell Reference ในสูตรแล้วกดคีย์ F4 

ใส่เครื่องหมาย $ ได้กี่แบบ?

ใส่ได้ทั้งหมด 4 แบบ ตามรูป ซึ่งสามารถเปลี่ยนรูปแบบได้เรื่อยๆ โดยการกดปุ่ม F4 เวลาเขียนสูตรแล้ว Cursor อยู่ที่ Cell Reference นั้นๆ

หลักการง่ายๆ แค่ค่อยๆ ดูทีละ Cell Reference

ขอบคุณความรู้ดีๆ จาก 

https://www.thepexcel.com/fix-cell-reference/

เขียนโดย 

Sira Ekabut

• Microsoft Office Specialist : Excel ระดับ Expert
• ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views +
• หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!
• วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

การคำนวณอายุใน Excel

ขอบพระคุณ เครดิตจาก
คุณพิมานแมน http://oknation.nationtv.tv/blog/CharoenPD/2015/04/02/entry-1

Excel : การคำนวณอายุ

การแสดงวัน/เดือน/ปี แบบไทย

        Excel จะคำนวณวันเวลาแบบตะวันตกเท่านั้น  จึงต้องต้องแปลง พ.ศ. ให้เป็น ค.ศ.
โดยเอาปี พ.ศ. - 543
ใช้สูตร =B1-543

เมื่อได้ปี ค.ศ.แล้ว คลิกเซลล์ที่ต้องการ พิมพ์วันที่/เดือน/ปีในแถบสูตร เช่น 8/25/1959 (เดือน/วันที่/ค.ศ.)

จากนั้นจัดรูปแบบเซลล์ให้ แสดงวันที่แบบไทย
โดยคลิกขวาที่เซลล์ > การจัดรูปแบบเซลล์ > ตำแหน่งที่ตั้ง > ชนิดปฏิทิน 

ผลที่ได้ในเซลล์จะเป็น แบบไทย  แต่ในแถบสูตรเป็นแบบ ตะวันตก
เมื่อแก้ไขวันที่แบบตะวันตกในแถบสูตร  วันที่ในเซลล์จะเปลี่ยนตาม  แต่เป็นแบบไทย

ฟังก์ชั่น =TODAY() และ =NOW()
           ใน Excel จะมีฟังก์ชั่น =TODAY()  แสดงวันที่ปัจจุบัน จะเปลี่ยนเองโดยอัตโนมัติทุกๆเที่ยงคืน
ฟังก์ชั่น =TODAY() สามารถนำไปประยุกต์ใช้ในหลายสถานการณ์  เช่น ใบเสร็จรับเงิน หรือ เอกสารที่ต้องการแสดงวันที่จัดทำเป็นปัจจุบัน

           การให้เซลล์แสดงวันที่เป็น แบบไทย  ใช้วิธีการดังได้กล่าวไปแล้วในตอนต้น  นอกจาก TODAY() แล้วยังมีฟังก์ชั่น =NOW() ซึ่งจะแสดงเวลาหรือวันที่ หรือทั้ง 2 อย่างพร้อมกัน


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

1.การคำนวณอายุเป็น  ปี

               A =YEAR(TODAY())-YEAR(B2)&" ปี"

               B =ROUND(((NOW()-B3)/365),0)&" ปี"


2.การคำนวณอายุเป็น  ปี/เดือน/วัน

               A =DATEDIF(B2,TODAY(),"Y")&" ปี "&DATEDIF(B2,TODAY(),"YM")&" เดือน "&DATEDIF(B2,TODAY(),"MD")+1&" วัน"

               B =DATEDIF(B3,TODAY(),"Y")&" ปี "&DATEDIF(B3,TODAY(),"YM")&" เดือน "&DATEDIF(B3,TODAY(),"MD")&" วัน"

มาฝึกภาษาอังกฤษกับบาร์บี้กันเถอะ

คลิ๊กที่ลิ๊งข้างล่างได้เลยค่ะ มี 3 ไฟล์ เลือกกันได้เลยตามความชอบ

https://drive.google.com/drive/u/0/folders/1qmqUbsRAygJfwWzE_M50eW3lVvNSV1zO