excel, návod, tipy, funkce, makra, vba, tabulky, šablony
Váš průvodce programem Microsoft Excel a programováním maker ve VBA
< návrat zpět
Téma: Seřazení pomocí vzorce
MS Excel
Téma: Seřazení pomocí vzorce 
Zaslal/a 24.1.2013 16:41
Zdravím,
mohli byste prosím vy, excela znalý, mě navédst (nebo mě říct že to nejde)...
Mám v tabulce seznam, a chtěl bych ho pomocí vzorce seřadit na jiný list. Jde o vygenerování seznamů v závislosti na různých podmínkách, ale to už bych si pak poradil. Co neumím je například vypsat pátou položku podle abecedy ze seznamu, který podle abecedy seřazen není.
Napadla mě jediná obklika - tento seznam nejdříve seřadit klasicky pomocí zabudované funkce excelu. Ale to mi nevyhovuje por další použití ....
předem díky za návod, nebo spíš nakopnutí kde začít
@marjankaj:
špatne,výsledkom má byť kombinácia A a N v dĺžke 10 znakov..citovat
špatne,výsledkom má byť kombinácia A a N v dĺžke 10 znakov..citovat
@AL máš pravdu.
Tak si pomôžem trochu inak A=1, N=0
Ale fungovať to bude iba do 17 riadkov.
maticový vzorec
=TEXT(SUMPRODUCT(IF(A1:A10="vybráno";1;0);POWER(10;10-ROW(A1:A10)));"0000000000")
alebo aj s A a N
=SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT(IF(A1:A10="vybráno";1;0);POWER(10;10-ROW(A1:A10)));"0000000000");0;"N");1;"A")citovat
Tak si pomôžem trochu inak A=1, N=0
Ale fungovať to bude iba do 17 riadkov.
maticový vzorec
=TEXT(SUMPRODUCT(IF(A1:A10="vybráno";1;0);POWER(10;10-ROW(A1:A10)));"0000000000")
alebo aj s A a N
=SUBSTITUTE(SUBSTITUTE(TEXT(SUMPRODUCT(IF(A1:A10="vybráno";1;0);POWER(10;10-ROW(A1:A10)));"0000000000");0;"N");1;"A")citovat
@marjankaj
cool :)
malá poznámka - nie je nutné prevádzať na text, funguje i v tvare:
=SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(IF(A1:A10="vybráno";10^(10-ROW(A1:A10))));1;"A");0;"N")
ale to je len malý detail a ten vzorec je inak výbornýcitovat
cool :)
malá poznámka - nie je nutné prevádzať na text, funguje i v tvare:
=SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(IF(A1:A10="vybráno";10^(10-ROW(A1:A10))));1;"A");0;"N")
ale to je len malý detail a ten vzorec je inak výbornýcitovat
@marty099
A co si představujete pod pojmem "sofistikovanější řešení"? Excel je tabulkový kalkulátor a textové funkce jsou v podstatě pouze doplňkové.
@all
na druhou stranu jsem ale uvedl, že v excelu téměř neexistuje pojem NEJDE a tak mi to nedalo a také jsem to zkusil
nejdříve se mi líbila funkce DEC2BIN, ovšem pouze do chvíle, kdy jsem zjistil, že pracuje pouze v intervalu od -512 do 511, což je pouze 9 znaků
Maticový
=DEC2BIN(SUMA((B1:B9>C1:C9)*2^(POČET2(B1:B9)-ŘÁDEK(B1:B9)+ŘÁDEK(B1)-1));POČET2(B1:B9))
Pak jsem (nezávisle) dospěl k podobnému řešení jako marjankaj, s tím, že abych se vyhnul 0 na začátku, hodnotím pomocí 1 a 2
Maticový
=""&SUMA(((B1:B15>C1:C15)+1)*10^(POČET2(B1:B15)-ŘÁDEK(B1:B15)+ŘÁDEK(B1)-1))
Tady upřesním, že to bude fungovat max pro 15 řádků, protože excel každé číslo osekává na 15 míst a zbytek doplní nulama (1234567890123456789 oseká na 1234567890123450000)
Takže pokud bych potřeboval opravdu obecné řešení, tak mě bohužel nenapadá jinak, než pomocí vlastní funkce. Např.:
Function COMPARE_AREAS(Range1 As Range, Range2 As Range) As String
Dim sRetValue As String
sRetValue = "#N/A"
If Range1.Columns.Count = 1 Then
If Range1.Columns.Count = Range2.Columns.Count Then
If Range1.Rows.Count = Range2.Rows.Count Then
sRetValue = vbNullString
Dim I As Integer
For I = 1 To Range1.Cells.Count
sRetValue = sRetValue & IIf(Range1.Cells(I).Value > Range2.Cells(I).Value, "A", "N")
Next I
End If
End If
End If
Set Range1 = Nothing
Set Range2 = Nothing
COMPARE_AREAS = sRetValue
End Function
A co si představujete pod pojmem "sofistikovanější řešení"? Excel je tabulkový kalkulátor a textové funkce jsou v podstatě pouze doplňkové.
@all
na druhou stranu jsem ale uvedl, že v excelu téměř neexistuje pojem NEJDE a tak mi to nedalo a také jsem to zkusil
nejdříve se mi líbila funkce DEC2BIN, ovšem pouze do chvíle, kdy jsem zjistil, že pracuje pouze v intervalu od -512 do 511, což je pouze 9 znaků
Maticový
=DEC2BIN(SUMA((B1:B9>C1:C9)*2^(POČET2(B1:B9)-ŘÁDEK(B1:B9)+ŘÁDEK(B1)-1));POČET2(B1:B9))
Pak jsem (nezávisle) dospěl k podobnému řešení jako marjankaj, s tím, že abych se vyhnul 0 na začátku, hodnotím pomocí 1 a 2
Maticový
=""&SUMA(((B1:B15>C1:C15)+1)*10^(POČET2(B1:B15)-ŘÁDEK(B1:B15)+ŘÁDEK(B1)-1))
Tady upřesním, že to bude fungovat max pro 15 řádků, protože excel každé číslo osekává na 15 míst a zbytek doplní nulama (1234567890123456789 oseká na 1234567890123450000)
Takže pokud bych potřeboval opravdu obecné řešení, tak mě bohužel nenapadá jinak, než pomocí vlastní funkce. Např.:
Function COMPARE_AREAS(Range1 As Range, Range2 As Range) As String
Dim sRetValue As String
sRetValue = "#N/A"
If Range1.Columns.Count = 1 Then
If Range1.Columns.Count = Range2.Columns.Count Then
If Range1.Rows.Count = Range2.Rows.Count Then
sRetValue = vbNullString
Dim I As Integer
For I = 1 To Range1.Cells.Count
sRetValue = sRetValue & IIf(Range1.Cells(I).Value > Range2.Cells(I).Value, "A", "N")
Next I
End If
End If
End If
Set Range1 = Nothing
Set Range2 = Nothing
COMPARE_AREAS = sRetValue
End Function
Příloha: 
11430_compare.zip (15kB, staženo 25x)
citovat11430_compare.zip (15kB, staženo 25x)@marjankaj
to ma nenapadlo, chyba v testovaní, dík za opravu
@marjankaj & eLCHa
obmedzenie 15 znakov sa dá obísť, opäť zreťazením, ale to len tak na okraj...citovat
to ma nenapadlo, chyba v testovaní, dík za opravu
@marjankaj & eLCHa
obmedzenie 15 znakov sa dá obísť, opäť zreťazením, ale to len tak na okraj...citovat
No v tomto prípade, povedzme, že rozsah, ktorý vyhodnocujem, predstavuje oblasť 20 buniek, pričom moja funkcia bude mať obmedzenie na 15 znakov. Tak prvých 15 buniek vyhodnotím funkciou, výsledok bude reťazec v tvare napr. NNNAAANNNAAAAAA (15 znakov) a pokračujem v zápise funkcie na jej konci ampersandom a vyhodnocovaním buniek 16-20 atd. Na začiatku teda potrebujem určiť počet vyhodnocovaných buniek a potom ich vyhodnocovať v skupinách po 15 pomocou pôvodnej, trochu modifikovanej funkcie, resp. niekoľkými variáciami pôvodnej funkcie spojených ampersandom.citovat
@AL
To mi tiež napadlo, rozdeliť to na skupiny a pospájať.
Ale taký vzorec? Čo som sa s koňom zrazil?
Veď stačí funkcia UDF, alebo aj to riešenie s pomocným stlpcom.
To som dal iba pre ilustráciu. Nakoniec mi nie je celkom jasné praktické využitie toho desaťznakového reťazca.
A kto by to lúštil, ak by chcel 100 znakový.
citovat
To mi tiež napadlo, rozdeliť to na skupiny a pospájať.
Ale taký vzorec? Čo som sa s koňom zrazil?
Veď stačí funkcia UDF, alebo aj to riešenie s pomocným stlpcom.
To som dal iba pre ilustráciu. Nakoniec mi nie je celkom jasné praktické využitie toho desaťznakového reťazca.
A kto by to lúštil, ak by chcel 100 znakový.
citovatUživatelské menu
Nejste přihlášen(a)
\n
\nMenu
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.
RSS nejnovější články