Zvýraznění řádku barvou, pokud je buňka prázdná. Výběr buněk pomocí vyhledávacího okna. Jak změnit barvu řádku na základě textové hodnoty jedné z buněk

Ke splnění tohoto úkolu využijeme možnosti podmíněného formátování.
Vezměme si tabulku obsahující seznam objednávek, jejich termíny, aktuální stav a cenu. Zkusme, aby se jeho buňky vybarvily samy v závislosti na jejich obsahu.

Pokyny pro Excel 2010

ZAPNOUT TITULKY!

Jak to udělat v Excelu 2007

ZAPNOUT TITULKY!
Vyberte buňky s cenami objednávky a kliknutím na šipku vedle tlačítka „Podmíněné formátování“ vyberte „Vytvořit pravidlo“.

Vyberme čtvrtou položku, která nám umožňuje porovnat aktuální hodnoty s průměrem. Zajímají nás nadprůměrné hodnoty. Kliknutím na tlačítko „Formátovat“ nastavíme barvu buněk.


Potvrdíme naši volbu a buňky s nadprůměrnou cenou se zbarví do modra a upozorní nás na drahé zakázky.


Vybereme buňky se stavy objednávky a vytvoříme nové pravidlo. Tentokrát použijeme druhou možnost, která nám umožní zkontrolovat obsah buňky. Vyberte „Text“, „obsahuje“ a zadejte slovo „Dokončeno“. Nastavíme zelenou barvu, potvrdíme a dokončená práce se změní na zelenou.


No, udělejme další pravidlo, že zpožděné objednávky barví červeně. Zvýrazňujeme termíny dokončení objednávek. Při vytváření pravidla opět vybereme druhou položku, ale tentokrát nastavíme „Hodnota buňky“, „menší než“ a do dalšího pole zadáme funkci, která vrátí dnešní datum.


„OK“ a dostali jsme vesele vyzdobený stůl, který nám umožňuje vizuálně sledovat průběh objednávek.


Všimli jste si, že stavy se nastavují výběrem z rozevíracího seznamu hodnot? Jak takové seznamy vytvořit, jsme si popsali v návodu.

Jak to udělat v Excelu 2003

ZAPNOUT TITULKY!
"Podmíněné formátování" v nabídce "Formát". To bude vyžadovat trochu více ruční práce. Takto bude vypadat nastavení pro náš první úkol – vybarvování buněk s hodnotami vyššími než průměr.


Budete muset ručně zadat funkci „=PRŮMĚR ()“, umístit kurzor do hranatých závorek, kliknout na tlačítko vedle a pomocí myši zadat požadovaný rozsah.
Ale princip akce je stejný.
Podmaňte si Excel a brzy se uvidíme!

Datum: 15. listopadu 2015 Kategorie:

Ahoj všichni. Dnešní příspěvek se chci věnovat způsobům výběru buněk v aplikaci Microsoft Excel. Pokud již víte, jak zvýraznit buňky, projděte text, možná si nejste vědomi některých funkcí programu a nevyužíváte je.

A tak je výběr skupin buněk nezbytný, aby se s nimi provedla nějaká společná akce: nebo atd. Jak můžete vybrat buňky v Excelu? Pojďme na to přijít!

Jak vybrat všechny buňky v listu aplikace Excel

Okamžitě odpovím na nejčastěji kladenou otázku k tématu článku. Jak vybrat všechny buňky listu najednou? Nabízím vám dvě metody, podle toho, která se vám líbí nejlépe, použijte jednu:

Výběr obdélníkového rozsahu buněk

Řekněme, že potřebujete vybrat obdélníkovou skupinu buněk, abyste je naformátovali pro svůj datový typ. Mohu nabídnout 5 způsobů zvýraznění a vy si vyberete, který je pro vás v tuto chvíli vhodný:

Výběr pomocí příkazu "Go".

Úplně vyberte řádky a sloupce

Pokud potřebujete vybrat celý sloupec nebo řádek, je zde několik možností:

• Klikněte myší na číslo řádku nebo název sloupce. Pokud potřebujete vybrat několik sousedních čar, podržte levé tlačítko myši na čísle čáry a přetáhněte výběr přes čáry, které chcete vybrat. Totéž děláme se sloupci
• Umístěte kurzor do libovolné buňky řádku, který chcete vybrat, a kombinace lisů Shift+Mezerník. Chcete-li vybrat sloupec, použijte kombinaci Ctrl+Mezerník
• Chcete-li vybrat nesousedící řádky a sloupce − štípnoutCtrl a klikněte podle názvů sloupců a čísel řádků. Po dokončení uvolněte Ctrl.

Výběr nesousedících rozsahů

Pokud potřebujete vybrat několik buněk, které spolu nesousedí, udělejte to pomocí jedné z navrhovaných metod:

Výběr na více listech najednou

Pokud list stejného dokumentu obsahuje identické tabulky s různými údaji, můžeme provádět operace na všech kartách současně. To šetří čas a snižuje pravděpodobnost chyby. Potřebujeme například záhlaví každé tabulky na několika listech. Není třeba to dělat na každé záložce zvlášť – vyberte všechny listy a udělejte vše najednou.

Chcete-li vybrat stejnou oblast na více listech, nejprve vyberte požadované listy. Aktivujte první list ze seznamu, podržte Ctrl a kliknutím na popisky všech listů jej vyberte.

Když jsou vybrány všechny potřebné listy, můžete provádět operace. Vezměte prosím na vědomí, že v řádku názvu vedle názvu souboru je nápis „ [Skupina]" To znamená, že Excel je připraven zpracovat skupinu listů.

Změna názvu při výběru skupiny listů

Dále na aktivním listu vyberte požadované rozsahy, proveďte změny, vyplňte obecné údaje a vzorce. Vše, co uděláte, se použije na všechny vybrané listy. Po dokončení dávkového zpracování nezapomeňte zrušit výběr listů. Chcete-li to provést, klepněte pravým tlačítkem myši na jakoukoli zkratku seskupeného listu a vyberte z kontextové nabídky Zrušit seskupení.

Podmíněný výběr buněk

Microsoft Excel může zvýraznit skupinu buněk na základě jejich obsahu. Ne všichni uživatelé si tuto funkci uvědomují, i když její použití může být velmi užitečné.

Spusťte příkaz Domů – Úpravy – Najít a vybrat – Přejít. V zobrazeném okně klikněte na Vybrat.... Zobrazí se dialogové okno Vybrat skupinu buněk s následujícími možnostmi výběru:

Výběr buněk podle jejich obsahu

Výběr buněk pomocí vyhledávacího pole

Chcete-li otevřít okno hledání hodnoty, udělejte to Domů – Úpravy – Najít a vybrat – Najít(nebo stiskněte kombinaci kláves Ctrl+F). Objeví se vyhledávání.

Použití vyhledávání ke zvýraznění buněk

Do pole Najít: zadejte znaky nebo čísla, která chcete najít, a klikněte na Najít vše. V dolní části okna se zobrazí seznam buněk obsahujících vybraná data. Vyberte jednu nebo více buněk (podržte Ctrl) v seznamu, aby je Excel vybral. Chcete-li vybrat všechny nalezené buňky, vyberte jednu z nich a stiskněte kombinaci Ctrl+A.

K vyhledávání můžete použít speciální znaky:

• "?" - libovolný symbol
• „*“ - libovolný počet libovolných znaků

Chcete-li například najít všechny buňky, které začínají písmenem „A“, zadejte do vyhledávání „A*“.

To je vše o výběru buněk v MS Excel a další příspěvek chci věnovat . Jako vždy bude tento článek obsahovat mnoho užitečných věcí, které se vám ve vaší práci budou určitě hodit. Tak si to přečtěte, nebudete litovat!

Mimochodem, těším se na vaše dotazy k tomuto článku v komentářích!

Každý list sešitu Excelu je rozdělen do řádků a sloupců, jejichž počet závisí na verzi aplikace. Řádky a sloupce lze mazat, přidávat, spojovat a přesouvat. Dříve jsme se zabývali podmíněným mazáním řádků, podmíněným mazáním sloupců a přidáváním prázdných řádků do sešitů aplikace Excel. Níže zvážíme další akci prováděnou s řetězci - výběr.

Jak vybrat jeden řádek?

Existuje několik způsobů, jak zvýraznit řádek:

1. V otevřeném okně listu můžete jednou kliknout levým tlačítkem kurzorem na číslo požadovaného řádku;

2. také můžete v otevřeném okně listu nastavit značku výběru do libovolné buňky požadovaného řádku a použít kombinaci kláves Shift+Space (mezera);

3. Nakonec můžete nastavit značku výběru na první buňku požadovaného řádku a použít klávesovou zkratku Ctrl+Shift+šipka doprava. Navíc, pokud jsou v řádku data, pak první stisknutí této kombinace kláves vybere část řádku s daty a druhé stisknutí vybere celý řádek.

Jak vybrat několik řádků v řadě?

1. Vyberte první řádek požadovaného rozsahu a bez uvolnění levého tlačítka myši táhněte kurzorem šipky podél čísel požadovaných řádků;

2. vyberte první řádek požadovaného rozsahu a při stisknuté klávese Shift klikněte na číslo posledního řádku požadovaného rozsahu;

3. Klepněte na kurzor pro zadávání textu v adresním okně řádku vzorců, zadejte rozsah řádků (například 10:20) a stiskněte klávesu Enter.

Jak vybrat několik řádků, které nejsou v řadě?

Chcete-li selektivně vybrat několik řádků, musíte kliknout na čísla požadovaných řádků kurzorem se šipkou a současně stisknout klávesu Ctrl.

Jak vybrat řádky podle podmínky?

Pokud je potřeba selektivně vybírat řádky (pro formátování, kopírování či jiné účely), např. obsahující zadaný text nebo naopak neobsahující určitý text, můžete využít doplněk, který vám umožní urychlit výběr linek pro různé podmínky. Níže je dialogové okno, které umožňuje vyhodnotit možnosti tohoto doplňku.

Doplněk vám umožňuje:

1. Jedním kliknutím vyvoláte dialogové okno makra přímo z panelu nástrojů Excelu;

2. Najděte a zvýrazněte řádky v závislosti na nastavené podmínce a zadaných hodnotách,

Je možné zadat několik textových hodnot najednou oddělených znaménkem ";" (středník);

3. Vyberte jednu z osmi podmínek pro řádky s požadovanou hodnotou:

- shoduje se s požadovanou hodnotou;

- neodpovídá požadované hodnotě;

- obsahuje místní význam;

- neobsahuje požadovanou hodnotu;

- začíná s požadovanou hodnotou;

- nezačíná s požadovanou hodnotou;

- končí požadovanou hodnotou;

- nekončí požadovanou hodnotou.

4. Při hledání textu zvažte nebo ignorujte velká a malá písmena (rozlišujte mezi velkými a malými písmeny);

5. Když je nalezen řádek, je možné zvýraznit:

a) celá čára podél šířky listu;

b) část struny omezená použitým rozsahem (od první vyplněné buňky do poslední vyplněné buňky);

c) část řetězce omezená přiděleným (zvoleným) rozsahem vyhledávání.

6. Vyberte rozsah řetězců pro hledání zadaných hodnot a v případě potřeby přidejte omezení do vybraného rozsahu nastavením počátečního a koncového řetězce.

Řekněme, že jedním z našich úkolů je zadání informace o tom, zda zákazník v aktuálním měsíci provedl objednávku. Poté je na základě obdržených informací nutné zvýraznit buňky barevně podle podmínky: který z klientů neudělal za poslední 3 měsíce ani jednu objednávku. Pro takové klienty budete muset nabídku odeslat znovu.

To je samozřejmě úkol pro Excel. Program by měl takové protistrany automaticky najít a podle toho je barevně zvýraznit. Pro tyto podmínky použijeme podmíněné formátování.

Automaticky vyplňte buňky daty

Nejprve si připravíme strukturu pro naplnění registru. Nejprve se podmínečně podívejme na hotový příklad automatizovaného registru, který je znázorněn na obrázku níže:

Uživatel musí pouze uvést, zda klient provedl objednávku v aktuálním měsíci, poté by měla být do příslušné buňky vložena textová hodnota „objednávka“. Hlavní podmínka pro zvýraznění: pokud protistrana neučiní ani jednu objednávku po dobu 3 měsíců, její číslo se automaticky zvýrazní červeně.

Toto prezentované řešení by mělo automatizovat některé pracovní postupy a zjednodušit vizuální analýzu dat.

Automaticky vyplnit buňky aktuálními daty

Nejprve pro evidenci s čísly zákazníků vytvoříme záhlaví sloupců se zelenou barvou a aktuální měsíce, které budou automaticky zobrazovat časová období. Chcete-li to provést, zadejte do buňky B1 následující vzorec:

Jak funguje vzorec pro automatické generování odchozích měsíců?

Na obrázku vzorec vrací časové období od data napsání článku: 17.09.2017. První argument ve funkci DATA obsahuje vzorec, který díky funkcím ROK a DNES vrací vždy aktuální rok k dnešnímu datu. Druhý argument určuje číslo měsíce (-1). Záporné číslo znamená, že nás zajímá, jaký to byl měsíc v minulém čase. Příklad podmínek pro druhý argument s hodnotou:

• 1 – znamená první měsíc (leden) roku uvedeného v prvním argumentu;
• 0 je před 1 měsícem;
• -1 jsou 2 měsíce. zpět od začátku aktuálního roku (tj.: 10.1.2016).

Poslední argument je číslo dne v měsíci zadané ve druhém argumentu. Výsledkem je, že funkce DATE shromáždí všechny parametry do jedné hodnoty a vzorec vrátí odpovídající datum.

Jak vidíte, funkce DATE nyní používá hodnotu z buňky B1 a zvyšuje číslo měsíce o 1 vzhledem k předchozí buňce. Výsledkem je 1 – datum dalšího měsíce.

Nyní zkopírujte tento vzorec z buňky C1 do zbytku záhlaví sloupců v rozsahu D1:L1.

Vyberte rozsah buněk B1:L1 a vyberte nástroj: „HOME“ - „Buňky“ - „Formát buněk“ nebo jednoduše stiskněte CTRL + 1. V zobrazeném dialogovém okně na kartě „Číslo“ v části „Formáty čísel:“ vyberte možnost „(všechny formáty)“. Do pole „Typ:“ zadejte hodnotu: MMM.YY (vyžadována velká písmena). Díky tomu získáme zkrácené zobrazení hodnot data v hlavičkách registrů, což zjednoduší vizuální analýzu a zpříjemní ji díky lepší čitelnosti.

Věnovat pozornost! Když přijde měsíc leden (D1), vzorec automaticky změní datum na další rok.



Jak obarvit sloupec v Excelu na základě podmínky

Nyní musíte zvýraznit buňky týkající se aktuálního měsíce. Díky tomu snadno najdeme sloupec, do kterého potřebujeme zadat aktuální údaje za tento měsíc. Postup:

Sloupec pod příslušným záhlavím registru je automaticky zvýrazněn zeleně podle našich podmínek:

Jak funguje vzorec pro zvýraznění barvy sloupce podle podmínky?

Protože jsme před vytvořením pravidla podmíněného formátování pokryli celou oblast tabulky, abychom zadali data o případu, bude formátování aktivní pro každou buňku v tomto rozsahu B2:L15. Smíšený odkaz ve vzorci B$1 (absolutní adresa pouze pro řádky a relativní adresa pro sloupce) znamená, že vzorec bude vždy odkazovat na první řádek každého sloupce.

Automatické zvýraznění sloupce na základě aktuálního měsíce

Hlavní podmínka pro vyplnění buněk barvou: pokud rozsah B1:L1 obsahuje stejné datum jako první den aktuálního měsíce, buňky v celém sloupci okamžitě změní barvu na barvu zadanou v podmíněném formátování.

Věnovat pozornost! V podmínkách tohoto vzorce je poslední argument funkce DATE nastaven na 1, stejně jako vzorce v definování dat pro záhlaví sloupců registru.

V našem případě je to zelená výplň buněk. Pokud otevřeme náš registr příští měsíc, bude odpovídající sloupec zvýrazněn zeleně, bez ohledu na aktuální den.

Tabulková část je naformátovaná, nyní ji vyplňte textovou hodnotou „objednávka“ ve smíšeném pořadí klientů za aktuální a předchozí měsíce.

Jak zvýraznit buňky červeně na základě podmínky

Nyní musíme červeně zvýraznit buňky s počty zákazníků, kteří neudělali ani jednu objednávku po dobu 3 měsíců. Postup:

Čísla zákazníků jsou zvýrazněna červeně, pokud jejich řádek nemá v posledních třech buňkách pro aktuální měsíc (včetně) hodnotu „objednávka“.

Analýza vzorce pro zvýraznění buněk barvou:

Nejprve se vypořádejme se střední částí našeho vzorce. Funkce OFFSET vrací referenční posun rozsahu od základního rozsahu o zadaný počet řádků a sloupců. Vráceným odkazem může být jedna buňka nebo celý rozsah buněk. Volitelně můžete definovat počet řádků a sloupců, které mají být vráceny. V našem příkladu funkce vrací odkaz na rozsah buněk za poslední 3 měsíce.

Důležitá část pro naši podmínku barevného zvýraznění je v prvním argumentu funkce OFFSET. Určuje, od kterého měsíce má začít offset. V tomto příkladu se jedná o buňku D2, to znamená, že začátek roku je leden. U zbývajících buněk ve sloupci bude číslo řádku základní buňky přirozeně odpovídat číslu řádku, ve kterém se nachází. Další 2 argumenty funkce OFFSET určují, na kolika řádcích a sloupcích má být posunutí provedeno. Protože budeme provádět výpočty pro každého klienta ve stejném řádku, zadáme hodnotu offsetu pro řádky jako –¬ 0.

Zároveň pro výpočet hodnoty třetího argumentu (posun sloupce) použijeme vnořený vzorec MONTH(TODAY()), který v souladu s podmínkami vrátí číslo aktuálního měsíce v aktuálním roce. Od čísla měsíce vypočteného vzorcem odečteme číslo 4, to znamená, že v případě listopadu dostaneme posun o 8 sloupců. A například za červen - pouze 2 sloupce.

Poslední dva argumenty funkce OFFSET určují výšku (v počtu řádků) a šířku (v počtu sloupců) vráceného rozsahu. V našem příkladu se jedná o oblast buněk o výšce 1 řádku a šířce 4 sloupců. Tento rozsah pokrývá sloupce za předchozí 3 měsíce a za aktuální.

První funkce ve vzorci COUNTIF testuje, kolikrát se textová hodnota "objednávka" objeví ve vráceném rozsahu pomocí funkce OFFSET. Pokud funkce vrátí hodnotu 0, znamená to, že po dobu 3 měsíců nebyly žádné objednávky od klienta s tímto číslem. A v souladu s našimi podmínkami je buňka s číslem tohoto klienta zvýrazněna červenou barvou výplně.

Pokud chceme evidovat data zákazníků, je pro tento účel ideální Excel. Můžete snadno zaznamenat počet objednaných produktů a také data transakcí v příslušných kategoriích. Problém se postupně začíná objevovat s narůstajícím objemem dat.

Pokud je jich tolik, strávíme několik minut hledáním konkrétní pozice registru a analýzou zadaných informací. V tomto případě se vyplatí přidat do tabulky registrů mechanismy pro automatizaci některých pracovních postupů uživatele. To jsme udělali.