วันจันทร์ที่ 23 เมษายน พ.ศ. 2555

การป้องกัน VBA Code ของ MS Access

ปัญหา

MS Access 2007/2010 เมื่อ compile โดยจัดทำเป็น accde แล้วมีปัญหาเมื่อนำไปใช้กับ Windows ที่ต่างรุ่นกัน เช่นทำจาก Vista / Office 2010 เมื่อนำไปใช้กับ Windows 7 / Office 2010 หรือ Windows XP / Office 2010 มีปัญหา เปิดไม่ได้ บอกว่าไม่รู้จัก ทั้ง ๆ ที่เป็น Office 2010 ด้วยกันเอง

การแก้ไข

ไม่ต้อง compile เป็น accde แต่ ยังคงใช้เป็น accdb เหมือนเดิม แต่ใส่รหัสที่โค้ด เพื่อไม่ให้เปิดดูได้ เพราะถ้าเปิดได้ และแก้ไขโค้ด ก็จะทำให้โปรแกรมที่เขียน ทำงานไม่ได้ตามที่ต้องการ หรืออาจจะไม่ทำงานเลย

วิธีการใส่รหัสเพื่อไม่ให้เปิดดูโค้ด ทำดังนี้
  1. เปิดหน้าจอ VBA (กด Atl + F11)
  2. ไปที่ Tools > Database Properties ...จะเปิดหน้าจอข้างล่าง

  3. คลิกที่แถบ Protection
  4. พิมพ์รหัสตามต้องการ
  5. เมื่อปิดและจะเข้าไปแก้ไขโค้ด ต้องใส่รหัส

วันศุกร์ที่ 20 เมษายน พ.ศ. 2555

stroke โรคหลอดเลือดสมอง อัมพาต อัมพฤกษ์

Stroke มีชื่อเรียกต่าง ๆ กัน เช่น โรคหลอดเลือดสมอง อัมพาต อัมพฤกษ์ เส้นเลือดอุดตัน เป็นต้น
Stroke เป็นโรคพบได้บ่อยโรคหนึ่ง ในประเทศที่เจริญแล้ว โรคนี้เป็นสาเหตุการเสียชีวิตเป็นอันดับสองรองจากโรคหัวใจ เป็นโรคพบได้บ่อยในอายุตั้งแต่ 45 ปีขึ้นไป (95%ของผู้ป่วยทั้งหมด) โดยประมาณสองในสามของผู้ป่วยมีอายุมากกว่า 65 ปี ทั้งนี้ พบในผู้ชายบ่อยกว่าในผู้หญิงประมาณ 1.5 เท่า

Stroke เกิดจากอะไร?


Stroke มีสาเหตุเกิดจาก สมองขาดเลือดทันทีภายในระยะ เวลาเป็นนาทีหรือ ชั่วโมง ซึ่งเกิดได้จาก 2 สาเหตุสำคัญ คือ
  1. หลอดเลือดแดงสมองอุดตัน (ischemic stroke)
  2. หลอดเลือดแดงสมองแตก (hemorrhagic stroke)

โรคอัมพาต โรคอัมพฤกษ์มีอาการอย่างไร?

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

ป้องกันโรคอัมพาต โรคอัมพฤกษ์ได้อย่างไร?

การป้องกัน โรคอัมพาต โรคอัมพฤกษ์ คือ การป้องกัน/หลีกเลี่ยงปัจจัยเสี่ยงดังกล่าวแล้ว ที่สำคัญ คือ
  • ป้องกันโรคหลอดเลือดแดงแข็ง ด้วยการไม่สูบบุหรี่ เลิกบุหรี่ และจำกัดอาหารไขมัน
  • ป้องกันโรคความดันโลหิตสูง ไขมันในเลือดสูง และโรคเบาหวาน โดย กินอาหารมีประโยชน์ห้าหมู่ ทุกวัน ในปริมาณพอควรที่ไม่ทำให้เกิดโรคอ้วน จำกัดอาหารไขมัน และอาหารแป้งและน้ำตาล
  • ออกกำลังกายสม่ำเสมอตามควรกับสุขภาพทุกวัน ในคนปกติ ประมาณ 30 นาทีต่อวัน
  • ตรวจสุขภาพประจำปี เพื่อตรวจ ความดันโลหิต โรคเบาหวาน โรคไขมันในเลือด และโรคหัวใจ เพื่อให้การรักษา ควบคุมโรคแต่เนิ่นๆ
  • กินยาเพื่อป้องกันการเกิดลิ่มเลือดตามแพทย์แนะนำ




http://www.nlm.nih.gov/medlineplus/stroke.html
http://haamor.com/knowledge/%E0%B8%A7%E0%B8%B4%E0%B8%81%E0%B8%B4%E0%B9%82%E0%B8%A3%E0%B8%84/article/%E0%B8%AD%E0%B8%B1%E0%B8%A1%E0%B8%9E%E0%B8%B2%E0%B8%95/ (ศาสตราจารย์เกียรติคุณ พญ. พวงทอง ไกรพิบูลย์ )

วันจันทร์ที่ 16 เมษายน พ.ศ. 2555

การย้ายแถว ใน Excel

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

วิธีการ
  1. เลือกทั้งแถว
  2. คลิกขวาที่เส้น
  3. ลากไปวางในตำแหน่งแถวที่ต้องการ
  4. ปล่อยเมาส์ จะมีคำถาม ให้เลือก Shift Down and Move
  5. จะได้ข้อมูลใหม่ ดังภาพ ซึ่งจะเห็นว่า แถวที่ 3 มาแทรกอยู่ระหว่างแถวที่ 9 และ แถวที่ 10 โดยที่ข้อมูลเดิมยังอยู่ครบ


วันเสาร์ที่ 14 เมษายน พ.ศ. 2555

ไฟโตนิวเทรียนท์ (phytonutrients) สารอาหารที่สำคัญจากพืช

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

เมื่อเรากินพืชผักจึงได้รับ ไฟโตนิวเทรียนท์ ด้วย ไฟโตนิวเทรียนท์ ในร่างกายของเรา เป็นสิ่งจำเป็นนอกเหนือไปจากสารอาหารทั้ง 5 หมู่ ที่รู้จักกันดีอยู่แล้ว ไฟโตนิวเทรียนท์ ช่วยซ่อมแซมเซลล์ ช่วยลดคอเลสเตอรอล ช่วยเสริมสร้างความต้านทาน และเป็นตัวต้านอนุมูลอิสระ นอกจากนี้ยังช่วยป้องกัน รักษาโรคสำคัญ เช่น มะเร็ง เบาหวาน และโรคเกี่ยวกับหัวใจและหลอดเลือด (cardiovascular disease)

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

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



อ้างอิง
http://www.glyconutrientsreference.com/whatarephytonutrients.php
http://members.cruzio.com/~dolson/healthtips/colors.html

วันอังคารที่ 10 เมษายน พ.ศ. 2555

การใช้ if ตรวจสอบหลายเงื่อนไข ใน Excel

การใช้ฟังก์ชัน If ของ Excel ตรวจสอบหลายเงื่อนไข  มีรูปแบบ ดังนี้            If( condition, value_if_true, value_if_false )

เราสามารถตรวจสอบหลายเงื่อนไข โดยการเพิ่ม if เข้าในส่วนที่ไม่เป็นไปตามเงื่อนไข เช่น
            If( condition, value_if_true, If( condition, value_if_true, value_if_false ) )
ตัวอย่าง
            =IF(MID(A1,1,3)="นาย","นาย",IF(MID(A1,1,3)="นาง","นาง",""))

ถ้ามีการตรวจสอบหลายครั้ง การใช้ฟังก์ชัน if จะไม่ค่อยสะดวก เพราะตรวจสอบยากมาก เนื่องจากข้อความที่เขียนจะยาวมาก ต้องตรวจสอบหลายครั้งกว่าที่จะทำได้ถูกต้อง

ทางออกอย่างหนึ่งที่ง่ายกว่า คือการใช้ VBA ช่วย โดยสร้างฟังก์ชันขึ้นใช้เอง และใช้ if หรือ Select caseตรวจสอบเงื่อนไข จะทำให้สะดวกกว่า เช่น
    if (เงื่อนไขที่ 1) then
        ..............................
    elseif (เงื่อนไขที่ 2) then
        ..............................
    elseif (เงื่อนไขที่ 3) then
        ...............................

    elseif (เงื่อนไขที่ 4) then
        ...............................
    else
        ...............................
    end if

ตัวอย่าง

ต้องการตรวจสอบและแยกคำนำหน้าชื่อออกไปอีกเซลล์ต่างหาก คำนำหน้าชื่อที่ต้องการตรวจสอบมีหลายคำ ได้แก่ นาย นาง นางสาว เด็กชาย เด็กหญิง ด.ช. และ ด.ญ.

วิธีการ
  1. เปิดหน้าจอ VBA โดยกด Alt + F11
  2. เพิ่ม Module โดยไปที่ Insert > Module
  3. คลิกเลือก Module
  4. คัดลอก ข้อความต่อไปนี้ และ Paste ลงในหน้าจอ

    Function getTitle(fullName As String) As String
    Dim thisTitle As String
        If Mid(fullName, 1, 3) = "นาย" Then
            getTitle = "นาย"
        ElseIf Mid(fullName, 1, 3) = "นาง" Then
            getTitle = "นาง"
        ElseIf Mid(fullName, 1, 6) = "นางสาว" Then
            getTitle = "นางสาว"
        ElseIf Mid(fullName, 1, 4) = "ด.ช." Then
            getTitle = "ด.ช."
        ElseIf Mid(fullName, 1, 5) = "ด.ญ." Then
            getTitle = "ด.ญ."
        ElseIf Mid(fullName, 1, 7) = "เด็กชาย" Then
            getTitle = "เด็กชาย"
        ElseIf Mid(fullName, 1, 8) = "เด็กหญิง" Then
            getTitle = "เด็กหญิง"
        Else
            getTitle = blank
        End If
    End Function


วิธีการเรียกใช้งาน ฟังก์ชัน ให้ดูเรื่อง การแยกคำนำหน้าชื่อ

    วันจันทร์ที่ 9 เมษายน พ.ศ. 2555

    การแยกคำนำหน้าชื่อ ชื่อ และนามสกุล ไว้คนละเซลล์ ด้วย Excel VBA

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

    อย่างนี้ จำเป็นต้องใช้ VBA ช่วย จะสามารถทำได้สะดวกมากกว่า อย่างไรก็ตาม ต้องมีเงื่อนไขว่า ชื่อและนามสกุลต้องเขียนแยกกัน โดยมีช่องว่าง เพราะใน VBA ข้างล่างนี้ ใช้ช่องว่างเป็นหลักในการแยกชื่อ และนามสกุล

    ตัวอย่าง

    ให้ท่านแยกคำนำหน้าชื่อ ชื่อ และนามสกุล จากข้อมูลข้างล่างนี้ ออกเป็นคนละเซลล์กัน


    วิธีการ มีดังนี้
    1. เปิดหน้าต่าง VBA ของ Excel โดยกดปุ่ม Alt + F11
    2. ไปที่เมนู Insert > Module (แทรก > โมดูล)
    3. คลิกที่ Module1 และพิมพ์โค้ดข้างล่างนี้

      Function getTitle(thisFullName As String) As String
      Dim Titles As Variant
      Dim myCount As Integer

      ' สร้างตัวแปร Array ชื่อ Titles เพื่อเก็บคำนำหน้าชื่อ สามารถเพิ่มได้อีกไม่จำกัด
      ' ต้องให้ นางสาว มาก่อน นาง มิฉะนั้นจะตรวจสอบนางสาวไม่ได้

      Titles = Array("นาย", "นางสาว", "นาง", "เด็กชาย", "ด.ช.", "ว่าที่ ร.ต.")
      For myCount = 0 To UBound(Titles)
          getTitle = Left(thisFullName, Len(Titles(myCount)))
            If Titles(myCount) = getTitle Then
              Exit Function
              Else
              getTitle = blank
            End If
      Next myCount
      End Function

      Function getFirstName(thisFullName As String) As String
      Dim thisTitle As String
      Dim firstName As String
      Dim spacePos As Integer
      thisTitle = getTitle(thisFullName)
      spacePos = InStr(thisFullName, " ")
      If spacePos = 0 Then 'ถ้าไม่มีช่องว่าง
          firstName = Trim(Mid(thisFullName, Len(thisTitle) + 1, Len(thisFullName) - Len(thisTitle)))
      Else
          firstName = Trim(Mid(thisFullName, Len(thisTitle) + 1, InStr(thisFullName, " ") - Len(thisTitle)))
      End If
      getFirstName = firstName
      End Function

      Function getLastName(thisFullName As String) As String
      Dim lastName As String
      Dim spacePos As Integer
      spacePos = nameAndTitle = InStr(thisFullName, " ")
      If spacePos = 0 Then 'ถ้าไม่มีช่องว่าง แสดงว่าไม่มีนามสกุล
          lastName = Trim(Mid(thisFullName, InStr(thisFullName, " "), Len(thisFullName)))
      Else
          lastName = blank
      End If
      getLastName = lastName
      End Function
    4. บันทึกไว้ และกลับไปที่ Sheet ข้อมูล
    จากโค้ดข้างบน มีการสร้างฟังก์ชัน 3 ฟังก์ชัน คือ
    • getTitle() สำหรับแยกคำนำหน้าชื่อ การใช้งานถ้าจะให้สมบูรณ์ ต้องเพิ่มคำนำหน้าให้ครบ เช่น "เด็กหญิง", "ด.ญ.", "ม.ล." และอื่น ๆ ที่มีในข้อมูล เป็นต้น โดยทุกคำที่เพิ่ม ต้องอยู่ในเครื่องหมายคำพูดด้วย
    • getFirstName() สำหรับแยกชื่อตัว 
    • getLastName() สำหรับแยกนามสกุล
    การเรียกใช้งาน
    1. กลับไปที่ Sheet ข้อมูล
    2. ที่เซลล์ D2 พิมพ์ =getTitle(B2) แล้วกด Ctrl + Enter เพื่อให้ส่วนที่เลือกอยู่ตำแหน่งเดิม

    3. ลาก Fill handle ลงมาให้ถึงเซลล์ D4

    4. ที่เซลล์ E2 พิมพ์ =getFirstName(B2) แล้วกด Ctrl + Enter


    5. ดับเบิ้ลคลิกที่ Fill handle จะได้ชื่อครบทุกคน
    6. ที่เซลล์ F2 พิมพ์ =getLastName(B2) แล้วกด Ctrl + Enter


    7. ดับเบิ้ลคลิกที่ Fill handle จะได้นามสกุลครบทุกคน

    การบันทึกไฟล์ Excle 2010 ต้องบันทึกเป็นไฟล์นามสกุล .xlsm เนื่องจากมีโค้ดอยู่ด้วย

    เป็นอันเสร็จ ถ้ามีข้อมูลน้อย ๆ ก็ไม่มีปัญหา ถ้ามีข้อมูลเยอะ ๆ ต้องทำตามข้างบนนี้ จะสะดวกกว่ามาก

    วันอาทิตย์ที่ 8 เมษายน พ.ศ. 2555

    การใช้ INDEX และ MATCH ของ Excel

    INDEX ใช้สำหรับแสดงข้อมูล ในช่วงที่ระบุ
    MATCH ใช้ค้นหาตำแหน่ง ในช่วงข้อมูลที่ระบุ ตามเงื่อนไขที่กำหนด

    รูปแบบการใช้

    INDEX( array, row_num, [ column_num ] )
         array  คือช่วงข้อมูลที่กำหนด
         row_num คือ ตำแหน่งเริ่มต้นลงมากี่แถว ในช่วงที่กำหนด
         column_num คือ ตำแหน่งถัดเข้าไปกี่คอลัมน์ ในช่วงที่กำหนด

    MATCH( lookup_value, lookup_array, match_type )
         lookup_value คือ ข้อมูลที่ต้องการค้นหา
         lookup_array คือ ช่วงข้อมูลที่กำหนดให้ค้นหาภายในนี้
         match_type คือ ลักษณะการค้นหา มีค่าได้ 3 ค่า คือ 0, +1 และ -1
              ถ้าเป็น 0 หมายถึงต้องเหมือนกับข้อมูลที่ต้องการค้นหา
              ถ้าเป็น +1 หมายถึง ให้หาข้อมูลที่มากที่สุด ซี่งน้อยกว่าหรือเท่ากับข้อมูลที่กำหนด และต้องเรียงข้อมูลจากน้อยไปหามาก เช่น -2 -1 0 1 2 3 หรือ ก - ฮ หรือ A-Z
              ถ้าเป็น -1  ให้หาข้อมูลที่น้อยน้อยที่สุด ซึ่งมากกว่าหรือเท่ากับข้อมูลที่กำหนด และต้องเรียงข้อมูลจากมากไปหาน้อย     

    เมื่อนำมาใช้ด้วยกัน ทำให้สามารถค้นหาข้อมูลได้ โดยการหาตำแหน่ง ด้วย MATCH เสียก่อน จากนั้น จึงนำมาแสดงด้วยคำสั่ง INDEX

    ตัวอย่าง
    จากข้อมูลข้างล่าง ให้หาอายุ (B2) และอาชีพ(B3) ของคนที่ระบุในเซลล์ B1

    ในเซลล์ B2 เขียนสูตร ดังนี้

    =INDEX(E2:G5,MATCH(B1,E2:E5,0),2)


    สูตร MATCH(B1,E2:E5,0) เป็นการค้นหาคำว่า สมาน ในช่วง E2 ถึง E5 โดยให้หาคำที่เหมือนกัน (match_type = 0) ซึ่งจะได้ค่าเป็น 1 เพราะคำว่า สมาน อยู่ในตำแหน่งที่ 1
    สูตร INDEX (E2:E5, 1, 2) จึงได้ค่าเท่ากับ 30

    สำหรับอาชีพ ในเซลล์ B3 ก็เขียนสูตรในทำนองเดียวกัน ดังนี้

    =INDEX(E2:G5,MATCH(B1,E2:E5,0),3)


    INDEX และ MATCH สามารถนำมาใช้คู่กัน เพื่อค้นหาตำแหน่ง และให้แสดงข้อความหรือข้อมูลตามต้องการได้

    การใช้คำสั่ง OFFSET ของ Excel กำหนดช่วงข้อมูล

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

    OFFSET( reference,rows,cols,height,width)

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

    ตัวอย่าง
    ให้หาผลรวมของจำนวนข้างล่างนี้ โดยกำหนดให้สามารถเพิ่มข้อมูลได้อีก และให้ปรับผลรวมโดยอัตโนมัติ


    จากภาพ เซลล์ C2 คือผลรวมของตัวเลขในเซลล์ C4 ถึง C6 และถ้ามีการเพิ่มข้อมูลในเซลล์ C7 C8 หรือ C9 ก็ให้ปรับผลรวมโดยอัตโนมัติ

    วิธีการ
    ต้องใช้คำสั่ง OFFSET เพื่อกำหนดช่วงให้รวมข้อมูลด้วยคำสั่ง SUM แต่เนื่องจากมีการเผื่อไว้สำหรับการเพิ่มข้อมูล จึงให้นับจำนวนข้อมูล โดยใช้ Count ร่วมด้วย เขียนสูตรในเซลล์ C2 ดังนี้

    =SUM(OFFSET(C4,0,0,COUNT(C4:C15),1))


    สูตร COUNT(C4:C15)จะได้ค่าเท่ากับ 3 เพราะมีข้อมูลตัวเลขเพียง 3 เซลล์
    สูตร OFFSET(C4,0,0,3,1) จะได้ช่วงข้อมูลจากจุดอ้างอิง C4 เริ่มจาก C4 ไปทางล่าง 0 และไปทางซ้ายเท่ากับ 0)มีความสูงเท่ากับ 3 และความกว้างเท่ากับ 1 ผลสุดท้ายจึงได้ C4 ถึง C6 นั่นเอง
    สูตร SUM(C4:C6) จึงได้เท่ากับ 59 ดังภาพข้างต้น

    ที่เหลือ สามารถลาก Fill handle จาก C2 ไปทางขวา จะได้ดังภาพ


    ถ้ามีการเพิ่มข้อมูล (ไม่เกิน C15) ข้อมูลจะปรับให้โดยอัตโนมัติ เพราะมีการใช้ Count เพื่อตรวจสอบจำนวนข้อมูล และใช้ OFFSET เพื่อกำหนดช่วงข้อมูล ให้กับฟังก์ชัน SUM

    วันเสาร์ที่ 7 เมษายน พ.ศ. 2555

    SUBSTITUTE และ REPLACE ใน Excel

    SUBSTITURE และ REPLACE ของ Excel ใช้แทนที่ข้อความเหมือนกัน ต่างกันที่ ถ้าเราทราบตำแหน่งของข้อความเดิมที่จะไปแทนที่ ให้ใช้ Replace แต่ถ้าเราไม่ทราบตำแหน่ง ให้ใช้ Substitute

    รูปแบบการใช้งาน

    SUBSTITUTE(text, "old_text", "new_text", instance_num)
       text:  ตำแหน่งข้อความที่จะนำมาเปลี่ยน เช่น A1
       old_text: ข้อความเดิมที่ต้องการจะเปลี่ยน
       new_text:  ข้อความที่ต้องการให้เปลี่ยนเป็นข้อความนี้
       instance_num:  ระบุจำนวนครั้งของข้อความที่เกิดขึ้นในข้อความที่จะนำมาเปลี่ยน ที่ต้องการเปลี่ยน (งง เหมือนกัน อ่านต่ออีกซักหน่อยนะครับ)  ถ้าไม่ระบุ Excel จะเปลี่ยนข้อความนั้น ๆ ทีมีอยู่ในข้อความทุกครั้ง เช่น ข้อความเดิมคือ 24122341225 ถ้าต้องการเปลี่ยนเลข 41 เป็น 55 จะมีการเปลี่ยน 2 ครั้ง เพราะหมายเลข 41 มีปรากฏในข้อความที่ระบุ 2 ครั้ง คือ ปราฏครั้งที่ 1 ตำแหน่งที่ 2 และ ปรากฏครั้งที่ 2 ตำแหน่งที่ 7 (ดังนี้ 24122341225) ดังนั้น ถ้าไม่ระบุ instance_num โปรแกรม Excel จะระบุให้ทั้ง 2 แห่ง แต่ถ้าต้องการเปลี่ยนเฉพาะ แต่ถ้าต้องการเปลี่ยนเฉพาะแห่งแรก ต้องระบุเป็น 1 เพราะเท่ากับเป็นการบอกว่า ให้เปลี่ยนเฉพาะที่มันเกิดครั้งแรกเท่านั้น ครั้งต่อ ๆ ไป ถ้าพบก็ไม่ต้องเปลี่ยน


    REPLACE(old_text,start_num,num_chars,new_text)
       old_text:  ข้อความที่จะนำมาเปลี่ยน
       start_num: ตำแหน่งแรกที่จะเปลี่ยนในข้อความนั้น
       num_chars: ช่วงที่จะเปลี่ยนนับจากตำแหน่งที่ระบุ
       new_text:  ข้อความใหม่ที่จะนำไปเปลี่ยน

    ตัวอย่าง
    จากภาพ ให้แทนที่ ตัวเลข 41 ด้วยตัวเลข 000
    จะเห็นว่า ตัวเลข 41 ปรากฏอยู่หลายแห่ง และในหลายตำแหน่ง เช่น ที่ A1 อยู่ตำแหน่งที่ 3 ส่วนที่  A3 และ A4 อยู่ตำแหน่งที่ 1 และที่ A13 อยู่ตำแหน่งที่ 2 เป็นต้น

    ลักษณะนี้ จะใช้ Replace ไม่น่าจะสะดวก ต้องใช้ Substitute ดีกว่า

    ที่ B1 เขียน สูตร ดังนี้

    =SUBSTITUTE(A1,"41","000")  (ไม่จำเป็นต้องระบุ instance_num เพราะมี 41 เกิดขึ้นแห่งเดียว)


    ลาก หรือ ดับเบิ้ลคลิกที่ปุ่ม Fill handle จะได้ข้อความใหม่ที่แทนที่แล้วทั้งหมด ดังนี้

    เพิ่มเติม

    จากตัวอย่าง จะเห็นว่ามีการแทนที่เลข 41 ด้วย 000 ทำให้มีจำนวนมากกว่าเดิมไป 1 ตัว ถ้าต้องการรักษาความยาวของข้อความ ให้เท่ากับ 4 ซึ่งเป็นความยาวของข้อความเดิม ให้ใช้ mid()  เพิ่มเข้าไป

    สูตร mid() เป็นการกำหนดช่วงข้อความที่จะนำมาให้แสดง มีรูปแบบดังนี้

    MID(text,start_num,num_chars)
       text:  ข้อความที่จะนำมาเปลี่ยน
       start_num:  ตำแหน่งแรกที่จะนำมาใช้
       num_chars: ช่วงความยาวที่ต้องการ โดยระบุเป็นจำนวนตัวอักษรที่ต่อจากตำแหน่งแรกที่ระบุ ถ้าเกินจากนั้นไปแล้วให้ตัดทิ้งไป ไม่เอามาใช้

    จากตัวอย่าง ต้องการตำแหน่งที่ 1 ถึง 4 เขียนสูตรที่เซลล์ C1 ได้ ดังนี้

    =MID(SUBSTITUTE(A1,"41","000"),1,4)

    จะได้ ดังภาพ


    ลาก Fill handle จะได้ ดังภาพ


    จะเห็นว่า Excel อนุญาตให้ใช้สูตรซ้อน ๆ กันได้ ทำให้ได้งานตามเงื่อนไขที่ต้องการ

    SUMPRODUCT

    เมื่อต้องการหาผลรวมของรายการหลายรายการ โดยมีเงื่อนไขหลายเงื่อนไข ให้ใช้ สูตร SUMPRODUCT
    เช่น ต้องการหาผลรวมการขายของพนักงาน ตามข้อมูลข้างล่างนี้


    จะเห็นว่า สุดาขายสินค้าหลายอย่าง แต่เราต้องการทราบเฉพาะจานเท่านั้น ดังนั้น ในกรณีนี้ จะมี 2 เงื่อนไข คือ ชื่อ และสินค้า แล้วให้นำเงินที่ขายได้มารวมกัน
    ในเซลล์ D12 ให้เขียนสูตร ดังนี้
    =SUMPRODUCT(--(B2:B9="สุดา"),--(C2:C9="จาน"),D2:D9)

    ข้อความ --(B2:B9="สุดา") จะได้ผลเป็น 0 ถ้าช่วงที่กำหนดไม่มีคำว่า "สุดา" และ เป็น 1 ถ้ามีคำว่า "สุดา"
    ข้อความ --(C2:C9="จาน")จะได้ผลเป็น 0 ถ้าช่วงที่กำหนดไม่มีคำว่า "จาน" และ เป็น 1 ถ้ามีคำว่า "จาน"
    ส่วน D2:D9 คือค่าที่จะให้นำมารวมกัน

    ผลที่ออกมาจะได้ ดังภาพ
    ในกรณีที่มีการระบุจำนวนสินค้า และราคาสินค้าด้วย ดังภาพข้างล่างนี้

    จะเห็นว่า สุดา ขายจานได้ 5 ใบวันที่ 1/4/2555 และ ขายได้อีก 8 ใบ เมื่อวันที่ 6/4/2555 จานราคาใบละ 20 บาท ดังนั้น การคำนวณ จึงต้องเอาจำนวนที่ขายได้ ไปคูณกับราคาแต่ละชิ้น แล้วจึงนำไปบวกกันอีกที ลักษณะนี้ เขียนสูตรที่ D12 ดังนี้

    =SUMPRODUCT(--(B2:B9="สุดา"),--(C2:C9="จาน"),D2:D9*E2:E9)

    จะได้ผล ดังภาพ

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