Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako šteti skupno število klikov v določeni celici v Excelu?

Ta članek govori o štetju skupnih klikov v določeni celici v Excelu.

Št. Skupnih klikov v določeni celici s kodo VBA


Št. Skupnih klikov v določeni celici s kodo VBA


Za izračun števila klikov v določeni celici v Excelu naredite naslednje.

1. Na delovnem listu vsebuje celico, ki jo je treba šteti za svoje skupne klike, z desno miškino tipko kliknite zavihek stanja in nato kliknite Ogled kode iz kontekstnega menija.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kopirajte in prilepite pod kodo VBA v okno Code.

VBA koda: šteje število klikov v določeni celici v Excelu

Public xRgS, xRgD As Range
Public xNum As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRgS = Range("E2")
    If xRgS Is Nothing Then Exit Sub
    Set xRgD = Range("H2")
    If xRgD Is Nothing Then Exit Sub
    If Intersect(xRgS, Target) Is Nothing Then Exit Sub
    xNum = xNum + 1
    xRgD.Value = xNum
End Sub

Opombe: V kodi je E2 celica, ki jo potrebujete za štetje vseh klikov, H2 pa je izhodna celica štetja. Prosimo, spremenite jih po potrebi.

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej, ko kliknemo na celico E2 na določenem delovnem listu, se bodo skupni kliki samodejno naselili v celici H2, kot je prikazano spodaj. Če na primer kliknete celični čas E2 5, se v celici H5 prikaže številka 2.


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Barbara · 2 months ago
    Can you provide a code that allows counting clicks from A2, B2 cells through A14, B14 cells. Thanks in advance.
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Barbara,
      Do you mean counting the total clicks in range A2:B14? Or clicks for each cell in range A2:B14?
  • To post as a guest, your comment is unpublished.
    Andrés · 4 months ago
    Hola
    Hay alguna manera de programar el conteo de clicks de acuerdo a la fecha, es decir programar varias celdas para que cuenten con la fecha del día?
  • To post as a guest, your comment is unpublished.
    Demetrius · 7 months ago
    Hello, there is a way to back the counting for any number that I want? For exemple: I'd made 5 clicks, but i just wanted 3. So I change the number in the cell to 3, and when I click again, it continue from 3.
    Thank for the code!
  • To post as a guest, your comment is unpublished.
    Guido · 1 years ago
    Thank you for the code, very useful.
    I'm not a programmer and I would like to know how to extend this process to every line. That is to say, not only E2>H2 but also E3>H3, E4>H4, and so on.
    Is there a code for this?


    Thank you in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi Guido,

      The below VBA code can help you to solve the problem. Please have a try. Thanks for your comment.
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xRgArray As Variant
      Dim xNum
      Dim xStrR, xStrS, xStrD As String
      Dim xRgS, xRgD As Range

      Dim xFNum As Long
      xRgArray = Array("E2,H2", "E3,H3", "E4,H4", "E5,H5", "E6,H6")
      On Error Resume Next
      If Target.Cells.count > 1 Then Exit Sub
      For xFNum = LBound(xRgArray) To UBound(xRgArray)
      xStrR = xRgArray(xFNum)
      xStrS = ""
      xStrS = Left(xStrR, 2)
      xStrD = ""
      xStrD = Right(xStrR, 2)
      Set xRgS = Nothing
      Set xRgS = Range(xStrS)
      If TypeName(xRgS) <> "Nothing" Then
      Set xRgD = Nothing
      Set xRgD = Range(xStrD)
      If TypeName(xRgD) <> "Nothing" Then
      If TypeName(Intersect(xRgS, Target)) <> "Nothing" Then
      xRgD.Value = xRgD.Value + 1
      End If
      End If
      End If
      Next
      End Sub
      • To post as a guest, your comment is unpublished.
        Ruth · 10 months ago
        Thanks for this. I tried and it worked, however it only worked only until certain number of cells, how can we extend this code until the end of the cells? for example i type in this code below and it only works until "G9,G9". Thanks


        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim xRgArray As Variant
        Dim xNum
        Dim xStrR, xStrS, xStrD As String
        Dim xRgS, xRgD As Range

        Dim xFNum As Long
        xRgArray = Array("C4,C4", "D4,D4", "E4,E4", "F4,F4", "G4,G4", "C6,C6", "D6,D6", "E6,E6", "F6,F6", "G6,G6", "C7,C7", "D7,D7", "E7,E7", "F7,F7", "G7,G7", "C8,C8", "D8,D8", "E8,E8", "F8,F8", "G8,G8", "C9,C9", "D9,D9", "E9,E9", "F9,F9", "G9,G9", "C10,C10", "D10,D10", "E10,E10", "F10,F10", "G10,G10", "C11,C11", "D11,D11", "E11,E11", "F11,F11", "G11,G11", "C14,C14", "D14,D14", "E14,E14", "F14,F14", "G14,G14", "C15,C15", "D15,D15", "E15,E15", "F15,F15", "G15,G15", "C16,C16", "D16,D16", "E16,E16", "F16,F16", "G16,G16", "C17,C17", "D17,D17", "E17,E17", "F17,F17", "G17,G17", "C18,C18", "D18,D18", "E18,E18", "F18,F18", "G18,G18", "C20,C20", "D20,D20", "E20,E20", "F20,F20", "G20,G20")
        On Error Resume Next
        If Target.Cells.count > 1 Then Exit Sub
        For xFNum = LBound(xRgArray) To UBound(xRgArray)
        xStrR = xRgArray(xFNum)
        xStrS = ""
        xStrS = Left(xStrR, 2)
        xStrD = ""
        xStrD = Right(xStrR, 2)
        Set xRgS = Nothing
        Set xRgS = Range(xStrS)
        If TypeName(xRgS) <> "Nothing" Then
        Set xRgD = Nothing
        Set xRgD = Range(xStrD)
        If TypeName(xRgD) <> "Nothing" Then
        If TypeName(Intersect(xRgS, Target)) <> "Nothing" Then
        xRgD.Value = xRgD.Value + 1
        End If
        End If
        End If
        Next
        End Sub
        • To post as a guest, your comment is unpublished.
          crystal · 9 months ago
          Hi Ruth,
          The code is hard to optimized for meeting your needs. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Dennis · 1 years ago
    How can you "reset" the counter?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Dennis,
      Please add the below VBA code at the end of the original code. Every time you run this code, the counting will be reset to 0. Thank you for your comment.

      Sub ClearCount()
      xRgD.Value = ""
      xNum = 0
      End Sub