< návrat zpět

MS Excel

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

Zaslal/a slavekskeeve 26.1.2017 20:00

slavekskeeveAhoj, 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.. 10

Zaslat odpověď >
Strana:  1 2 3   další »
xlnc(26.1.2017 21:10)#034705
avatar
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
MePExG
Prikladám riešenie pomocou kontingenčnej tabuľky KT a Power Query PQ. Popis je v prílohe.
Příloha: zip34706_naporc.zip (24kB, staženo 26x)
citovat
icon AL(26.1.2017 21:33)#034707
avatar
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
avatar
Ono s tím vzorcem pracovat pod VBA je to pakárna. Vymýšlím jinou techniku bez použití listu.citovat
icon AL(26.1.2017 21:54)#034709
avatar
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
avatar
Právě že nechci Evaluate, nechci řazení, nechci využívat list :-)citovat
icon AL(26.1.2017 22:13)#034711
avatar
njn, si proste iný level :)citovat
xlnc(26.1.2017 22:47)#034713
avatar
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
avatar
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
icon AL(26.1.2017 23:40)#034718
avatar
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ší »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Helios iNuvio

Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.

On-line nástroje