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 samodejno razvrstite stolpec po vrednosti v Excelu?

Na primer, tabela za nakup je prikazana kot prikaz leve slike. Zdaj želite, da se stolpec Cena samodejno razvrščajo, ko v tem stolpcu vnesete novo številko / ceno, kako jo lahko rešite? Tukaj predstavim makro VBA, ki vam bo pomagal samodejno razvrščanje določenega stolpca po vrednosti v Excelu.

Samodejno razvrstite stolpec po vrednosti z vrednostjo VBA

Preprosto primerjati po pogostosti dogodkov v Excelu

Kutools za Excelove Napredna vrsta pripomoček podpira sortiranje podatkov po dolžini besedila, priimku, absolutni vrednosti, frekvenci itd. v Excelu hitro. Popolna brezplačna preizkusna različica 60-dan!
oglas razvrščate po frekvenci 2


puščica modra desno mehurčekSamodejno razvrstite stolpec po vrednosti z vrednostjo VBA

Ta makro VBA bo samodejno razvrstil vse podatke v določenem stolpcu, takoj ko vnesete nove podatke ali spremenite vrednost v stolpcu v Excelu.

1. Z desno tipko kliknite trenutno ime lista v Sheet Tab barin nato kliknite Ogled kode iz menija z desnim klikom.

2. V pogovornem oknu za Microsoft Visual Basic za aplikacijo v okno za odpiranje vstavite naslednjo makro kodo VBA.

VBA: Samodejno razvrščanje stolpcev v Excelu

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Opombe: V zgornji kodi vba B: B pomeni, da bo samodejno razvrščanje stolpca B, B1 je prva celica v stolpcu B, B2 je druga celica v stolpcu B in jih lahko spremenite glede na vaše potrebe.

3. Nato se vrnite na delovni list, ko vnesete novo številko v stolpec Cena ali spremenite obstoječe cene, se stolpec Cena samodejno sortira po naraščajočem vrstnem redu.

Opombe: pri vnosu nove številke v stolpec Cena morate vnesti številko v prvo prazno celico pod originalno številko. Če obstajajo prazne celice med novo vneseno številko in izvirnimi številkami ter prazne celice med prvotnimi številkami, ta stolpec ne bo samodejno razvrščen.


Demo: Samodejno razvrstite stolpec po vrednosti z VBA v Excelu

V tem videoposnetku Kutools in zavihek Kutools Plus zavihek doda Kutools za Excel. Če je potrebno, kliknite tukaj da brezplačno brezplačno preizkusite 60-dan!


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.
    Paula · 1 months ago
    May I ask if there is a way to use this VBA code if there is a formula in the column that you want to be the main sort.

    i.e. if A2 = the sum of f2 + g2+ h2 and column A needs to be sorted on the total sum? (I can't get VBA to work if formula in the cells)

    Any assistance would be kindly appreciated as hope to use it for ranking races when the winner is the one with the total of three racing events.

    Thank You
  • To post as a guest, your comment is unpublished.
    Bekir · 2 months ago
    Hi,

    How can I make this macro start after I fill all data to the specific column?
    I want the listing macro start after I fill empty boxes. Because when I use this, it changes the place suddenly.
    I need to start this macro after I filled the last data of that column (20 datas to be exact)
  • To post as a guest, your comment is unpublished.
    Rhea · 9 months ago
    Hello, I am an intermediate Excel user. I am wondering if someone could help direct me for what I am trying to accomplish. Here is what I am trying to do: Auto-Sort with Custom Order.

    A little about my spreadsheets: I have one sheet (named: Sheet1) that lists 16 columns (A through P) and currently 19 rows (this number will increase as more data is entered). In the last column (column P) all cells have a drop down menu of options (located and pulled from sheet two (named "BackEnd"). The drop down selections are color-coded (using conditional formatting rules).

    What I am hoping to do: As a user enters data in a new row, as soon as they are finished selecting an option from the drop down menu in column P, Excel will auto-sort the new row to a specific order. I have been able to create VBA code for auto-sorting in ascending and descending order, but I have not been able to create a code for auto-sorting the data in a specific order. The order I would like to have the data sorted into is:

    Unresponsive
    Not Interested
    Interested
    Pre-Screened Not Qualified:
    Pre-Screened Qualified
    Application Pending
    Application Approved
    Initial Assessment:
    Scope of Work:
    Home Repairs in Progress
    On Hold:
    Complete
    Other:

    Is this possible? Any help is appreciated! Thank you!
  • To post as a guest, your comment is unpublished.
    Stephen · 1 years ago
    While this is a fantastic code, I would like to know if anyone could help me get it go from ascending to descending? In other words, I'm looking for it to be the largest number on top with the lowest number on the bottom
    • To post as a guest, your comment is unpublished.
      Xander · 1 years ago
      Change (Order1:=xlAscending, Header:=xlYes, ) To (Order1:=xlDescending, Header:=xlYes, )
  • To post as a guest, your comment is unpublished.
    SHABEERALI · 1 years ago
    Any one know how to set set an auto serial no. for an excel work sheet. The number should excludes all heading and sub headings, and should auto re arrange after adding or deleting a row.
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      Kutools for Excel provides an Insert Sequence Number feature, which can insert unique and consecutive values no matter how many times you insert.
      For example, the first time you insert 1,2,3,4;
      The second time this feature will insert 5,6,7,8,9,10
      The third time this feature will insert 11,12,…