RSS

การตัดเอาตัวอักษรตัวแรกของข้อความมาแสดง

Sick smile กรณีมีข้อความอยู่ในเซลล์ใด ๆ แล้วต้องการตัดตัวอักษรโดยมีเงื่อนไขตามด้านล่างคือ

  1. ถ้าตัวอักษรแรกเป็น พยัญชนะ ให้ตัดตัวอักษรที่เป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา
  2. ถ้าตัวอักษรแรกเป็น สระ ให้ตัดตัวอักษรที่ถัดจากสระซึ่งเป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา

ยกตัวอย่างเช่น

ถ้า A1 คือ าญจนา ผลลัพท์ที่ต้องการ คือ
ถ้า A1 คือ เกวัฒน์ ผลลัพท์ที่ต้องการ คือ
ถ้า A1 คือ รุ้งทอง ผลลัพท์ที่ต้องการ คือ

เราสามารถเขียนสูตรที่ B1 เพื่อหาอักขระตัวแรกของค่าใน A1 ได้ตามด้านล่างครับ

=MID(A1,MIN(SEARCH(CHAR(ROW($161:$206)),A1&CHAR(ROW($161:$206)))),1)

Ctrl+Shift+Enter Nerd smile

Excel Forum • แสดงกระทู้ – การตัดตัวอักษรแบบมีเงื่อนไข

 
Leave a comment

Posted by on 25/08/2013 in Char, Mid, Min, Row, Search

 

Code สั่ง print ผ่านวงแลน

Freezing ปัญหาใช้ Macro สั่ง Print ผ่านวงแลน เมื่อนำไฟล์งานไป print เครื่องอื่น กลับ print ไม่ได้ เนื่องจาก Printer ที่กำหนดค่าไว้ Macro เป็นอีก Port แต่เครื่องที่นำไฟล์ไปใช้เป็นอีก Port ซึ่ง Port ของ Printer ไม่ตรงกัน

ตัวอย่างเครื่อง Print ที่กำหนดไว้ใน Macro

เครื่องแรก >>\\172.30.53.61\OKI MICROLINE 791 บน Ne06:
เครื่องที่สอง >>\\172.30.53.61\OKI MICROLINE 791 บน Ne13:

ทางเลือกหนึ่งสำหรับการแก้ปัญหา เราสามารถใช้ Code กำหนดให้ผู้ใช้เลือก Printer เองแทนการ Loop หา Port หรือกำหนด Port ตามตัวอย่างด้านล่างครับ Punch

Sub PrintOutput()
Dim Apt As String
If Application.Dialogs(xlDialogPrinterSetup).Show Then
Apt = Application.ActivePrinter
End If
Application.ActivePrinter = Apt
Range("A1:F35").PrintOut
End Sub

Excel Forum • แสดงกระทู้ – Code สั่ง print ผ่านวงแลน

 
Leave a comment

Posted by on 28/07/2013 in Lan, Print

 

เปิดไฟล์เดียวแต่กลับขึ้นมา 2 ไฟล์

Crying face ในบางครั้งเราพบเปิดไฟล์ Excel 1 ไฟล์แต่กลับเห็นเป็น 2 ทั้ง ๆ ที่เป็นงานเดียวกันเมื่อ save หรือเปลี่ยนแปลงกับอีกไฟล์อีกไฟล์ก็เป็นด้วย เลยไม่รู้ว่าไปกดถูกที่ไหน เช่น ไฟล์ราคาซื้อ 1 จะเปิดมาเป็น ราคาซื้อ 1:1 และ ราคาซื้อ 1:2

ภาพประกอบการแสดงหมายเลขต่อท้ายชื่อไฟล์

SNAGHTML8e5e86

จากกรณีดังกล่าวแสดงว่าไปกดเมนู View แล้วเลือก New Window โดยไม่ได้ตั้งใจครับ หากต้องการให้เหลือ Window เดียวก็ให้ปิดไป 1 ไฟล์ (1 Window) ครับ จากนั้นกด Save

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

 
Leave a comment

Posted by on 29/06/2013 in Window

 

การเปรียบเทียบค่า True, False กับ 1, 0

Smile สำหรับท่านที่ใช้งาน Excel มาสักระยะจะพบว่า True เปรียบได้กับเลข 1 และ False เปรียบได้กับเลข 0 หากเราคีย์ในเซลล์ใด ๆ เป็น

=True+0

ผลลัพธ์จะได้ 1

=False+0

ผลลัพธ์จะได้ 0

จากด้านบนเป็นการยืนยันว่า True กับ 1 และ False กับ 0 สามารถเทียบเคียงกันได้

สำหรับ True, False ถ้าอยู่เดี่ยว ๆ ในสูตร หรือ ถ้าเทียบกับพวก Logic กันเองเช่น

=True>False

ผลลัพธ์จะได้เป็น True ซึ่งถือเสมือนเป็นการเปรียบเทียบตัวเลขกับตัวเลข (1>0) ถ้าเทียบข้ามพวกเช่นไปเทียบกับตัวเลขเป็น

=True>9

ผลลัพธ์จะได้ True ซึ่งค่อนข้างขัดแย้ง เนื่องจากหาก True มีค่าเป็น 1 แล้วก็ต้องไม่มากกว่า 9 และผลลัพธ์ควรเป็น False แต่ในกรณีนี้เป็นการเปรียบเทียบข้ามพวก โปรแกรมจะถือ True ว่าเป็น Text และเมื่อเป็น Text จะไม่มีโอกาสน้อยกว่าตัวเลขไม่ว่าตัวเลขจะมากเท่าใดก็ตามครับ

ยกตัวอย่างเช่น หากต้องการให้

=Sum(1>False,2,0,1)

มีค่าเท่ากับ

=Sum(True>False,2,0,1)

เราจะเขียนเป็น =Sum(1>False+0,2,0,1) เพื่อบังคับให้ค่า logic กลับมาเป็นตัวเลขเสีย่กอนจึงจะเทียบกันได้ครับ Winking smile

 
Leave a comment

Posted by on 18/05/2013 in Functions

 

Tags: ,

การเปรียบเทียบข้อมูลจาก 2 ชีทแล้วนำผลต่างไปแสดงในชีทที่ 3

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

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

ภาพข้อมูลใน Sheet1

image

ภาพข้อมูลใน Sheet2

image

ภาพการสรุปผลต่างของข้อมูลระหว่าง Sheet1 กับ Sheet2

image

จากภาพด้านบนจะเป็นการหาผลต่างระหว่าง 2 ชีทโดยยึดค่าในคอลัมน์ A เป็นหลัก เราสามารถเขียนสูตรที่ Sheet3 ได้ดังนี้ครับ

  1. ที่เซลล์ G1:G2 คีย์ ไม่มีในชีท1, ไม่มีในชีท2 ตามลำดับ
  2. ที่เซลล์ H1 คีย์สูตร
    =SUM(IF(ISNA(MATCH(sheet2!A2:A117,sheet1!A2:A119,0)),1))
    Ctrl+Shift+Enter
  3. ที่เซลล์ H2 คีย์สูตร
    =SUM(IF(ISNA(MATCH(sheet1!A2:A119,sheet2!A2:A117,0)),1))
    Ctrl+Shift+Enter
  4. ที่เซลล์ H3 คีย์สูตร
    =SUM(H1:H2)
    Enter
  5. ที่เซลล์ I1 คีย์สูตร
    =SUM(H$1:H1)-H1+1
    Enter > Copy ไปยัง I2
  6. ที่เซลล์ E3 คีย์สูตร
    =IF(ROWS(E$3:E3)>$H$3,””,LOOKUP(ROWS(E$3:E3),$I$1:$I$2,$G$1:$G$2))
    Enter > Copy ลงด้านล่าง
  7. ที่เซลล์ B3 คีย์สูตร
    =IF(ROWS(B$3:B3)>$H$3,””,CHOOSE(MATCH($E3,$G$1:$G$2,0),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet2!$A$2:$A$117,sheet1!$A$2:$A$119,0)),ROW(sheet2!$A$2:$A$117)-ROW(sheet2!$A$2)+1),COUNTIF($E$3:$E3,$E3))),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet1!$A$2:$A$119,sheet2!$A$2:$A$117,0)),ROW(sheet1!$A$2:$A$119)-ROW(sheet1!$A$2)+1),COUNTIF($E$3:$E3,$E3)))))
    Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

การสรุปผลต่างออกมาลักษณะนี้จะช่วยลดเวลาในการตรวจสอบข้อมูลด้วยสายตาและลดความผิดพลาดลงได้ครับ Thumbs up

 
Leave a comment

Posted by on 28/04/2013 in Choose, If, Index, Lookup, Match, Row, Rows, Small, Sum

 

การลบ Style จำนวนมากในคราวเดียวด้วย VBA

Smile กรณีทีใช้ Excel 2007+ แล้ว Save กลับมาเป็น Excel 2003- บางครั้งจะพบปัญหาว่ามี Format มากเกินกว่าข้อจำกัดของ Excel 2003- ทำให้เปิดไฟล์ไม่ได้ โปรแกรมจะฟ้องว่า Too many difference formats เนื่องจาก Excel 2003- นั้นมี Style ได้แค่ 4,000 แบบเท่านั้น แต่ Excel 2007+ สามารถมีได้ถึง 64,000 แบบ

Styles ได้แก่การจัดรูปแบบเซลล์ทั้งหลาย ไม่ว่าจะเป็นตัวหนา ตัวเอียง ตัวบาง ขีดเส้นใต้ สีตัวอักษร สีเซลล์ ขนาดตัวอักษร ฯลฯ หากทำไว้หลากหลายจนเกินข้อจำกัดที่มีไห้ก็เป็นปัญหาตามด้านบนและทำงานต่อไม่ได้ครับ

การลบ Style มาก ๆ ด้วยมือนั้นย่อมไม่สะดวก เราสามารถใช้ Code ด้านล่างเพื่อลบ Style ทั้งหมดพร้อมกันทีเดียวได้ครับ Thumbs up

Sub DelAllStyles()
Dim s As Style
On Error Resume Next
For Each s In ThisWorkbook.Styles
s.Delete
Next s
End Sub

กรณีใช้ Excel 2003- หลังจากลบออกด้วย Code แล้วให้จัดรูปแบบใหม่ให้เหลือเฉพาะที่จำเป็นก็พอครับ Winking smile

 
Leave a comment

Posted by on 30/03/2013 in Style

 

การ Run ตัวเลขแบบเว้นบรรทัดใน Excel

Smile การ Run ตัวเลขให้ต่อเนื่องกันไปโดยไม่เว้นบรรทัดนั้นสามารถทำได้หลายวิธี หนึ่งในนั้นคือคีย์ตัวเลขเริ่มต้นแล้วนำเมาส์ไปชี้ที่มุมขวาล่างของเซลล์ (จนปรากฎเครื่องหมาย +) จากนั้นคลิกขวาค้างแล้วลากไปทิศที่ต้องการ เมื่อปล่อยเมาส์จะมี Option ให้เลือกว่าต้องการจะทำสิ่งใด ในเมนูย่อยนั้นก็จะมี Fll Series ซึ่งเป็นการ Run ตัวเลขอยู่ด้วย เมื่อเลือก Fill Series แล้วก็จะเติมตัวเลขตามลำดับให้อัตโนมัติ ขึ้นอยู่กับว่าเราลากไปทิศทางใด หากไปทางขวาและลงด้านล่าง จะเป็นการ Run แบบเพิ่มตัวเลขไปครั้งละ 1 หากลากไปด้านซ้ายหรือขึ้นด้านบนจะเป็นการ Run แบบลดตัวเลขไปครั้งละ 1

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

ยกตัวอย่างเช่นต้องการให้ข้อมูลเป็นตามภาพด้านล่าง

image

จะเห็นได้ว่า Run ตัวเลขที่บรรทัดที่ 1, 3, 5, 7, 9 โดย Run แบบเว้น 1 บรรทัด  สามารถทำดังนี้ครับ

  1. ที่ A1 คีย์ 1
  2. ลากเมาส์คลุม A1:A2 (A2 เป็นช่องว่าง เพราะต้องการเว้น 1 บรรทัด)
  3. ชี้ไปยังจุดจับเติม (มุมขวาล่างของ A2 เมื่อนำเมาส์ไปชี้แล้วเป็นเครื่องหมาย +)
  4. คลิกจุดจับเติมแล้วลากไปด้านล่างตามต้องการ

หากต้องการเว้น 3 บรรทัดก็ให้คลุม A1:A4 โดย A2:A4 เป็นเซลล์ว่างจากนั้นทำตามข้อ 3-4 ด้านบนก็จะได้ตัวเลขที่ Run แบบเว้นบรรทัดว่าง 3 บรรทัดตามต้องการ Winking smile

 
Leave a comment

Posted by on 24/02/2013 in Copy, Run

 
 
Follow

Get every new post delivered to your Inbox.

Join 791 other followers