excel2010, trim funkcia na vsetko-preventivne. nefunguje vsak na SUMIF.

Používať preventívne TRIM na všetko nie je dobré rozhodnutie. Zbytočne to komplikuje vzorce, zneprehľadňuje ich a tiež znižuje udržiavateľnosť. Použil by som skôr podmienené formátovanie, pomocou ktorého by som si zvýraznil bunky s medzerami na konci. Každý jeden výskyt medzery na konci by som potom obúchal o hlavu tomu, čo to tam napísal. Ak to chceš riešiť "automaticky", tak makrom si môžeš vyčistiť vstupné dáta. Robiť to tak, že do každého vzorca pridáš TRIM, je najhorší možný spôsob.

Niekto tam pise, ze na SUMIF sa to neda pouzit ? Je toho viac, na co sa neda pouzit ?

Vo všeobecnosti to nebude fungovať vo vzorcoch, do ktorých nevstupuje priamo hodnota bunky, ale kritérium. Takže napríklad SUMIF, COUNTIF a COUNTIFS.

Teda zistil som tiez ze excel nerozlisuje velke a male pismena. Iba tie medzery.

Áno, a pokiaľ chceš rozlišovať veľké a malé písmená, tak najjednoduchšie je SUMIF prepísať na SUMPRODUCT a použiť funkciu EXACT.

Ak sa zadava do vzorca text, tak sa musi dat to dvojitych uvodzoviek "text", vsak ?

Áno. Niekedy ale ten text vo vzorci vystupuje ako kritérium, takže sa neberie doslovne. Napríklad ">2" v SUMIF spočítava bunky s väčšou hodnotou než 2. Nespočítavajú sa bunky, ktoré obsahujú doslovne text ">2".

Ale tie hviezdicky robia TRIM ?

Tie hviezdičky znamenajú, že namiesto nich môžu byť hocijaké znaky. Takže pre výraz "*TRICKO*" sa zoberie okrem hodnoty "TRICKO " napríklad aj hodnota "ABCTRICKOXYZ".

A este ten chlapik, co sa to pytal, to nakoniec urobil nejako inac, co som este nevidel ze co to ma znamenat

Prepísal SUMIF na SUMPRODUCT, v ktorom si môže lepšie definovať kritérium. V nápovedi pre SUMPRODUCT nájdeš podrobnosti (ide o obyčajný skalárny súčin). Tie znaky "--" sú obyčajné "+", ktoré slúži na konverziu z hodnôt TRUE/FALSE na 1/0 (kvôli čitateľnosti by som uprednostnil funkciu N).

Dik moc ! Super si mi poradil. Keby to takto robil kazdy (detailne, explicitne, k veci). Dalsi excel borec ^^

Tomu vzorcu chlapika nerozumiem vobec.

=SUMPRODUCT(--(TRIM(B3:B27)="Emmanuel");C3:C27)

1, Pozrel som si na youtube priklad na SUMPRODUCT, myslim ze viem o co ide, ale preco to tam on pouzil, ked len scitaval hodnoty, ktore su pri mene Emanuel. Urobil som to manualne, scitalo sa to iba.

Urobil som to takto ako mu radili

=SUMIF(B3:B27;"*Emmanuel*";C3:C27)

Vyslo to, ale nie je to optimalne riesenie, kedze *x* robi prilis velku toleranciu. Preto chcel silou mocou tiez pouzit TRIM, ale nechapem ako to moze fungovat.

2, To '--' je toto klasicke z matiky ?: -(-2) = +2 ?
Dal som do jeho vzorca namiesto tych minusiek plusko a pokazilo sa to.

3, "Ak to chceš riešiť "automaticky", tak makrom si môžeš vyčistiť vstupné dáta."
- nemam prestavu ako by to malo vyzerat, ak mozes nacrtni iba kostru prosim ta

Ten vzorec je skalárny súčin dvoch polí (v exceli rozsahy buniek, v matematike vektory). V prvom sú jednotky a nuly podľa toho, či ich chce zarátať do sumy alebo nie. V druhom sú hodnoty, ktoré sa potom prirátavajú podľa prvého poľa (buď jedenkrát alebo nulakrát).

1. Tento vzorec použil preto, že v tom prvom poli si môže ľubovoľne definovať podmienku pomocou vzorca - to sa pomocou SUMIF nedá. Aby to fungovalo, musia to ale byť polia čísel (TRUE ani FALSE nie je číslo).

2. Áno, -- je klasické + z matematiky. Je to tam preto, aby sa z TRUE/FALSE stalo číslo. Samotné + nestačí, dalo by sa tam ale dať napríklad 0+. Kvôli zrozumiteľnosti je najlepšie použiť funkciu N.

3. Napríklad takto zrušíš všetky medzery hneď pri vkladaní:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    Dim aString As String
    
    For Each aCell In Target
        If Not aCell.HasFormula Then
            aString = Trim(aCell)
            If aCell.Value <> aString Then
                aCell.Value = aString
            End If
        End If
    Next aCell
End Sub

Tento vzorec použil preto, že v tom prvom poli si môže ľubovoľne definovať podmienku pomocou vzorca - to sa pomocou SUMIF nedá.

- slovom 'podmienka' nemyslis kriterium, vsak ?
Emanuel je tu kriterium

=SUMIF(B3:B27;"*Emmanuel*";C3:C27)

ale aj podmienka, mi logicky vychadza, pretoze v SUMIF, uz nie je iny dalsi level podmienenosti alebo ako inak to nazvat.
V SUMIF sa da nadefinovat najaka podmienka, lebo inak by sa to nevolalo SUMIF, nechapem co si tym myslel.

Áno, -- je klasické + z matematiky. Je to tam preto, aby sa z TRUE/FALSE stalo číslo. Samotné + nestačí, dalo by sa tam ale dať napríklad 0+. Kvôli zrozumiteľnosti je najlepšie použiť funkciu N.

- chces povedat, ze on si nejako nadefinoval, ze ak bude v prvom stlpci (poli- podla nazvoslovia tej funkcie, po anglicky Array) meno Emanuel, tak to znamena True a to je vlastne 1 (z programovania predpokladam, False je nula. preto sa da v pythone urobit ze "True + True" a vyjde ti 2). A teda: 1(prvy stlpec) * cislo(druhy stlpec) = furt to cislo a potom dole riadkami sucet. Hmm dobry napad, ale nechapem ze ako zmenil True/False na cislo pomocou minusiek.

V jeho vzorci

=SUMPRODUCT(--(TRIM(B3:B27)="Emmanuel");C3:C27)

stale plati ze tam ma 3 elementy: uvedenie funkcie SUMPRODUCT, prve pole, druhe pole. Druhe pole je jasne, je to:

C3:C27

. Prve pole je toto vsetko ?:

--(TRIM(B3:B27)="Emmanuel")

Uz som nieco nasiel na nete, je to inak ako v programovani

Jeho vec sa da napisat trojako teda:

=SUMPRODUCT(--(TRIM(B3:B27)="Emmanuel");C3:C27)
=SUMPRODUCT(0+(TRIM(B3:B27)="Emmanuel");C3:C27)
=SUMPRODUCT(N(TRIM(B3:B27)="Emmanuel");C3:C27)

Ako presne excel berie '--', '0+', ze to konvertuje booleany na cisla ? A preco to nejde napr. s '+0' ?

Skusal som v tabulke toto

a toto

islo to aj bez konverzie. Preco ?

To makro si ako pridam do mojho excelu ?
A ako funguje ?

Lebo ja som myslel na to, ze ked otvorim doma cudziu excel tabulku, tak predtym nez zacnem robit, celu ju trimnem. Naraz. Jednym tlacitkom- makrom. Vlastne zmenim vsetky udaje. To sa da ?

Kritérium je splnené vtedy, keď sú splnené nejaké podmienky. Zatiaľ čo v SUMIF môžeš zadať kritérium len ako reťazec, tak vo vzorci môžeš použiť všetky dostupné funkcie.

Kedy Excel zmení TRUE/FALSE na 1/0 si môžeš vyskúšať pomocou vzorcov "=+TRUE", "=--TRUE", "=0+TRUE", "=TRUE+0", "=1*TRUE", a pod. Všetko okrem prvého sa prekonvertuje na číslo, pretože "je jasné", že ide o číselné operácie (unárne mínus, binárne operácie nad číslami). To, že Excel "nepozná" unárne plus, ber ako fakt.

Áno, prvé pole je to, čo si napísal.

Makro pridáš do Excelu tak, že otvoríš editor makier (Alt+F11) a pridáš ho na ten list, kde ho chceš mať. V novších verziách Excelu si tuším musíš zapnúť Developer toolbar v nastaveniach, aby si mal prístupné ikonky v menu. Makro funguje úplne jednoducho - pri každej zmene hodnoty v bunke odstráni začiatočné a koncové medzery (mení len bunky, v ktorých nie sú vzorce).

Nie je mi jasné, ako si myslel, že budeš pracovať s cudzími tabuľkami. Pôvodne si v nich chcel prepísať všetky vzorce tak, aby používali TRIM? Ak áno, tak potom je lepšie skôr doplniť to makro, ktoré upravuje hodnoty už pri vkladaní.

Dá sa spraviť aj to jedno tlačidlo, ktoré by vyčistilo cudziu tabuľku. Ak nechceš dávať makro priamo do každého takého excelovského zošita, tak si pohľadaj niečo o tom, ako vytvoriť Excel Add-In.

1, Co som pridal tie obrazky ze ide ratat s booleanmi aj bez konverzie..
preco mi to islo ako na tych obrazkoch bez konverzie a preco sa to muselo konvertovat pri vzorci toho chlapika ?

2, Ak sa da prechytracit SUMIF aby sa dala TRIMovat cez SUMPRODUCT, je mozne nejako inak prechytracit aj ostatne funkcie, kde sa neda puzit TRIM ?
COUNTIF, VLOOKUP....

1. Lebo vždy tam máš binárny operátor a vždy je jeden operand číselný, takže druhý operand sa automaticky prekonvertuje na číslo.

2. COUNTIF vieš tiež prepísať na SUMPRODUCT, pričom jediným argumentom bude tá podmienka (prekonvertovaná na číslo 0/1). Pri ostatných funkciách väčšinou nezostáva nič iné, len použiť maticové vzorce (array formulas).

Uz som 2 dni zamrznuty si pridat to 'makro' a pouzit ho a potom z toho urobit add-in.
To makro vobec nie je nikde vidiet v ziadnom liste makier a neda sa spustit ani nic.

Predtym ked som necital toto:

www.rondebruin.nl/win/personal.htm

som nejako dal to 'makro' do add-inu bez toho aby bolo v personal skrytom sheete, ale aj tak to neislo. Vygooglil som milion stranok o personal sheete a o add-inoch, vsade sa pise to iste. Neviem preco to nejde. Uz som aj vymazal personal, potom zalozil novy, vymazal ten add-in.
Teraz som nanovo v takomto stadiu najprv cez personal sheet isiel:

Keby si to makro umiestnil do listu, v ktorom chceš orezávať medzery, tak by sa automaticky spúšťalo pri každej úprave bunky v ňom. Všimni si, ako je to makro definované:

Private Sub Worksheet_Change(ByVal Target As Range)

- Private definuje viditeľnosť makra,
- Worksheet_Change je špeciálny názov makra, ktoré sa spúšťa automaticky pri zmene na liste,
- Target je parameter, s ktorým sa toto makro automaticky spúšťa.

Ty chceš ale spúšťať makro manuálne, takže okrem toho, že si ho správne umiestnil do modulu, tak:
- Private zmeníš na Public,
- Worksheet_Change zmeníš na nejaký rozumný názov,
- odstrániš parameter Target, takže tam zostanú len prázdne zátvorky,
- v makre nahradíš Target za ActiveSheet.UsedRange.

1, Az teraz to bude fungovat ako som cely cas chcel a aj pisal:
Hocikedy si zapnem moj excel2010, nic automaticky tam nebude robene, nic sa menit nebude same od seba. Az ked sa rozhodnem, ze ked idem robit s cudzimi tabulkami, tak na nieco kliknem a TRIM-ne mi to cele. Je to ok takto?

2, Podla tvojich instrukcii som to zmenil. Je to spravne ?

Ako vidis, dal som to iste makro aj do personalu a aj do add-inu. Chcel som vidiet rozdiel. Ale ziaden nie je. Ci ? Personal sa otvara ako skryty pri kazdom spusteni excelu, ale add-in az po povele uzivatela ak sa v nom nachadza nieco co chce uzivatel spustit ?
Podla teba, ako je lepsie si uchovavat makra, v personal, ci add-ine ?
Viac krat som sa pokusal zmenit meno modulu v add-ine, napr. ako je aaaaa v personale, ale nechce sa to uchovat. Stlacil som aj hned po zmene control+s, ulozilo sa to, ale po dalsom spusteni excelu sa zmena nezachovala, nevies kde moze byt chyba ?

3, Nase makro teraz funguje na Sheet samostatne. Ak sa mi dostane do ruk subor (workbook) s 40 sheetmi, mozes mi prosim upravit toto makro aby to urobilo TRIM na cely workbook ?

4, Chcel som zmenit celu farbu GUI excelu na ciernu. Islo v nastaveniach iba ribbon a to okolo, ale bunky svietia stale nabielo, v noci z toho bolia oci. Pocul som ze v 2010 sa to da nejako zmenit, ale nepodarilo sa mi to vypatrat. Vies ako sa to robi ?

Dakujem

1. Áno, takto je to OK.

2. Áno, je to správne.

Rozdiel medzi Personal Macro Workbook a Excel Add-In je hlavne v tom, ako ich môžeš preniesť na iný počítač. V prípade Personal musíš makrá kopírovať ručne priamo vo VBA editori - Personal Macro Workbook máš len jeden. Oproti tomu Excel Add-In stačí len prekopírovať ako jeden súbor a zaregistrovať v Exceli - Excel Add-Inov môžeš mať zaregistrovaných viacero. Čo je lepšie záleží od toho, čo potrebuješ.

Čo sa týka premenovania modulu, tak s takýmto problémom som sa ešte nestretol. Ale skúsil by som vytvoriť celý Add-In nanovo, aby sa modul volal už pri prvom uložení tak, ako chceš.

3.

Public Sub Trim_cely_Sheet()
    Dim aWorksheet As Worksheet
    Dim aCell As Range
    Dim aString As String
    
    For Each aWorksheet In ActiveWorkbook.Worksheets
        For Each aCell In aWorksheet.UsedRange
            If Not aCell.HasFormula Then
                aString = Trim(aCell)
                If aCell.Value <> aString Then
                    aCell.Value = aString
                End If
            End If
        Next aCell
    Next aWorksheet
End Sub

4. Neviem o tom, že by sa to v Exceli 2010 dalo nejako špeciálne nastaviť. Excel zobrazuje pozadie buniek podľa farebnej témy, ktorá sa nastavuje priamo v nastaveniach Windowsu (pravý klik na plochu / Prispôsobiť / Farba okna / Rozšírené nastavenie vzhľadu... / Okno). To nastavenie sa ale týka všetkých okien, nie len Excelu - to by ti ale mohlo skôr vyhovovať, ak ti vadí biela farba všeobecne. Ja používam f.lux, s ktorým som veľmi spokojný.

Ked som ukladal makro do add-inu, pise error:

Chýba ti tam kľúčové slovo Sub.