Memasukkan Nilai yang Berbeda-beda dalam satu Kolom

Jika anda
ingin memastikan bahwa nilai yang dimasukkan dalam satu kolom tertentu berbeda-beda satu dengan lainnya (misalnya data faktur penjualan selama sebulan atau data nomor penduduk, atau data nomor mahasiswa), maka anda dapat menggunakan data validation dari Excel.

Pada saat anda memasukkan informasi ke dalam sebuah worksheet, tentunya anda ingin memastikan bahwa data yang dimasukkan harus unik untuk satu kolom tertentu. Sebagai contoh, jika anda memasukkan data nomor faktur dalam kolom B sebuah worksheet, maka anda berkeinginan bahwa data yang dimasukkan tidak sama untuk setiap faktur atau dengan kata lain tidak boleh dua kali input.

Ada beberapa cara untuk mengatasi permasalahan ini. Jika anda selalu memasukkan nomor faktur baru pada bagian akhir kolom, maka anda dapat menggunakan fasilitas data validation untuk memastikan bahwa data yang dimasukkan dalam sel tersebut tidak sama dengan data yang telah dimasukkan sebelumnya (yang terletak di atas sel tersebut). Cara melakukannya adalah sebagai berikut:

  1. Pilih/Sorot kolom B.
  2. Klik menu Data | Validation (untuk Excel 2003 ke bawah) atau klik Data tab pada ribbon dan kemudian Data Validation pada kelompok Data Tool (Excel 2007) untuk menampilkan Data Validation Dialog Box
  3. Dengan menggunakan pilihan Allow drop-down list, pilih Custom option.
  4. Kemudian dalam kotak Formula, masukan rumus (formula) sebagai berikut:


    =MATCH(B1,$B:$B,0)=ROW(B1)

  5. Kemudian klik tombol OK.


Selanjutnya anda bisa mengubah Pesan di layar jika mouse berada di kolom B dengan mengklik Tab Input Message untuk memberikan pesan pada user bahwa mereka tidak boleh memasukkan data yang sama dua kali. Sedangkan untuk mengubah pesan kesalahannya maka gunakan Tab Error Alert .

Berikut contoh pengaturan Input Message.(jika sel dipilih)


dan pengaturan Error Alert (pesan kesalahan entry data)


serta Tampilannya di Layar jika sel dipilih/disorot


jika data salah


Satu lagi adalah, j ika anda memasukkan data disembarang sel dalam kolom B (tidak harus dipaling bawah, dan ingin mengetahui apakah anda memasukkan telah nilai data tersebut sebelumnya maka anda dapat melakukannya dengan langkah tersebut di atas tetapi rumusnya pada langkah ke empat adalah

=COUNTIF($B:$B,b1)<2

Ada ide lainnya silahkan anda kirimkan ke blog ini baik melalui comment, email dan berbagi untuk kemajuan IT Indonesia. Selamat mencoba dan sukses !!!

Share

10 comments for “Memasukkan Nilai yang Berbeda-beda dalam satu Kolom

  1. qq
    March 25, 2009 at 4:45 am
    gimana sc buat grafik yg gabung antara line dg bar
    • aurino
      March 27, 2009 at 3:48 am
      Buat grafik biasa garis dulu misalnya lalu klik salah satu titik dalam grafik yang akan diubah dan ubah jenis grafiknya (chart type)
      contohnya adalah sbb: download disini
    • aurino
      March 27, 2009 at 4:29 am
      Sudah dijawabkan
  2. Kakang
    June 26, 2010 at 1:19 pm
    Kalo source datanya dari sheet lain gimana bos??
  3. yuda
    October 17, 2011 at 7:14 am
    kok tidak bisa tak coba. keluar peringatan kalo sintaxnya kurang benar
  4. sutrisno
    December 22, 2011 at 9:57 pm
    gan, gimana caranya membuat makronya, agar kita bisa mencetak halaman tertentu dan mencetak selurunya. mks
    • December 23, 2011 at 7:28 pm
      Print tips for Excel
      Ron de Bruin (last update 31-Aug-2007)
      Go back to the Excel tips page

      1. Printing non-contiguous areas on one page
      2. Hide rows, columns or cells when you print a sheet
      3. Print odd and even pages
      4. Print visible, Hidden or all worksheets
      5. Do not print Header or Footer on all pages
      6. Print the Last Save time in the Footer of every sheet you print
      7. Printing a Workbook’s Full Path in a Header or Footer (John WalkenBach)
      8. Print every Worksheet with a value In cell A1
      9. Print the same worksheet ? time with the number in cell, Header or Footer
      10. Print Formulas
      11. Insert Page Breaks every ? rows
      12. One liners to Print a workbook, sheet, sheets, selection…..

      Printing non-contiguous areas on one page

      Insert a new worksheet (you’re going to use this for printing).
      Do this for each area you want to print on the same page.
      (The areas don’t have to be on the same sheet)

      If you change the original range, the picture will change too (values and formatting!).
      You can use this sheet always to print because it will always update if you change
      The original data.

      Excel 97-2003

      Select the range
      Edit | Copy in the menu bar (or use Ctrl-C)
      Go to the new worksheet and with the Shift key pressed click on Edit | Paste Picture Link.
      Go back and do the same for the other areas.

      Note: You see that the edit menu will change when you press the shift button when you click on Edit

      Excel 2007

      Select the range
      Home tab | Clipboard group | Copy (or use Ctrl-C)
      Go to the new worksheet and Click on the arrow on the Paste button in the Clipboard group on
      the Home tab and choose As Picture >Paste Picture Link.
      Go back and do the same for the other areas.

      Print selection or range with one or more areas with a macro.

      The macro will add a new sheet and copy all the selection areas on it.
      Then it will print and delete the sheet.

      Sub Test()
      Dim Destrange As Range
      Dim Smallrng As Range
      Dim Newsh As Worksheet
      Dim Ash As Worksheet
      Dim Lr As Long

      Application.ScreenUpdating = False

      Set Ash = ActiveSheet
      Set Newsh = Worksheets.Add
      Ash.Select

      Lr = 1

      ‘You can also use a range with more areas like this
      ‘For Each smallrng In Ash.Range(“A1:C1,D10:G20,A30″).Areas

      For Each Smallrng In Selection.Areas
      Smallrng.Copy
      Set Destrange = Newsh.Cells(Lr, 1)
      Destrange.PasteSpecial xlPasteValues
      Destrange.PasteSpecial xlPasteFormats
      Lr = Lr + Smallrng.Rows.Count
      Next Smallrng

      Newsh.Columns.AutoFit

      Newsh.PrintOut

      Application.DisplayAlerts = False
      Newsh.Delete
      Application.DisplayAlerts = True

      Application.ScreenUpdating = True

      End Sub

      Hide rows, columns or cells when you print a sheet

      AutoFilter or Advanced Filter is also a very good way to print only the things you want
      in the sheet, filter the range with your criteria and print the sheet.
      Check out this site for examples http://www.contextures.com/tiptech.html

      But it is not always possible to get the result with a filter, See the examples below for
      another way to hide Rows/Cells.

      If you use one of the Print options in Excel the event below will check the ActiveSheet name
      and run the code. This example will run if the ActiveSheet name = “Sheet1”

      1) It will hide row 10:15
      2) Print
      3) Unhide row 10:15

      Copy/Paste this event in the Thisworkbook module

      Where do I paste the code that I want to use in my workbook
      http://www.rondebruin.nl/code.htm

      Private Sub Workbook_BeforePrint(Cancel As Boolean)
      If ActiveSheet.Name = “Sheet1″ Then
      Cancel = True
      Application.EnableEvents = False
      Application.ScreenUpdating = False
      With ActiveSheet
      .Rows(“10:15″).EntireRow.Hidden = True
      .PrintOut
      .Rows(“10:15″).EntireRow.Hidden = False
      End With
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End If
      End Sub

      Or hide columns (this example hide column B and D)

      With ActiveSheet
      .Range(“B1,D1″).EntireColumn.Hidden = True
      .PrintOut
      .Range(“B1,D1″).EntireColumn.Hidden = False
      End With

      Or hide all rows with a blank cell in column A

      With ActiveSheet
      On Error Resume Next
      .Columns(“A”).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
      .PrintOut
      .Columns(“A”).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
      On Error GoTo 0
      End With

      Make the Font white of a range

      You can replace the red lines in the BeforePrint event above
      with the example code below if you want to use it.
      Or you can use the examples below in a macro.

      1) It will make the font white
      2) Print
      3) Make the font black again

      ‘Range with one area
      With ActiveSheet
      .Range(“B10:B14″).Font.ColorIndex = 2
      .PrintOut
      .Range(“B10:B14″).Font.ColorIndex = 1
      End With

      ‘Range with more areas
      With ActiveSheet
      .Range(“A1:A3,B10:B14,C12″).Font.ColorIndex = 2
      .PrintOut
      .Range(“A1:A3,B10:B14,C12″).Font.ColorIndex = 1
      End With

      ‘All cells with a error
      With ActiveSheet
      .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 2
      .PrintOut
      .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 1
      End With

      Use conditional formatting

      Make the Font white with conditional formatting
      http://www.contextures.com/xlCondFormat03.html#Print

      If you only want to print unprotected cells then use Dave Peterson’s example below

      Pick out a cell that you can use for a flag (say X1–but you can use any cell
      you want, but keep it out of the print range).

      Then select your range to hide/print.

      Excel 97-2003: Format|Conditional formatting
      Excel 2007: “Conditional formatting” In the Styles group on the Home tab
      Click on New Rule and choose “Use a formula to determine which cells to format”

      The formula is:
      =AND($X$1=”hide”,CELL(“Protect”,A1)=1)
      make it white font on white fill (or blue on blue or …)
      Note: A1 in the formula is the first cell in your selection

      When you want to print, enter hide in X1 to hide, print and clear X1 when you’re ready.

      Hide Empty rows, Print and unhide the rows

      This example will loop through every row in the range
      Set rng = Sheets(“Sheet1″).Range(“A1:A30″)

      If every cell in column A:G is empty it will hide that row.
      After the loop it print the sheet and then unhide the rows.

      Change “A1:G1″ in the macro to the cells you want.
      You can also use this with non contiguous ranges like “B1,D1:G1″

      Sub Hide_Print_Unhide()
      Dim rw As Long
      Dim rng As Range
      Dim cell As Range

      Application.ScreenUpdating = False

      Set rng = Sheets(“Sheet1″).Range(“A1:A30″)

      With rng.Columns(1)
      For Each cell In rng
      If Application.WorksheetFunction.CountA( _
      .Parent.Cells(cell.Row, 1).Range(“A1:G1″)) = 0 Then _
      .Parent.Rows(cell.Row).Hidden = True
      Next cell
      .Parent.PrintOut
      .EntireRow.Hidden = False
      End With

      Application.ScreenUpdating = True
      End Sub

      Print odd and even pages
      This option is not available in Excel but you can use a macro to do it.

      Sub Print_Odd_Even()
      Dim Totalpages As Long
      Dim StartPage As Long
      Dim Page As Integer

      StartPage = 1 ‘1 = Odd and 2 = Even

      ‘Or use the InputBox suggestion from Gord Dibben
      ‘StartPage = InputBox(“Enter 1 for Odd, 2 for Even”)

      Totalpages = Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)
      For Page = StartPage To Totalpages Step 2
      ActiveSheet.PrintOut from:=Page, To:=Page, _
      Copies:=1, Collate:=True
      Next
      End Sub

      Print visible, Hidden or all worksheets

      If you want to print a whole workbook you can use this code line

      ThisWorkbook.PrintOut Or ActiveWorkbook.PrintOut

      But this will not print hidden Worksheets.
      You can use this macro to print hidden and visible Worksheets

      Sub Print_Hidden_And_Visible_Worksheets()
      ‘Dave Peterson
      Dim CurVis As Long
      Dim sh As Worksheet
      For Each sh In ActiveWorkbook.Worksheets
      With sh
      CurVis = .Visible
      .Visible = xlSheetVisible
      .PrintOut
      .Visible = CurVis
      End With
      Next sh
      End Sub

      To print only hidden sheets use

      With Sh
      CurVis = .Visible
      If CurVis >= 0 Then
      .Visible = xlSheetVisible
      .PrintOut
      .Visible = CurVis
      End If
      End With

      Do not print Header or Footer on all pages

      The example below will only print the right header on the first page of the ActiveSheet.

      You have these options
      (LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, RightFooter)

      Check out the VBA help for all formatting codes.
      Look for “Formatting Codes for Headers and Footers”

      You can use something like this :

      .CenterFooter = “&8Page &P & of &N”
      .RightFooter = “&8Last Saved : &B” & ActiveWorkbook.BuiltinDocumentProperties(“Last Save Time”)
      .LeftFooter = “&8″ & ActiveWorkbook.FullName & Chr(10) & “Sheetname : &B” & ActiveSheet.Name

      Sub Test()
      Dim TotPages As Long
      TotPages = Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)
      With ActiveSheet.PageSetup
      .RightHeader = “Your Header info”
      ActiveSheet.PrintOut From:=1, To:=1
      .RightHeader = “”
      ActiveSheet.PrintOut From:=2, To:=TotPages
      End With
      End Sub

      Tip: If you not want to print the last page you can do this
      ActiveSheet.PrintOut From:=2, To:=TotPages -1

      You can also make a different header for the last page if you want

      Print the Last Save time in the Footer of every sheet you print

      If you copy this in the ThisWorkbook module it will print the Last Save Time
      in the Right Footer of every sheet when you use one of the Print options in Excel.
      Note: The Property is not working correct in Excel 97.

      Where do I paste the code that I want to use in my workbook
      http://www.rondebruin.nl/code.htm

      Private Sub Workbook_BeforePrint(Cancel As Boolean)
      Dim wkSht As Worksheet
      For Each wkSht In ThisWorkbook.Worksheets
      wkSht.PageSetup.RightFooter = “&8Last Saved : ” & _
      Format(ThisWorkbook.BuiltinDocumentProperties(“Last Save Time”), _
      “yyyy-mmm-dd hh:mm:ss”)
      Next wkSht
      End Sub

      If you use Excel 97 you can use this to add the Date in a worksheet cell

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
      Cancel As Boolean)
      ‘If you save the file the date/time will be placed in cell A1 of Sheet1
      Sheets(“Sheet1″).Range(“A1″).Value = Format(Now, “yyyy-mmm-dd hh:mm:ss”)
      End Sub

      Print every Worksheet with a value In cell A1

      With this macro you loop through every worksheet and if there Is a
      value in a certain cell it will add the sheet to the array and print it.
      You can also test a for a word like Sh.Range(“A1″).Value = “PrintMe”

      Sub Print_All_Worksheets_With_Value_In_A1()
      Dim Sh As Worksheet
      Dim Arr() As String
      Dim N As Integer
      N = 0
      For Each Sh In ActiveWorkbook.Worksheets
      If Sh.Visible = xlSheetVisible And Sh.Range(“A1″).Value “” Then
      N = N + 1
      ReDim Preserve Arr(1 To N)
      Arr(N) = Sh.Name
      End If
      Next
      With ActiveWorkbook
      .Worksheets(Arr).PrintOut
      End With
      End Sub

      Print the same worksheet ? time with the number in cell, Header or Footer

      This example will print ? copies of the same sheet (It use a Input box to ask you how many)
      It will copy the page number in cell A1 or in the Header or Footer.

      Note: you can use this for testing .PrintOut preview:=True

      Sub PrintCopies_ActiveSheet_1()
      Dim CopiesCount As Long
      Dim CopieNumber As Long
      CopiesCount = Application.InputBox(“How many copies do you want”, Type:=1)

      For CopieNumber = 1 To CopiesCount
      With ActiveSheet
      ‘ This example print the number in cell A1
      .Range(“A1″).Value = CopieNumber & ” of ” & CopiesCount

      ‘If you want the number in the footer use this line
      ‘.PageSetup.LeftFooter = CopieNumber & ” of ” & CopiesCount

      ‘Print the sheet
      .PrintOut
      End With
      Next CopieNumber
      End Sub

      The example below continue printing where It left off, such as today you print
      numbered pages 1-25 and the next time when you enter 10 in the input box it print 26-35.

      Sub PrintCopies_ActiveSheet_2()
      ‘ This example print the number in cell A1
      Dim CopiesCount As Long
      Dim CopieNumber As Long
      CopiesCount = Application.InputBox(“How many copies do you want”, Type:=1)

      With ActiveSheet
      If Not IsNumeric(.Range(“A1″).Value) Then .Range(“A1″).Value = 0

      For CopieNumber = 1 To CopiesCount
      .Range(“A1″).Value = .Range(“A1″).Value + 1

      ‘Print the sheet
      .PrintOut

      Next CopieNumber
      End With
      End Sub

      Print Formulas

      If you want to print your formulas then you can toggle the view with this

      Excel 97-2003: Tools – Options – View and check Formulas
      Excel 2007: “Show Formulas” in the Formula Auditing group on the Formula tab

      Or the shortcut CTRL ` in all Excel versions

      Check out also this example from John Walkenbach.
      http://j-walk.com/ss/excel/tips/tip37.htm

      And David McRitchie’s site
      http://www.mvps.org/dmcritchie/excel/formula.htm

      Insert Page Breaks every ? rows

      If row 1 is a header row and you want to print it on every page then
      change RW + 1 to RW + 2 and use File>Page Setup>Sheet to fill in $1:$1
      in the “Rows to repeat at top: ” box.

      This example will add breaks every 20 rows from row 1 till the last row with data in column A.

      Sub Insert_PageBreaks()
      Dim Lastrow As Long
      Dim Row_Index As Long
      Dim RW As Long

      ‘How many rows do you want between each page break
      RW = 20

      With ActiveSheet
      ‘Remove all PageBreaks
      .ResetAllPageBreaks

      ‘Search for the last row with data in Column A
      Lastrow = .Cells(Rows.Count, “A”).End(xlUp).Row

      For Row_Index = RW + 1 To Lastrow Step RW
      .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
      Next
      End With
      End Sub

      One liners to Print a workbook, sheet, sheets, selection…..

      Look in the VBA help for PrintOut and see that you can use the following arguments.
      expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

      Note: Remember that you can’t print sheets that are hidden
      If you print the whole workbook with the first example there is no problem but the example
      for Sheets or worksheets (example 2 and 3) will not work if there are hidden sheets.

      ActiveWorkbook.PrintOut
      ‘the whole workbook

      Worksheets.PrintOut
      ‘all worksheets

      Sheets.PrintOut
      ‘all sheets

      Sheets(Array(“Sheet1″, “Sheet3″)).PrintOut
      ‘all sheets in the array

      ActiveWindow.SelectedSheets.PrintOut
      ‘print all selected sheets

      ActiveSheet.PrintOut
      ‘only the activesheet

      Sheets(“Sheet1″).PrintOut
      ‘only “Sheet1″

      Selection.PrintOut
      ‘print only the selection

      Range(“C1:D5″).PrintOut
      ‘print range

  5. Adhink
    March 28, 2013 at 4:59 am

    *maaf.., gimana macrox gan, data tidak boleh dua kali input….

Leave a Reply