การคำนวณอีสเตอร์

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

  
  =DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6 

เพียงเพื่อความสนุกผมคำนวณวันที่ของวันอีสเตอร์สำหรับ 300 ปีจาก 1900 ผ่าน 2199 แล้วฉันจะสร้างตารางเดือยและการจัดกลุ่มวันที่ตามวัน และแล้วกราฟเดือย:

ในระหว่างนี้ระยะเวลา 300 ปี, วันที่พบบ่อยที่สุดสำหรับอีสเตอร์เป็น 31 มีนาคม (มันเกิดขึ้น 13 ครั้งเกี่ยวกับข้อมูลที่) ร่วมกันอย่างน้อยคือ 24 มีนาคม (เพียงหนึ่งเกิดขึ้น) ฉันยังได้เรียนรู้ว่าครั้งต่อไปวันอีสเตอร์ตรงกับวัน April Fool 's จะอยู่ใน 2018 

Read More

สูตรการดำเนินการของการคำนวณวันเดือน

 ไฟล์สหายสามารถใช้ได้: คลิกที่นี่เพื่อดาวน์โหลด

หลายเหตุการณ์ที่กำหนดไว้สำหรับการเกิดขึ้นโดยเฉพาะอย่างยิ่งในวันภายในเดือน ตัวอย่างเช่นอาจจะ payday วันศุกร์สุดท้ายของทุกเดือน หรือที่ประชุมอาจจะมีการกำหนดไว้สำหรับทุกวินาทีจันทร์ของเดือน

Excel ไม่ได้มีฟังก์ชันที่สามารถคำนวณประเภทนี้วัน แต่ก็เป็นไปได้ในการสร้างสูตร ในภาพด้านล่างสูตรในเซลล์ D4 คำนวณวันที่อยู่กับพารามิเตอร์ในคอลัมน์ C. สูตรใน D4 คือ:

 =DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1))))

สูตรนี้ไม่ถูกต้องเสมอ แต่ ถ้าคุณระบุจำนวนวันที่ไม่อยู่ (เช่น 6 วันศุกร์) ก็จะส่งกลับวันที่ในเดือนถัดไป

D6 เซลล์ที่มีสูตรการปรับเปลี่ยนที่จะแสดง "(ไม่มี)" ถ้าวันที่ไม่ได้อยู่ในเดือนที่ระบุไว้ สูตรนี้เป็นอย่างมากอีกต่อไป:

 =IF(MONTH(DATE(C3,C4,1+((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+
 (C5-WEEKDAY(DATE(C3,C4,1)))))<>C4,"(none)",DATE(C3,C4,1+
 ((C6-(C5>=WEEKDAY(DATE(C3,C4,1))))*7)+(C5-WEEKDAY(DATE(C3,C4,1)))))

 

ในบางกรณีคุณอาจจำเป็นต้องตรวจสอบเกิดขึ้นครั้งล่าสุดของวันในเดือนใด การคำนวณนี้ต้องใช้สูตรที่แตกต่างกัน (ดูรูปด้านล่าง):

 =DATE(C9,C10+1,1)-1+IF(C11>WEEKDAY(DATE(C9,C10+1,1)-1),
 C11-WEEKDAY(DATE(C9,C10+1,1)-1)-7,C11-WEEKDAY(DATE(C9,C10+1,1)-1))

 

ในภาพนี้สูตรในเซลล์ D10 จะแสดงวันที่ของวันศุกร์ในเดือนมีนาคม 2008

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

Read More

เป็นคำเฉพาะที่อยู่ในสตริงข้อความ?

ที่นี่ฟังก์ชัน VBA ที่อาจเป็นประโยชน์ในบางสถานการณ์ที่คล้ายกัน

ฟังก์ชั่น ExactWordInString ส่งกลับ True ถ้าคำที่ระบุอยู่ในสตริงข้อความ

คุณอาจจะคิดว่าฟังก์ชั่นนี้เป็นเพียงรูปแบบใน Excel ของฟังก์ชัน FIND หรือฟังก์ชัน Instr VBA ของ มีความแตกต่างที่ลึกซึ้งมาก ฟังก์ชั่น ExactWordInString จะมองหาคำที่สมบูรณ์ - ไม่ข้อความที่อาจเป็นส่วนหนึ่งของคำที่แตกต่างกัน

ตัวอย่างในรูปที่ประกอบควรชี้แจงว่าฟังก์ชันนี้จะทำงานได้ เซลล์ C2 มีสูตรนี้ซึ่งถูกคัดลอกไปยังเซลล์ที่อยู่ด้านล่าง:

  = ExactWordInString (A2, B2) 

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

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

เมื่อต้องการใช้ฟังก์ชันนี้ในสูตรเพียงคัดลอกและวางในโมดูล VBA ในสมุดงานของคุณ

Function ExactWordInString(Text As String, Word As String) As Boolean ' Returns TRUE if Word is contained in Text as an exact word match Dim i As Long Const Space As String = " " Text = UCase(Text) ' Replace non-text characters with a space For i = 0 To 64 Text = Replace(Text, Chr(i), Space) Next i For i = 91 To 255 Text = Replace(Text, Chr(i), Space) Next i ' Add initial and final space to Text & Word Text = Space & Text & Space Word = UCase(Space & Word & Space) ExactWordInString = InStr(Text, Word) <> 0 End Function

* อัพเดท *

Excel MVP ริค Rothstein ส่งฉันฟังก์ชั่นที่ง่ายมากที่ผลิตเดียวกันผล ในความเป็นจริงนั้นจะใช้เพียงหนึ่งคำสั่ง:

Function ExactWordInString(Text As String, Word As String) As Boolean
  ExactWordInString = " " & UCase(Text) & " " Like "*[!AZ]" & UCase(Word) & "[!AZ]*"
 End Function

Read More

การเปลี่ยนค่าเริ่มต้นของเซลล์ที่จัดรูปแบบความคิดเห็น

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

เมื่อต้องการเปลี่ยนการจัดรูปแบบเริ่มต้นของความเห็นมือถือของคุณ (ดังนั้นคุณไม่ต้องทำซ้ำขั้นตอนที่ในแต่ละครั้ง), คุณต้องออกไปข้างนอก Excel และใช้จอแสดงผลของ Windows กล่องโต้ตอบการตั้งค่า โดย Excel จะใช้การจัดรูปแบบที่กำหนดไว้สำหรับคำแนะนำเครื่องมือของ Windows '(ข้อความที่ปรากฏขึ้นเมื่อ hovers ตัวชี้ของคุณที่อยู่ใกล้ปุ่มแถบเครื่องมือ)

คุณสามารถเข้าถึงแสดงกล่องโต้ตอบการตั้งค่าจาก Control Panel หรือคุณสามารถคลิกขวาที่เดสก์ทอปและเลือก Properties เข้าแท็บ Appearance ของกล่องโต้ตอบ Display Properties และเลือกรายการที่คำแนะนำเครื่องมือจากนั้นคุณสามารถปรับเปลี่ยนแบบอักษรขนาดตัวอักษรสีพื้นหลัง, สีพื้นหน้าและคุณลักษณะหนาและตัวเอียง

หมายเหตุ: หากคุณใช้ Windows XP คุณต้องคลิกปุ่มขั้นสูงในแท็บ Appearance ของกล่องโต้ตอบ Display Properties นี้นำขึ้นลักษณะกล่องโต้ตอบขั้นสูงซึ่งคุณสามารถทำการเปลี่ยนแปลง

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

Read More

โดยใช้รูปแบบตามเงื่อนไข

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

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

เมื่อต้องการใช้รูปแบบตามเงื่อนไขให้เลือกช่วง A2: C15 และเลือกรูปแบบการจัดรูปแบบตามเงื่อนไขการจัดรูปแบบกล่องตามเงื่อนไขโต้ตอบจะปรากฏขึ้นพร้อมกับสองกล่องใส่. ในกล่องแรกให้เลือกสูตรคือกดแท็บและป้อนสูตรต่อไปนี้:

  C2 = $ B2> $ 

คลิกรูปแบบและเลือกรูปแบบที่จะแยกแยะเซลล์ (เช่นใช้การแรเงาพื้นหลัง) คลิกที่ OK และการจัดรูปแบบจะถูกนำไปใช้

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

  = $ C7> $ B7 

Read More

การทำงานกับเศษส่วน

บางชนิดของข้อมูลเช่นคำพูดที่ตลาดหุ้นปกติแสดงเป็นเศษส่วนทศนิยมไม่ ในการป้อนเศษส่วนใน Excel พิมพ์ตัวเลขทั้งหมด (หรือจํานวนเต็ม) ตามด้วยช่องว่างแล้วพิมพ์เศษส่วนโดยใช้เครื่องหมายทับ (เช่น 5/8) ถ้าคุณพิมพ์เพียงเศษเสี้ยว, Excel อาจตีความว่าเป็นวันที่ (ดังนั้นจึงอาจอ่าน 5/8 เป็น 8 พฤษภาคม) เพื่อหลีกเลี่ยงการตีความผิดนี้ให้ใส่ 0, พื้นที่และจากนั้นเศษ

เมื่อคุณป้อนค่าเศษส่วน, Excel อัตโนมัติจะใช้รูปแบบตัวเลขเศษส่วนที่ลดลงในส่วนที่เล็กที่สุดที่เป็นไปได้ ตัวอย่างเช่นถ้าคุณใส่ 16 2/8, Excel จะแสดงตัวเลขเป็น 16 1/4 ในบางกรณี แต่คุณจะต้องการเศษส่วนที่จะใช้ตัวหารร่วม ตัวอย่างเช่นคุณอาจต้องการค่า 16 2/8 ที่จะแสดงเป็น 16 4/16 เพื่อให้ได้ผลนี้ให้เลือกเซลล์ของคุณและเลือกรูปแบบเซลล์. แล้วเลือกแท็บตัวเลขและเลือกเศษส่วนจากรายการประเภท สุดท้ายเลือกรูปแบบตัวเลขที่ต้องการจากรายการชนิด

แผ่นงานด้านล่างจะแสดงตัวอย่างบางส่วนของตัวเลขที่แสดงเป็นเศษส่วน คอลัมน์ B แสดงให้เห็นตัวเลขที่ผลิตโดยใช้การจัดรูปแบบเริ่มต้นของ Excel คอลัมน์ C มีค่าเดียวกันรูปแบบเป็น 16ths

นอกจากนี้คุณยังสามารถแสดงข้อมูลบางส่วนโดยใช้จุดทศนิยม ตัวอย่างเช่นหมายเลข 9 4/​​16 จะปรากฏเป็น 9.04 ที่นี่ตัวเลขทางขวาของจุดทศนิยมแทน 16ths เมื่อต้องการแสดงค่าในรูปแบบนี้ใช้ Excel ของ DOLLARFR () ฟังก์ชัน มันใช้ได้เฉพาะเมื่อ Analysis ToolPak ให้มีการติดตั้ง (เลือกเครื่องมือ Add-ins จะติดตั้ง) DOLLARFR () ฟังก์ชันใช้เวลาสองขัดแย้ง: หมายเลขและจำนวนเต็มสำหรับตัวหาร สูตร = DOLLARFR (9.25,16) เ​​ช่นการส่งคืน 9.04

ฟังก์ชั่นนี้ยังเป็นประโยชน์สำหรับข้อมูลที่ nondollar ดังนั้นถ้าคุณทำงานกับเท้าและนิ้วที่คุณสามารถเป็นตัวแทน 11.5 ฟุตเป็น 11.06 (11 ฟุต 6 นิ้ว) โดยการใช้สูตรนี้:

  = DOLLARFR (11.5,12) 

ค่านั้นจะปรากฏเป็น "11 [ฟุต] 6 [นิ้ว]."

DOLLARFR () ฟังก์ชันสำหรับการแสดงผลเท่านั้น คุณไม่สามารถใช้ค่าที่มันจะส่งกลับในการคำนวณอื่น ๆ หรือในแผนภูมิ เพื่อทำการคำนวณค่าดังกล่าว reconvert ให้เป็นค่าทศนิยมโดยใช้ DOLLARDE () ฟังก์ชัน (ยังเป็นส่วนหนึ่งของการวิเคราะห์ ToolPak) 

Read More

การลบหรือการหลีกเลี่ยงการเชื่อมโยงหลายมิติอัตโนมัติ

คุณอาจจะได้ค้นพบว่า Excel 2000 (และรุ่นที่ใหม่กว่า) สนับสนุนเชื่อมโยงหลายมิติเซลล์อัตโนมัติ เมื่อใดก็ตามที่คุณพิมพ์อะไรบางอย่างที่คล้ายกับ URL หรือ e-mail address ลงในเซลล์คุณลักษณะนี้จะแปลงข้อความเป็นเชื่อมโยงหลายมิติคลิกได้แต่ถ้าคุณไม่ต้องการที่จะสร้างเชื่อมโยงหลายมิติ?

ถ้าคุณใช้ Excel 2000 คุณจะออกจากโชค ไม่มีทางที่จะเปิดการทำงานนี้คุณสมบัติที่น่ารำคาญอาจปิดไม่เป็น แต่คุณสามารถ แต่แทนที่มัน ถ้า Excel จะสร้างเชื่อมโยงหลายมิติจากการเข้ามือถือของคุณให้คลิกที่ปุ่ม Undo (หรือกด Ctrl-Z) เพื่อเรียกคืนเนื้อหาของเซลล์เป็นข้อความปกติ หรือคุณสามารถนำหน้ารายการเซลล์กับเครื่องหมายอัญประกาศเดี่ยว

หมายเหตุ: หากคุณกำลังใช้ Excel 2002 หรือในภายหลังคุณสามารถเปิดเชื่อมโยงหลายมิติโดยอัตโนมัติหรือปิดในกล่องโต้ตอบแก้ไขอัตโนมัติ

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

เมื่อต้องการสร้างแมโครให้กด Alt-F11 เพื่อเปิดใช้งานแก้ไข Visual Basic ให้เลือกแทรกโมดูลการแทรกโมดูล VBA ใหม่ในโครงการของคุณและจากนั้นป้อนรหัสต่อไปนี้:

 Sub ZapHyperlinks() 
     Cells.Hyperlinks.Delete
 End Sub 

เมื่อคุณรันแมโคร ZapHyperlinks เชื่อมโยงหลายมิติทั้งหมดบนแผ่นงานที่ใช้งานจะถูกลบออกและแทนที่ด้วยข้อความปกติ 

Read More