Příspěvky uživatele
< návrat zpět
Keď Vám funguje vytváranie názvu, tak si iba nepotrebné položky "schovajte". Napr takto, a makro nemusíte používať.
Tak si do toho zakomponujte ešte kontrolu, či je prvá trojica znakov číslom :
ISNUMBER(LEFT(A2;3)*1)
ak to hodí FALSE nepokračujte.
Alebo či je 4 znak medzera, pretože SPZ má 4. znak medzeru.
Jediný problém tohto vzorca je, že neberie v úvahu CaseSensitive, a tým pádom je pre Vás nepoužiteľný.
Inak:
Tab2 - slúži iba ako vzor. Robí tú istú funkciu ako v tom Vašom makre. Je to iba vzor, čo sa má za čo nahradiť. A je na to použitý absolútny odkaz. Teda pre každú bunku z tých 10 000 v Výsledok sa urobí toto:
- Je hodnota tejto bunky vo vzoroch Tab2 ?
- Ak áno tak ju nahraď podľa vzoru (hľadaná hodnota stĺpec A, nahradená B, v tabuľke Tab2)
- Táto vzorová tabuľka Tab2, je navyše dynamická, čiže reaguje okamžite na zmenu hodnôt, či ich počtu.
- Ak chcete zobraziť aj hodnoty, ktoré niesú uvedené vo vzoroch, tak len zmente v tom vzorci "" za 'Tab1'!A1
- Tento vzorec natiahnete na počet riadkov, aký chcete, áno aj 10 000
- Ak potrebujete rezervu, čiže ak dopĺňate dáta do Tab1, tak vzorec sa dá jednoducho upraviť.
No treba ešte porozmýšľať, ako v tom či onom prípade poriešiť CaseSensitive...
EDIT:
To Vaše makro, upravte takto:
Sub MultiFindNReplace()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range, Spolu As String
xTitleId = "Vyber_oblasti"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
Spolu = "|" & Join(Application.Transpose(InputRng.Value), "|") & "|"
For Each Rng In ReplaceRng.Columns(1).Cells
Spolu = Replace(Spolu, "|" & Rng.Value & "|", "|" & Rng.Offset(0, 1).Value & "|")
Next
Worksheets("Výsledok").Range(InputRng.Address).Value = Application.Transpose(Split(Mid(Spolu, 2, Len(Spolu) - 2), "|"))
Application.ScreenUpdating = True
End Sub
Prikladám súbor s riešením makrom aj vzorcom (maticový)
=IF('Tab1'!A1="";"";IFERROR(INDEX('Tab2'!$B$1:$B$4;MATCH(TRUE;EXACT('Tab1'!A1;'Tab2'!$A$1:$A$4);0));'Tab1'!A1))
Ako vidíte skúšané na 10800 riadkoch.
Príloha by to značne uľahčila, ale pokiaľ to máte obdobné tomuto, že výsledok očakávate inde, tak to môžete urobiť vzorcom.
Tab1 - zdroj dát
Tab2 - určuje, čo sa má za čo nahradiť
Výsledok - výsleodk
Jedná sa len o to jedno slovo "Apple" ? Alebo podobným spôsobom chcete aj ostatné nahradiť.
Vyskúšajte toto:
Function GETYEAR(T As String) As Variant
Dim i As Byte, Y As Variant
GETYEAR = vbNullString
If Len(T) >= 4 Then
For i = 0 To Len(T) - 4
Y = Mid(T, Len(T) - 4 - i + 1, 4)
If IsNumeric(Y) And Y > 1900 And Y < 2100 Then GETYEAR = Y: Exit Function
Next i
End If
End Function
Alebo použiť namiesto podmienok funkciu CHOOSE:
=IFERROR(CHOOSE(MATCH(B8;{"LDPE";"HDPE";"CPP";"BOPP";"MDPE";"BOPP antifog";"BOPP metal";"BOPP perleť";"PET"};0);0,92;0,953;0,9;0,91;0,935;0,91;0,91;0,7;1,4);"")
Zoznam listov v Rozbaľovacom Zozname by som asi urobil takto :
Private Sub Worksheet_Activate()
Dim S As String, sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If IsError(Application.Match(sh.Name, Array("ABC"), 0)) Then S = S & sh.Name & ","
Next sh
S = Left(S, Len(S) - 1)
With Cells(8, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=S
End With
If IsError(Application.Match(CStr(Cells(8, 1)), Split(S, ","), 0)) Then Cells(8, 1) = Split(S, ",")(0)
End Sub
Pričom v tom ...Array("ABC")... majú byť čiarkami oddelené názvy listov, ktoré v zozname byť nemajú.
A ten posledný riadok zabezpečí, že pri aktualizácii, sa skontroluje, či hodnota, ktorá v zozname bola predtým, môže v zozname byť, ak nie tak sa vloží prvá zo zoznamu.
Tento kód by som dal do udalosti Activate toho listu, v ktorom je zoznam. Zabezpečí to aktualizáciu zoznamu listov pri každom aktivovaní listu.
Pozeral som Vám na to, a zistil som, že je tam niekoľko komplikácií, ak chcete aby to bolo aspoň trochu blbuvzdorné, ktoré zatiaľ neviem ako vyriešiť. Málo času aj chuti.
Ako príklad spomeniem:
-Tie kódy máte ako dátum, to tak pravdepodobne byť nemá. Má to byť asi text "1.1"...
-Kvôli "blbuvzdornosti" treba zaviesť kontrolu duplicity
-zoradenie A-Z (kvôli nepravideľnosti údajov). Buď sa to zoradí teraz, alebo potom sa zoradia listy.
-listy treba najskôr zmazať, a potom rozkopírovať šablónu iba na také názvy listov, aké sú zapísané (opäť kvôli blbuvzdornosti). A zle by sa kontrolovalo, ktorý list bol prepísaný, a ktorý tam je pozostatok z minulého spustenia.
-ten kód sa musí previesť pred zoradením na rovnaký zápis. Napr. 1.0, 1.1, 1.2, 2.0, 2.1... Alebo skôr 1.00, 1.10, 1.02, 2.00, 2.10 ... (1.0 je hlavné meno, 1.1 atď sú deti) Ak by bolo tých detí viac ako 9.
-Skúšal som aj maticové vzorce, aby som odľahčil potom množstvo roboty v makre, ale zatiaľ som neprišiel prečo mi vo Vašom zošite nefunguje maticový vzorec na radenie textov, ktorý normálne používam. Tak ma napadá previesť bodku na čiarku, aby bolo z toho číslo, a to snáď zoradí dobre, ale to zasa zmizne potrebná 0 na konci.
Proste takýchto "malých" na prvý pohľad neviditeľných problémov je tam veľa.
-Rozmýšľal som aj nad Podmieneným formátovaním pre duplicity, ale to nieje blbuvzdorné, stačí urobiť Copy-Paste, a oblasť formátovania sa poruší.
Idea je zatiaľ taká, že sa zoradia data podľa upraveného stĺpca s kódom. Tam kde je "1", zmením na 1.0/1.00. Zoradím, zistím, či sa niečo neopakuje. Ak je všetko OK, vymažem listy, pre každé použité číslo 1.0/1.00 vytvorím zo šablóny (skrytý list) nový list, premenujem na časť pred čiarkou, a začnem vkladať ďalšie údaje ktoré majú 1. na začiatku (keďže je to zoradené nieje problém). Vzniknú mi aj zoradené listy.
Asi takto, ale kedy a ako presne to prípadne urobím, netuším...
No ale ja Vám teraz nerozumiem. Ak chcete čítať na jednom liste údaje z iných listov na základe hodnôt v bunkách, ktoré zastupujú adresu údajov, tak potom vzorcom INDIRECT.
Ale ak chcete ukladať z jedného listu údaje na miesto určené premennými v bunkách, vzorec použiť nemôžete. Ak vložíte napr. na list "1" vzorec s odkazom na bunku s hodnotou v základnom liste, bude Vám to fungovať iba do chvíle kým túto hodnotu v základnom liste nezmeníte na inú, ktorú chcete z tejto bunky načítať cez vzorec v inom liste napr "2". V tom momente sa Vám zmení aj hodnota v liste "1", lebo odkazuje na tú istú bunku, vzorcom.
Toto čo chcete, ak to chápem správne, tak jedine makrom. Urobil som Vám tam featurku, ak sa prekliknete na list ABC, tak sa Vám načíta zoznam listov do Výberového Zoznamu v A8. Ak stlačíte tlačítko, tak sa Vám zapíšu hodnoty "Hodn 1" a "Hodn 2" do zvoleného listu, a to na prvý voľný riadok. Transponované, čiže stĺpec mení na riadok. To je len pre ukážku.
Takto ?
Takže v stĺpcoch 6-22 a 22-6, táto časť vzorca
OR($B5="";NOT(ISERROR(MATCH($B5;{"D";"N";"očr"};0))))
testuje, či je nejaká hodnota zadaná v stĺpci B, ak je prázdne vráti TRUE, druhá časť testne či v B je niektorá z hodnôt D, N, očr. To urobí MATCH (tá 0 na konci je prepínač pre presnú hodnotu). Ak MATCH nenájde hodnotu B v poli D,N,očr, vráti chybu, preto je tam ISERROR, ktorá vráti TRUE ak je chyba. Mi ale potrebujeme zistiť, či je B hodnota z poľa, apreto to celé negujeme s NOT. Čiže získame TRUE ak B je v poli D,N,očr. Tieto 2 podmienky sú obalené v OR (alebo) a pre naše IF je to dôležité, lebo ktorákoľvek podmienka v OR je TRUE, tak OR vracia TRUE.
IF(OR($B5="";NOT(ISERROR(MATCH($B5;{"D";"N";"očr"};0))));"";
Čiže naša IF urobí čo, ak dostane výsledok podmienok v OR TRUE ? No nič, a práve o to ide. Vloží "" do stĺpcov 6-22 a 22-6, ak nieje v B nič, alebo ak je v B D,N,očr.
Ďalšia časť, zase overuje či už samotná vyhľadávacia VLOOKUP nenájde hodnotu (to je TRUE z funkcie ISERR, pretože VLOOKUP pri nenájdení vracia chybu), alebo je nájdená hodnota 0. V oboch prípadoch bude v OR, ktorá ich obaľuje, testované TRUE, a ak bude aspoň jedna podmienka TRUE, opäť vráti OR hodnotu TRUE. To je testnuté v IF, a ak je z OR TRUE, tak neurobí zasa nič, čiže vloží "".
Poznámka, tieto dve dvojice podmienok, by možno šli dať do jedného OR, neskúšal som.
OR(ISERR(VLOOKUP($B5;List3!$A$2:$G$25;4));VLOOKUP($B5;List3!$A$2:$G$25;4)=0)
A až keď máme vylúčené všemožné varianty, prečo by sme nemali nič vypisovať, tak až potom sa vloží samotná vyhľadaná hodnota cez to posledné VLOOKUP
VLOOKUP($B5;List3!$A$2:$G$25;4)
VLOOKUP funguje tak, že sa jej zadá
1 čo chceme hľadať - stĺpec B
2 kde chceme hľadať - tabuľka musí byť tomu uspôsobená, prehľadávaný stĺpec musí byť prvý, a stĺpce z ktorých budeme z validného riadku čítať údaje, musia byť zadefinované v tomto 2 parametre. Použitý je absolútny odkaz (s tými dolármi), aby sa nám neposúval v každom riadku. Naopak vyhľadávaná hodnota je čiastočne absolútna aj relatívna, stĺpec B je nežiadúce posúvať (preto $), ale riadok je nevyhnutné posúvať (preto bez $).
3 určuje z ktorého stĺpca predošlej oblasti, sa má brať údaj. Pre 6-22 tu je 4, a pre 22-6 tu je 5.
Rovnako funguje aj vzorec v Stravenky a Cestovné.
Stĺpce Dovolená, Nemoc, a očr, fungujú veľmi podobne, len sa v prvej podmienky určí že v týchto stĺpcoch sa nebude nič vypisovať ak v B nieje adekvátny "kód". Teda pre Dovolená "D", pre Nemoc "N", pre očr "očr"
PS: Ešte by som ale doporučil vo všetkých VLOOKUP vo vzorcoch pridať na koniec parameter FALSE, ktorý určuje, že bude hľadať presnú zhodu. Neviem si totiž spomenúť či je to predvolená hodnota). Tak pre istotu. Tak ako v prílohe.
Prípadne vyskúšajte INDEX MATCH konštrukciu.
Ešte ma napadlo otočiť výpočet opačne:
=3227-(3227*10/100)
a vyjde z toho 0. To číslo 10 je tam iba pre pochopenie. Tú si zamente naspäť za 100, ako ste tam mal. To len aby ste vedeli o ktorom čísle hovorím. Tak to číslo 10 je to číslo 100 z predošlého Vášho vzorca. Ako to mám vysvetliť... Tie čísla vymente, prvá 100 je druhá, a druhá je prvá. Bez tej jednej zátvorky.
Už viem ako to vysvetliť:
Nový
=3227-(3227*bbb/aaa)
Starý
=(3227-((3227/aaa)*bbb))+0
Na pripočítaní tej 0 na konci vôbec nezáleží - nemá to vplyv.
Keď ukončíte Outlook, tak sa Vás opýta, že či chcete ukončiť a odoslať neskôr, lebo sa ešte odosiela. No a Vy dáte, že chcete Ukončiť a odoslať neskôr. Pri ďalšom štarte chodte hneď do Pošta na odoslanie, označte neodoslanú správu, a dajte hore tlačítko Odstrániť. Správa by sa mala zrušiť a máte ju v Odstránené položky.
Teda takto je to presne u mňa na Outlook 2013 Pro.
Inak posielať 160 MB cez e-mail, keď mailové servery podporujú ledva pár jednotiek MB ???
Uživatelské menu
\nMenu
On-line nástroje
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
RSS nejnovější články