Excel - vytvořit upozornění barevnou buňkou

Dobrý den,
pokusil jsem se udělat barevné upozorňování buňky, ale na něco nestačím. Každý stroj jde po 500 motohodinách do servisu. Nevím, jak to nastavit, aby se motohodiny po servisu opět automaticky vynulovaly a začaly počítat znovu od nuly do 500.
Těch strojů je více jak 100. Já udělal jen modelový soubor s 5 řádky. Pomůže někdo?
Děkuji

Posielam nástrel riešenia bez pomocných buniek, s využitím maticových vzorcov.
Vzhľadom na odkazovanie sa z vyhodnocovacej tabuľky na hárky jednotlivých strojov pomocou vzorca je vhodné hárky nazvať presne tak, ako sú uvedené vo vyhodnocovacej tabuľke.
Takže pokiaľ je vo vyhodnocovacej tabuľke inventárne číslo 45 a popis stroja Terex TL 70, tak v zmysle Tvojho systému označovania hárkov je potrebné aby si hárok nazval presne šablonou: "inv.č.-popis" vrátane medzier!
Tabuľku prvého stroja som upravil tak, aby si si mohol odskúšať zmenu semaforu na základe zadania posledného servisu.
Pochopil som to tak, že sa zadáva počet odpracovaných hodín a Mth je vypočítavané. Máš tam teda doplnený príslušný vzorec...
Vyhodnocovacia tabuľka je uzamknutá, prístupné sú len bunky stĺpca A, B a G. Ostatné sa vypočítava samé.
Uzamknutie je bez hesla, takže si to vieš odomknúť.
Bunka G4 má nastavené overovanie dát, nedovolí zadať menšiu alebo rovnú hodnotu, ako je prvá uvedená hodnota Mth, nebolo by totiž na základe čoho vyhodnocovať a vrátilo by chybu.
Nie je to ešte definitívum, pretože mám pocit, že niektoré časti vzorca sú nadbytočné a šlo by to aj zjednodušiť a preto som ešte neriešil:
- aby si vyhodnocovacia tabuľka sama zistila prešetrovaný rozsah a nemusel by si prednastavovať pole od 3. po 100. riadok
- otázku zadania presného počtu Mth pri výkone servisu. Mám však za to, že to musí byť vždy presná uvedená hodnota (do bunky G4 sa zadá presná hodnota ako je v tabuľke stroja, veď predsa do servisu ide stroj po uvedených Mth, ty však uvádzaš servis po 7 150 h avšak v tabuľke táto hodnota nie je... Prečo sa hodnota Mth neuvedie v čase odovzdania do servisu? Týmpádom aj prvá uvedená hodnota odpracovaných hodín nie je od servisu ale od posledného záznamu, čo je divné... Správna truhlikova poznámka je, že prenos žiadnych hodín by sa nemal vykonávať, počítať čas do nového servisu je predsa potrebné počítať od nuly od okamihu prevádzky po vykonaní servisu...)

P.S. Ak som to všetko dobre pochopil
A pravdepodobne budú nevyplnené bunky (tankovanie bez uvedeného výkonu) robiť problém...

Zlom z červenej na oranžovú nastáva pri zadanej revízii medzi presnými uvedenými hodnotami: 7 164 (č) a 7 174 (or)
Zlom z oranžovej na žltú nastáva pri zadanej revízii medzi presnými uvedenými hodnotami: 7 194 (or) a 7 204 (ž)
Zlom zo žltej na neoznačenú nastáva pri zadanej revízii medzi presnými uvedenými hodnotami: 7 244 (ž) a 7 254 (bez)
(v zmysle nasčítavaných hodnôt prevádzky a ich príslušnosti do intervalov farieb)
Pokiaľ sa zadá totožná hodnota Mth revízie s uvedenou hodnotou, do Mth sa započítava aj príslušný riadok, pokiaľ sa zadá hodnota revízie iná, než uvedená v tabuľke, nasčítavajú sa hodnoty prevádzky od riadku nasledujúceho po revízii.
(Mám tam chybičku - hranica je napr 481 ale započítava už aj akúkoľvek hodnotu nad 480 - zabudol som počítať s desatinnými hodnotami v nastavení podmieneného formátovania)
Ako vidno, je potrebné ujasniť, ako je to so stavom výkonu v okamihu revízie a pod. Z uvedeného dôvodu je môj návrh len uvedením princípu, ako sa na to dá ísť...
Hárky ostatných strojov (neobmedzene) a ich naviazanie na vyhodnocovaciu tabuľku sú analogické.

Díky za pomoc.
Já ty vzorce a ostatní budu studovat a testovat asi déle, než ty si to psal.
Já vím, že v těch jednotlivých listech je čurbes. Hrozně jsem se nad tím vztekal. Pomalu každá pátá tabulka je jinak posazená
a má jinak sloupce. Hrabou se v tom asi 3 lidé a každý tam vnáší své chytrosti. Mám ještě jednu tabulku kde jsou značené servisy
pro jednotlivé stroje (poslední servis a budoucí servis).
Tam, kde v listech nebyly, tam jsem je ručně doplnil. Zkusím podle tvého vzoru doplnit ještě pár dalších řádek do souhrnu a uvidím.
Mně třeba nechtěly fungovat odkazy na jiný list, protože jsem měl v názvu listu mezery (zlobilo podmíněné formátování).
Koukal jsem, že ty jdeš na to jinak (CONCATENATE) a mezery tam můžeš mít. Myslel jsem, že to nebude tak složité, ale je.
Znovu děkuji a jdu testovat.

Takže...

Logika funguje tak ako v predošlom súbore.
Zmena je:
- v upresnení intervalov farieb (viď legendu v hárku Souhrn) n
- v automatizácii súčtovania výkonov (spodná hranica je dynamická a teda nie je potrebné zadávať rozsah po 100. riadok, rozsah sa riadi automaticky podľa počtu vyplnených buniek v stĺpci B. Pritom som zároveň nahradil CONCATENATE tak, ako som ukázal vyššie)
- v automatizovanom preberaní stavu výkonu pri prevzatí do servisu
- v prechode späť na hárok stroja po dvojkliku na bunku stavu predchodzieho servisu v hárku Souhrn
- spätnej konverzii "tabuľky" v hárku Souhrn na bežný rozsah (spôsobuje problémy v adresovaní buniek)

Automatizované prevzatie stavu pri prevzatí do servisu: dvojklikom na údaj stavu Mth v stĺpci B hárka stroja sa táto hodnota prevezme do vyhodnocovacej tabuľky a ukáže vyhodnotenie (napr pre testovanie alebo zváženie reálneho využitia)

Opakujem, že toto nemusí byť úplne správna činnosť, pracuje to na základe toho, ako som to pochopil ja (napr, že stav pri preberaní do servisu musí byť niektorý zo stavov uvedených v tabuľke a nie nejaký stav "medzi"), ale prax môže vyžiadať úpravu niektorých vyhodnocovaní.
Otestuj...

P.S. Automatika pracuje prostredníctvom makier, čiže sa jedná o súbor xlsm a teda pri prvom spustení bude potrebné odsúhlasiť spúšťanie makier... Tým sa súbor zmení na dôveryhodný a pri ďalšom spúšťaní (pokiaľ sa nezmení cesta k súboru) už odsúhlasenie makier žiadať nebude.

Ahoj,
tak se to snažím testovat a proniknout do vzorců. Asi je to velké sousto.
Zadal jsem do souhrnu 10 strojů, ale někde dělám chybu. V jednotlivých listech strojů jsem doplnil na řádek 3 motohodiny které jsem vytahal ze servisního souboru. Od těchto motohodin se odvíjejí všechny další servisy. V některých listech od toho prapůvodního servisu už proběhl i další servis, ale ten technik ho tam nezapsal (nebo možná vůbec neproběhl – zapomnělo se na něj). Nevím. Proto je tady pokus o nějaké zobrazení, které udeří do očí.
Každopádně, nevím, jak dostat ta servisní čísla do listu „Souhrn“. Když se pokusím zapsat číslo do sloupce „Předchozí servis“ tak se buňka brání. Od sloupce G se odvíjejí všechny další sloupce.
Mimochodem, ty vzorce už jsou opravdu složité. Nepochopil jsem úplně ty počty uvozovek a možná apostrof. (CONCATENATE("'";A4;"-";B4) ;"'!B:B").
Při hodinách Excelu jsem asi hodně chyběl. O trojhmatu CTRL+SHIFT+Enter slyším poprvé.
Můžeš prosím kouknout, proč funguje pouze tvoje řádka?
Díky

Pozriem podvečer. Tie veci v úvodzovkách sú čiastkové obsahy, z ktorých sa skladá výsledná adresa. Každý čiastkový obsah musí byť v úvodzovkách, pokiaľ tá čiastka nie je odkazom. A áno, je to apostrof v úvodzovkách, aj keď som žil v tom, že pokiaľ sa odkazujem na hárok toho istého súboru, malo by to ísť aj bez apostrofu, len s výkričníkom (ktorý je oddeľovačom medzi odkazom na hárok a odkazom na bunku alebo oblasť).CSE vzorce (Control-Shift-Enter) sú maticové vzorce, ktoré narábajú s oblasťami buniek ako maticami a aj mne trvalo, kým som po niekoľkých pokusoch získal zápis, ktorý robil, čo som od neho chcel.
Ešte som Ti zabudol napísať, že prvý nástrel (bez dynamického posúvania spodného riadka údajov na základe ich skutočnej naplnenosti) bol nadefinovaný natvrdo po 32. riadok (pretože potiaľ siahali údaje na hárku prvého stroja, s ktorým som laboroval). Preto Ti bez úpravy nemohli vzorce korektne fungovať po pripisovaní ďalších riadkov (ale asi nie v tom je terajší problém). A ono by bolo fajn, keby som bol mohol nechať vo výsledkovej tabuľke formát "tabuľky", ktorá pri zahájaní ďalšieho riadka sama doplní vzorce na základe predošlého riadku, ale priznám sa, že "tabuľky" s veľkým "T" boli zavedené v neskoršej verzii Excelu a ja osobne som zo zvyku pokračoval v štandardných tabuľkách, preto nemám vžitý ich spôsob spolupráce s makrami. Záznamník makier ukázal, že kód zaznamenáva svoj názov tabuľky, kde nenastáva odkaz na konkrétnu bunku. Preto som sa vrátil na bežný rozsah, ale až bude čas, skúsim preskúmať aj túto oblasť, aby sme využili výhody "tabuliek".
Naďalej mi však chýba vyjasnenie si, ako sa eviduje stav, keď sa stroj odovzdá do servisu. Možno mi Tvoja príloha dá ďalšie, chýbajúce informácie (momentálne už nestíham pozrieť). Ozvem sa

K těm servisům,
Nevím, proč jsou ty počty motohodin takové, jaké jsou. Možná stroje koupili z druhé ruky a začali to počítat od čísla které získali. Myslím, že je jedno kdy proběhne další servis. Tím myslím, že není důležité, jestli proběhne při počtu motohodin +485, nebo +495 nebo +504. Přesně to v praxi určitě není možné. Horší je, že ty servisní prohlídky nejsou vždy zaznamenané v listu stroje. Myslel jsem, jestli by nešlo udělat to tak, že když technik napíše do sloupce slovo „Servis“ počítadlo nasčítávaných motohodin se vynuluje a jelo by opět od začátku. A bylo by jedno, jestli to napíše při 490 nebo při 505 motohodinách.
Vím, že v praxi to někdy chodí tak, že když je stroj na stavbě a je daleko od servisní stanice, tak se nevrací, dokud neudělá práci. Potom může dojít k většímu překročení 500 Mth, ale proto tam bude ten semafor, který signalizuje blížící se servis a bude záležet na vedoucím, zda tam tu mašinu pošle.
Nevím co víc ještě napsat k těm Mth do servisu.

Tak ešte raz
Teraz som si všimol, že tam máš údaj "Servis" v stĺpci G stroja , tak to chápem tak, že pri tomto stave bol stroj servisovaný.
Tak som upravil vzorce tak, že vo vyhodnocovacej tabuľke je tento riadok uvedený ako stav pri poslednom servise a "semaforový údaj" je súčtom výkonov od tohto riadka (vrátane) až nadol.
V prípade, že nie je nikde inde uvedený servis, než vo východzom riadku, tak výsledkom je rozdiel medzi východzou a koncovou hodnotou.
Napriek tomu je ešte potrebná oprava, pretože napr v hárku stroja 87... sa do súčtu výkonov započítava i hodnota výkonu v riadku, v ktorom je uvedený "Servis" avšak tento výkon je rozdiel od predošlého stavu a teda nemá byť započítaný. Ale to už dnes odo mňa nechci, ten vzorec je taký katastrofický, že už teraz sa mi z neho točí hlava, než aby som hľadal, kde do neho zasiahnuť a doplniť o posun o riadok nižšie pre súčtované pole
Chápem to dobre? Uffff

1. Jsem pitomec. Když jsem tam nakopíroval těch dalších 9 listů myslel jsem na to, že musím dát pozor na mezery v názvu stroje, ale že jsou okolo pomlčky mezery jsem přehlédl.
2. V mašinách je počítadlo motohodin jako v osobáku kilometry. Zapisuje se sloupec B a „Céčko“ se dopočítává.

Na vysvětlenou: Každý řidič má svůj čip, který přiloží ke čtečce firemní pumpy a zadá PIN. Potom má zadat Mth, potvrdit a teprve potom tankovat. Někteří po zadání PINu potvrdí prázdné pole a tankují. Prostě Mth nezadají. Proto je někde prázdná buňka. No a to dostane další člověk, který zadá do karty stroje údaje které vidí (bez motohodin). Prostě to tak je. Co naděláme.
E-mail ti pošlu zítra. Teď ještě budu chvíli testovat a pak musím vypadnout.

Opravdu díky za práci. V začátku jsem si nemyslel, že to bude takhle složité.

Jestli se chceš trochu zasmát a nejen pracovat, koukni na moje stránky. Mám největší sbírku kreslených vtipů pana Pavla Kantorka (pokud ti to jméno něco říká). Samozřejmě s jeho souhlasem, ještě, než zemřel.
http://kantorek.webzdarma.cz/index.html

Napadlo ma skúsiť to vrátiť do formy "Tabuľky" s veľkým "T" a ono to funguje... Ja ten Excel milujeeem! Takže ak chceš pridať nový stroj, stačí ho v hárku Souhrn zapísať do ďalšieho riadka (samozrejme až po odomknutí listu) a vzorce sa preberú automaticky. Po vytvorení hárka stroja pre tento nový riadok vyhodnocovacej tabuľky začnú odvolávky pracovať po zadaní inventárneho čísla a popisu v hárku Souhrn a nazvaní nového hárka stroja názvom v zmysle princípu "Inventární č.-Popis".
A vrátil som Ti Tvoje intervaly...
A malý trik:
Ak máš X hárkov, v ktorých potrebuješ ujednocovať vzhľad, formáty, zadať rovnaké vzorce atď atď, stačí ak ich všetky vyberieš (označíš uško hárka prvého z množiny rovnakých hárkov - Shift - uško posledného z množiny rovnakých hárkov) a v aktívnom hárku vykonávaš úpravy: vykonajú sa vo všetkých hárkoch naraz! Juchuuuu!!! Vystúpenie z označenej množiny hárkov sa vykoná označením nevybraného hárka a v prípade, že sú označené všetky hárky, vystúpenie z označenia množiny nastane vybratím iného než aktívneho hárka. Odomykanie a zamykanie musíš však jednotlivo po hárkoch, to nejde naraz.
Momentálne ma nič iné nenapadá, skúšaj...

Je vidět, že Excel miluješ. Díky a jdu testovat.

Keďže to vyzerá, že by to mohlo byť podľa Tvojich predstáv, tak som tam ešte doplnil kontrolu toho, čo som spomínal v bode 7 v predminulej odpovedi (schválne ponechanie nezávislého výpočtu maticou, aby udrelo do očí, že súčet nie je rovný 500...)
Ono, keď Ťa to nenapadne, že si máš všímať, či súčet dá alebo nedá 500, tak si to nevšimneš.
Tak som dorobil kontrolu súčtu podmieneným formátovaním. Ak súčet "odpracované od servisu" a "ostávajúce do servisu" nedá 500, písmo týchto dvoch stĺpcov a názvu stroja sa zmení na tučné tmavočervené a podfarbenie na svetlosivé.
Znamená to, že v hárku tohto stroja je nejaký kiks (nevyplnená položka započítavaná do sčítavania, preklep a pod).
A ešte jedná drobnosť:
Upravil som zadávanie krajných hodnôt intervalov pre "semafor" na pohodlnejšie: sú zadávané do prvých troch buniek stĺpca A (modré písmo) a do príslušných koncových buniek intervalov sa preberajú samé vzorcom.

Ahoj,
zatímco ty jsi psal svůj koment, já psal také. Mohl bys prosím ještě kouknout kde dělám chybu? Ještě jsem udělal další dva sloupce u jednotlivých strojů a v „Souhrnu“. Jeden sloupec má datumy, když jde stroj do servisu (v okamžiku „odentrování“ slova „Servis“ v tabulce stroje), aby to bylo v „Souhrnu“ vidět na první pohled.
Zde prosím o kontrolu vzorců, zda to tak může být.
Druhý sloupec „Stavba“ na kartě „Souhrn“ by měl zobrazovat poslední místo, kde se stroj vyskytuje. Myslím, že správný vzorec je na kartě stroje „20-Skuska“, ale nedaří se mi ho použít do karty „Souhrn“. Někde stále dělám chybu a nevím kde.
Pomůžeš ještě prosím? Tvůj soubor prostuduji.
Děkuji

Dodatočne ma napadlo, prečo čerpať dátum posledného servisu zo samostatného nového stĺpca v hárku strojov. Veď dátum tam už predsa je v stĺpci A, takže stačí sa na neho odvolať z posledného riadka výskytu slova "Servis". Potom som si spomenul, že si chcel mať dátum posledného servisu v samostatnom stĺpci aby bol očividnejší. Reku... prečo, keď predsa na to máš očividný zápis "Servis" a potom som si všimol, že stĺpec, kde uvádzaš slovo "Servis" je vlastne stĺpcom pre "Převoz". Takže pre odstránenie duplicity dátumu by som to skôr riešil tak, že by som namiesto Tvojho nového stĺpca "Den servisu" vytvoril stĺpec "Servis" (kde by stačilo uviesť "Áno" alebo "1") a tým by stĺpec "Převoz" slúžil skutočne pre svoj účel a nie pre údaj o vykonaní servisu. A potom by sa dátum servisu do vyhodnocovacieho hárka "Souhrn" preberal z tohto stĺpca, vyhľadaním riadka posledného výskytu slova "Áno" alebo "1" a odvolal sa na dátum v stĺpci A tohto riadka. Veď pre prehľad, resp. vyhodnotenie je predsa podstatný hárok "Souhrn", kde sa už nemusíš zorientovávať v zmesi údajov, ako je tomu v hárkoch strojov a dátum je tam jasne daný...
To len na margo veci. Ale ak to chceš mať takto, je to Tvoja vec...

Plním soubory daty a při té příležitosti testuji. Je toho hodně. Zatím bez problémů. Díky

Okrem toho sa dá vyrobiť velmi jednoduché makro, ktoré ti tie hárky vyrobí samé na základe zoznamu strojov zapísaných do hárka Souhrn, na jediné stlačenie tlačidla, takže potom by už stačilo tie hárky len napĺňať.
Podľa toho, či máš nejaký systematický zdroj týchto dát, by sa tie dáta dali aj importovať...

Hromadné předdefinování archů strojů používám, jak si mně to poradil. to ušetří spoustu práce. S daty to jde pomaleji, je potřeba vyplnit zpětně alespoň 500 Mth, aby se bylo od čeho odrazit pro další servis.
Nejvíc mě zdrželo zadávání datumů, protože jednou bylo zadané jako datum a jednou jako text a různě na přeskáčku. Musel jsem to všechno sjednotit. Nemáš nějaký jednoduchý fígl jak z textu udělat datum? Když to bylo jako text, nezobrazovalo se to v "Souhrnu".
Přes formát mi to nějak nešlo. Dělám to přes doplněk "Kutools", ale ten je pouze měsíc zdarma. Zatím funguje.

Označíš celý stĺpec kde sú "dátumy" a zvolíš nástroj "text na stĺpce" (myslím, že karta "Údaje" - som mimo pc). Preženie Ťa to cez sprievodcu, ty len odsúhlasuješ, až kým neprídeš na okno, kde sa volí formát výsledných dát a tam si cez OptionButtony hrubo vyberieš "dátum" a dáš ukončiť. A máš dátumy. Potom si na tento výsledok môžeš aplikovať dátumový formát, aký potrebuješ, cez "formát bunky". Prevody sa uskutočňujú vždy týmto spôsobom. Samotný formát bunky Ti druh výstupných dát spätne, keď už sú zadané hodnoty, nezmení. Kutools na to vôbec nepotrebuješ.