MS Excel

Ahoj, potřeboval bych poradit s makrem. Mám sloupec A, kde jsou číselné hodnoty, které budou pokaždé jiné (při každém exportu do excelu) a co potřebuju je, aby makro udělalo následující:
1) najde nejnižší číselnou hodnotu (i ve více řádcích) a všechny je přejmenoval např. na "1", potom druhou nejnišží a nahradit "2" atd...Dokud se nenahradí všechno.. Dejme tomu, že mám hodnoty 123, 999 a 321 a potřebuji aby se všechny 123 nahradily jedničkou, všechny 999 trojkou a 321 dvojkou. Jde o sloupec, takže Range A2 až An - díky za pomoc. Hledal jsem, ale..

Zaslat odpověď >

Strana: 1 2 3 další »

xlnc(26.1.2017 21:10)#034705

Nástřel... pro tyhle účely slouží funkce typu "RANK". Jenže ne všechny jsou dostupné (podle verze Excelu), navíc neumějí pořadí bez přerušení. Takže de facto potřebujete maticový vzorec pro každou buňku oblasti

=SUMA(1/(KDYŽ(Oblast<Bunka;COUNTIF(Oblast;Oblast);9,999999999E+307)))+1

a takto vytvořenou sadu hodnot překlopit do listu místo té původní.citovat

MePExG(26.1.2017 21:23)#034706

Prikladám riešenie pomocou kontingenčnej tabuľky KT a Power Query PQ. Popis je v prílohe.

Příloha: zip

34706_naporc.zip (24kB, staženo 25x)

citovat

AL(26.1.2017 21:33)#034707

Tak tomu hovorím pekný vzorec, Petře. Dá sa prípadne modifikovať:
=SUMA(1/(KDYŽ(Oblast<=Bunka;COUNTIF(Oblast;Oblast);9,999999999E+307)))citovat

xlnc(26.1.2017 21:44)#034708

Ono s tím vzorcem pracovat pod VBA je to pakárna. Vymýšlím jinou techniku bez použití listu.citovat

AL(26.1.2017 21:54)#034709

Pokiaľ by si ho chcel použiť vo VBA, tak cez Evaluate by to malo ísť, ale to asi vieš..

Trik na výpočet jedinečných hodnôt cez Sum(1/countif(oblast,oblast)) poznám už nejakú dobu z Walkenbachovej knižky. Vedel som, že to bude cesta, akurát som tam nedokázal zapracovať tú dodatočnú podmienku na Oblast<Bunka.

Cez VBA to nie je zložité. Šlo by napr.:
1. Skopírovať dáta
2. Odstrániť duplicity
3. Zotriediť
4. Následne využiť funkciu MATCH (pozvyhledat).

No, ale pokiaľ už máme vytvorený funkčný maticový vzorec, tak cez Evaluate, ako píšem úvodom:
Zadeklarovať si pole s počtom prvkov rovným počtu buniek, do ktorých sa ten výsledok má vložiť. Prvky poľa naplniť výsledkom toho maticového vzorca cez Evaluate. Následne to vyliať z poľa do stĺpca na liste.citovat

xlnc(26.1.2017 22:12)#034710

Právě že nechci Evaluate, nechci řazení, nechci využívat list :-)citovat

AL(26.1.2017 22:13)#034711

njn, si proste iný level :)citovat

xlnc(26.1.2017 22:47)#034713

Mno... není to rychlý algoritmus, holt práce s textem. 10 000 hodnot mi trvá při středním výkonu 44 sekund. Rozumná hranice je tak okolo 1 000 hodnot.

Sub ZamenaHodnotaRank()

Dim rngOblast As Range

Dim arrHodnoty
Dim arrHodnotyNove
Dim varSmall

Dim i As Long
Dim lngPoradi As Long
Dim lngPocetHodnot As Long

Dim strHodnoty As String

Set rngOblast = Range("A1:A1000")

lngPocetHodnot = rngOblast.Cells.Count

arrHodnoty = WorksheetFunction.Transpose(rngOblast)

strHodnoty = "|" & Join(arrHodnoty, "|") & "|"

Do

i = i + 1

On Error Resume Next

varSmall = Application.Small(arrHodnoty, lngPoradi + 1)

lngPoradi = lngPoradi + WorksheetFunction.CountIf(rngOblast, varSmall)

If IsError(varSmall) Then
Exit Do
End If

strHodnoty = Replace(strHodnoty, "|" & varSmall & "|", "|" & "T" & i & "|")

Loop

strHodnoty = Replace(Mid(strHodnoty, 2, Len(strHodnoty) - 2), "T", "")

arrHodnotyNove = Split(strHodnoty, "|")

rngOblast.Cells(1).Resize(lngPocetHodnot, 1) = _
WorksheetFunction.Transpose(arrHodnotyNove)

End Subcitovat

xlnc(26.1.2017 23:04)#034714

Ještě krátká úvaha...

Řazení samo o sobě není k užitku, i když by na něm nějaké to hledání minima proběhlo rychleji. A předpokládám, že původní řazení je podstatné.

Zbavení se duplicit (třeba přes Collection) - fajn, následně zjistit pořadí, dostaneme jakousi párovací tabulku a podle ní by se prováděl Replace. Neodhadnu časovou náročnost, ale asi by to bylo rychlejší, než co jsem vyplodil. Mohl by někdo zkusit.

Do Evaluate jsem jít nechtěl, ale je pravda, že se stejně třeba u COUNTIF dopouštím vzorce aplikovaného nad daty na listu (nefunguje mi na poli).

Běžné funkce typu "RANK" jsou na pytel, vynechávají pořadí (což by asi zadavateli vadilo). Čili sada pomalých maticových vzorců a začne to skřípat, i kdybychom využili pomocný sloupec listu nebo Evaluate (která navíc v případě maticových vzorců občas selže a musí se sestavit jiná podoba).

KT+Power nástroje .. neviděl jsem, nicméně databázový přístup a snad i nějaká funkce nad nad daty nemusí být špatný nápad. Jen asi těžko řešitelné se stařičkým MS Query a Power rozhodně nemá každý.citovat

AL(26.1.2017 23:40)#034718

Pekny kod.
MS Query zvlada Distinct i Order by, takze i cez MS Query by to asi bolo schodne..
Za pomoci MS Query vytvorit zotriedene unikatne hodnoty, potom vyuzit funkciu MATCH.citovat

Strana: 1 2 3 další »

Téma: Nahradit nejnižší...

Uživatelské menu

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Aktivní diskuse

Vyhledej

Vyhledej

Relativní cesta - zdroje Power Query

Relativní cesta - zdroje Power Query

Relativní cesta - zdroje Power Query

Relativní cesta - zdroje Power Query

Relativní cesta - zdroje Power Query