របៀបទាញយករូបភាពជាមួយលក្ខខណ្ឌក្នុងកម្មវិធី Excel

មានពីរជំហាន

ជំហានទីមួយៈ

បង្កើត Module ដោយចុច Alt+F11 ដើម្បីបើក Visual Basic Editor

ចុច Insert ហើយយក Module ហើយ Copy កូដ (ដូចរូបខាងក្រោម)



Code:

'******************************
'* InserPicFromFile           *
'* by: Sekmeas.blogspot.com   *
'* Last Update: 11-Sep-2013   *
'******************************
Sub InsertPicFromFile( _
   strFileLoc As String, _
   rDestCells As Range, _
   blnFitInDestHeight As Boolean, _
   strPicName As String)

   Dim oNewPic As Shape
   Dim shtWS As Worksheet

   Set shtWS = rDestCells.Parent

   On Error Resume Next
   'Delete the named picture (if it already exists)
   shtWS.Shapes(strPicName).Delete
   
   On Error Resume Next
   With rDestCells
      'Create the new picture
      '(arbitrarily sized as a square that is the height of the rDestCells)
      Set oNewPic = shtWS.Shapes.AddPicture( _
         Filename:=strFileLoc, _
         LinkToFile:=msoFalse, _
         SaveWithDocument:=msoTrue, _
         Left:=.Left + 1, Top:=.Top + 1, Width:=.Height - 1, Height:=.Height - 1)
      
      'Maintain original aspect ratio and set to full size
      oNewPic.LockAspectRatio = msoTrue
      oNewPic.ScaleHeight Factor:=5, RelativeToOriginalSize:=msoTrue
      oNewPic.ScaleWidth Factor:=5, RelativeToOriginalSize:=msoTrue
      
      If blnFitInDestHeight = True Then
         'Resize the picture to fit in the destination cells
         oNewPic.Height = .Height + 1.5
      End If
      
      'Assign the desired name to the picture
      oNewPic.Name = strPicName
   End With 'rCellDest
End Sub

បន្ទាប់មកចុចលើ Sheet ណាមួយដែលចង់ប្រើរបៀបទាញរូបភាព ឧទាហរណ៍ Sheet1 (CELENDER) រួច Copy កូដ (ដូចរូបខាងក្រោម)


Code:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("ID")) Is Nothing Then

         InsertPicFromFile _

            strFileLoc:=Range("Location").Value, _

            rDestCells:=Range("picture"), _

            blnFitInDestHeight:=True, _

            strPicName:="Sekmeas"

   End If

End Sub

រូចចុច Alt+Q ដើម្បីបិទ Visual Basic Editor

ជំហានទី២
បង្កើត Name Manager ចំនួន ៣ដូចជា

- ID កន្លែងវាយឈ្មោះរូប ដើម្បីទាយយករូបភាព ដោយរើស Cell E3:G3 រួចចុច Name Box វាយពាក្យ ID


- Location ជាទីតាំងសម្រាប់ដាក់អាស័យដ្ឋានរូបភាព ដោយរើស Cell A4 រួចចុច Name Box វាយពាក្យ Location បន្ទាប់មកចម្លងទីតាំងរូបភាពដាក់ចូល បើរូបភាពដាក់ក្នុង D:\PICTURE\
សូមវាយដូចនេះ ="D:\PICTURE\"&ID&".JPG"

Picture ជាទីតាំងរូបភាពដែលបង្ហាញ ដោយរើស Cell AA2:AC7 រួចចុច Name Box វាយពាក្យ Picture

គំរូ

ចុចទីនេះអានបន្ត>>>

របៀបកំណត់ទំរង់ Cell ក្នុងបង្ហាញទិន្នន័យរបស់ Excel

This tutorial on using conditional formatting to highlight dates is brought to us by MVP Frédéric Le Guen, with special acknowledgment and thanks to Ken Puls for assistance with translation from French to English. 

Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this.

Taking this functionality a step further, when you mix date functions with conditional formatting, you can create spreadsheets that display date alerts automatically when a deadline is near or differentiates between types of days, like weekends and weekdays.

The basics of conditional formatting for dates

To find conditional formatting for dates, go to

Home > Conditional Formatting > Highlight Cell Rules > A Date Occuring.

You can select the following date options, ranging from yesterday to next month:

These 10 date options generate rules based on the current date. If you need to create rules for other dates (e.g., greater than a month from the current date), you can create your own new rule.

Below are step-by-step instructions for a few of my favorite conditional formats for dates.

Highlighting weekends

When you design an automated calendar you don't need to color the weekends yourself. With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function.  Assume that you have the date table--a calendar without conditional formatting:

To change the color of the weekends, open the menu Conditional Formatting > New Rule

In the next dialog box, select the menu Use a formula to determine which cell to format.

In the text box Format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):

=WEEKDAY(B$5,2)>5

The parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends.

Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.

Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

Click OK, then open Conditional Formatting> Manage Rules

Select This Worksheet to see the worksheet rules instead of the default selection. In Applies to, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.

Now you will see a different color for the weekends. Note: This example shows the result in the Excel Web App.

Highlighting holidays

To enrich the previous workbook, you could also color-code holidays. To do that, you need a column with the holidays you'd like to highlight in your workbook (but not necessarily in the same sheet). In our example, we have US public holidays in column AH (as related to the year in the cell B2).

Again, open the menu Conditional Formatting > New Rule. In this case, we use the formula COUNTIF in order to count if the number of public holidays in the current month is greater than 1.

 =COUNTIF($AH$4:$AH$16,B$5)>1

Then, in the dialog box Manage Rules, select the range B4:AF11. If you want to highlight the holidays over the weekends, you move the public holiday rule to the top of the list.

This example in the Excel Web App below shows the result. Change the value of the month and the year to see how the calendar has a different format. 

Highlighting delays

In case we want to change the color of cells based on our approach on a date again, we will use conditional formatting to make it work for us.

In the following example, we show:

• yellow dates between 1 and 2 months
• orange dates between 2 and 3 months
• purple dates more than 3 months

We then construct three rules conditional formatting using formula DATEDIF . Respectively for the three cases the following formulas:

=DATEDIF($B2,$E$2,"m")>0

=DATEDIF($B2,$E$2,"m")>1

=DATEDIF($B2,$E$2,"m")>2

 In the Excel Web App below, try changing some dates to experiment with the result.

Color scales

Rather than choose a different color set for each period in our timeframe, we will work with the option of color scales to color our cells.

First, go into a new column (column E), calculate the difference in number of days in a year again with the DATEDIF formula and the parameter "yd".

=DATEDIF($D2,TODAY(),"yd")

Then choose the menu Conditional Formatting> New Rule option Format all cells based on their value and choose the following options:

• Scale = 3 colors
• Minimum = 0 red
• Midpoint = 10 yellow
• Maximum = 30 white

The result is a gradient color scale with nuances from white to red through yellow.  The closer to 0, the more red it will have, the closer to 10 the more yellow, and the closer to 30 the more white.  In the Excel Web App below, try changing some dates to experiment with the result.

ចុចទីនេះអានបន្ត>>>

របៀបការពារឯកសារក្នុង Excel 2007

Protecting a workbook

Follow these steps to protect an Excel 2007 workbook:

1. Click the Protect Workbook command button in the Changes group on the Review tab.
   Excel opens the Protect Structure and Windows dialog box, where the Structure check box is selected by default. With the Structure check box selected, Excel won’t let anyone mess around with the sheets in the workbook (by deleting them or rearranging them).
   
   

   You can protect the structure and windows in a workbook.
2. (Optional) If you want to protect any windows that you set up, select the Windows check box.
   When selected, this setting keeps the workbook windows in the same size and position each time you open the workbook.
3. To assign a password that must be supplied before you can remove the protection from the worksheet, type the password in the Password (optional) text box.
4. Click OK.
   If you typed a password in the Password (optional) text box, Excel opens the Confirm Password dialog box. Re-enter the password in the Reenter Password to Proceed text box exactly as you typed it Step 3, and then click OK.

Unprotecting a workbook

To remove protection from the current workbook, follow these steps:

1. Click the Unprotect Workbook command button in the Changes group on the Review tab.
2. If you assigned a password when protecting the workbook, type the password in the Password text box and click OK.

Protecting a shared workbook

If you create a workbook with contents to be updated by several different users on your network, you can use the Protect and Share Workbook command to ensure that Excel tracks all the changes made and that no user can intentionally or inadvertently remove Excel’s tracking of changes. Follow these steps:

1. Click the Protect and Share Workbook command button in the Changes group on the Review tab.
   The Protect Shared Workbook dialog box appears.
2. Select the Sharing with Track Changes check box.
   
   

   Protect a shared workbook so that users cannot remove Excel’s tracking of changes.
3. To assign a password that each user must supply before he or she can open the workbook to make any changes, type a password in the Password (optional) text box and click OK.

ចុចទីនេះអានបន្ត>>>

រូបមន្តសរុបនៅលើ អាស័យដ្ឋាន Cell តែមួយ ហើយច្រើន Sheet

រូបមន្តសរុបនៅលើ អាស័យដ្ឋាន Cell តែមួយ ហើយច្រើន Sheet

=SUM(Sheet1:Sheet20!A1)

មានន័យថា បូក Cell A1 ទាំង ​20 Sheet

ចុចទីនេះអានបន្ត>>>

របៀបផ្អាតកាត Building Master ដែលបាត់មិនឲ្យដំណើរការ

ចុចប៊ូតុង Other ចុច Building Master Card



ជ្រើសយក Suspend previous card និងបំពេញព័ត៌មាន រួចចុច Confirm។
បន្ទាប់មកយកនេះ ដាក់តាមបន្ទប់ដែលមិនចង់ឲ្យកាតបាត់ប្រើប្រាស់បានជាការស្រេច។

ប៉ុន្តែយើងត្រូវបង្កើតកាត់ Building Master ឡើងវិញទាំងអស់ក្រោយពេល Suspend រួច។

ចុចទីនេះអានបន្ត>>>