Excel - výběr podle části textu v buňce



Ahoj, chci se zeptat, mam tabulku, v té tabulce jsou tři sloupce, v prvním sloupci je název firmy AB, v druhém sloupci je vždycky popis nějaké transakce za určitý měsíc v roce, například: "nákup zboží XXX 01/18" a ve třetím sloupci částka. Já bych potřebovala, aby se mi pomocí nějaké funkce zobrazila vždycky ta částka ve třetím sloupečku podle dvou kritérií a to takových, že teda jedno z těch kritérií bude název firmy a druhé z těch kritérií bych chtěla aby se to soustředilo na buňku, kde je nějaký text a pak "01/18" a ten text není důležitý. Je to nějak možné ? Prostě zadat kritérium, například do sumifs, jen část toho textu v buňce? Díky moc a hezký den, Tereza
Jak správně píšeš funkce SUMIFS to řeší.Tady máš příklad: <http://leteckaposta.cz/455012320>
<p>protože jsou všechna data za prvními třemi sloupci číselná, můžete je zaokrouhlit tak, aby šířka každého sloupce byla jen čtyři znaky.</p><pre><code> full length shorter tiny    $143,573.39 $143.6k 144k 112.54% 112.5% 113 198,220,329 198.2m 198m </code></pre><p>na základě množství dostupného místa se můžete rozhodnout, kterou verzi chcete zobrazit. ti, kteří měli štěstí, že mají větší monitory, by všechno dostali. prezentace by elegantně degradovala pro menší monitory</p><p>samozřejmě, když je obsah buňky zkrácen, měli byste zobrazit celou hodnotu v tipu nástroje.</p><hr><p>aktualizace: @oskar zdůraznil v komentářích, že zobrazování čísel s různými velikostmi ztěžuje vizuální srovnání. tady je tedy variace, že porovnání relativních hodnot je důležitější než přesnost.</p><pre><code> revenue ($millions)  143.6 93.2 0.3 &lt; 0.1 </code></pre><p>(záhlaví v tomto příkladu je širší než data, ale jak zdůraznili jiní, lze to vyřešit nakloněním záhlaví pod úhlem 45 stupňů.)</p> <p>přiznávám, že jsem to nikdy předtím neudělal, takže došlo k trochu poškrábání hlavy. jednoduchá ukázková tabulka, která demonstruje:</p><pre><code>if exists (select * from sys.objects where object\_id = object\_id(n'[dbo].[mytable]') and type in (n'u')) drop table [dbo].[mytable] go create table dbo.mytable ( col1 int , col2 char(1) ) go insert dbo.mytable (col1, col2) values (1, 'a') insert dbo.mytable (col1, col2) values (1, 'b') insert dbo.mytable (col1, col2) values (1, 'c') insert dbo.mytable (col1, col2) values (2, 'a') insert dbo.mytable (col1, col2) values (2, 'b') insert dbo.mytable (col1, col2) values (2, 'c') insert dbo.mytable (col1, col2) values (3, 'a') insert dbo.mytable (col1, col2) values (3, 'b') insert dbo.mytable (col1, col2) values (3, 'c') </code></pre><p>pomocí parametru @sortstyle k rozlišení objednávek řazení se @sortstyle = 1 třídí podle <code>col1 asc, col2 desc</code> a @ sortstyle = 2 seřadit podle <code>col2 desc, col1 asc</code>.</p><pre><code>declare @sortstyle int set @sortstyle = 1 select col1 , col2 from dbo.mytable order by case when @sortstyle = 1 then col1 end asc, case when @sortstyle = 1 then col2 end desc, case when @sortstyle = 2 then col2 end desc, case when @sortstyle = 2 then col1 end asc set @sortstyle = 2 select col1 , col2 from dbo.mytable order by case when @sortstyle = 1 then col1 end asc, case when @sortstyle = 1 then col2 end desc, case when @sortstyle = 2 then col2 end desc, case when @sortstyle = 2 then col1 end asc </code></pre><p> <a href="https://dba.stackexchange.com/questions/4104/how-do-you-order-by-a-parameter/4111#4111">jak se objednáváte parametrem</a> pokrývá jednodušší případ třídění podle 1 sloupce.</p> <p>následující dotaz můžete použít k výpisu všech sloupců nebo sloupců vyhledávání napříč tabulkami v databázi.</p><pre><code>use adventureworks go select t.name as table\_name, schema\_name(schema\_id) as schema\_name, c.name as column\_name from sys.tables as t inner join sys.columns c on t.object\_id = c.object\_id where c.name like '%employeeid%' order by schema\_name, table\_name; </code></pre><p>k zobrazení všech objektů v databázích sql server 2005 nebo 2008 můžete použít zobrazení <a href="http://msdn.microsoft.com/en-us/library/ms186778.aspx">information\_schema</a> .</p><pre><code>select * from information\_schema.tables select * from information\_schema.columns </code></pre><p> <a href="http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/">http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/</a> </p> <p>sloupec můžete třídit do sestupného formátu a poté pouze získat hodnotu z n-tého řádku.</p><p>upravit::</p><p>aktualizováno podle požadavku na komentář.<strong> <em>upozorněn&iacute;</em> </strong>zcela nevyzkoušené!</p><pre><code>select dob from (select dob from users order by dob desc) where rowid = 6 </code></pre><p>něco takového by mělo fungovat pro oracle ... možná budete muset nejprve dostat syntaxi!</p> <pre><code>select substring(columnname, patindex('%[^0]%',columnname), 10) </code></pre> <p>můžete zvolit zobrazení dotazu; je to zaškrtávací políčko nazvané „zobrazit dotaz sql“ na stránce nastavení zobrazení (správce/struktura/zobrazení/nastavení).</p> <p>příkaz <code>merge</code> má složitou syntaxi a ještě složitější implementaci, ale v zásadě jde o spojení dvou tabulek, filtrování dolů na řádky, které je třeba změnit (vložit, aktualizovat nebo smazat), a poté provést požadované změny. vzhledem k těmto vzorovým údajům:</p><pre><code>declare @categoryitem as table ( categoryid integer not null, itemid integer not null, primary key (categoryid, itemid), unique (itemid, categoryid) ); declare @datasource as table ( categoryid integer not null, itemid integer not null primary key (categoryid, itemid) ); insert @categoryitem (categoryid, itemid) values (1, 1), (1, 2), (1, 3), (2, 1), (2, 3), (3, 5), (3, 6), (4, 5); insert @datasource (categoryid, itemid) values (2, 2); </code></pre><p><strong> cíl </strong></p><pre><code>╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 1 ║ 1 ║ ║ 2 ║ 1 ║ ║ 1 ║ 2 ║ ║ 1 ║ 3 ║ ║ 2 ║ 3 ║ ║ 3 ║ 5 ║ ║ 4 ║ 5 ║ ║ 3 ║ 6 ║ ╚════════════╩════════╝ </code></pre><p><strong> zdroj </strong></p><pre><code>╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 2 ║ 2 ║ ╚════════════╩════════╝ </code></pre><p>požadovaným výsledkem je nahrazení dat v cíli daty ze zdroje, ale pouze pro <code>categoryid = 2</code>. po výše uvedeném popisu <code>merge</code> bychom měli napsat dotaz, který spojuje zdroj a cíl pouze na klíče, a filtrovat řádky pouze v klauzulích <code>when</code>:</p><pre><code>merge into @categoryitem as target using @datasource as source on source.itemid = target.itemid and source.categoryid = target.categoryid when not matched by source and target.categoryid = 2 then delete when not matched by target and source.categoryid = 2 then insert (categoryid, itemid) values (categoryid, itemid) output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>to dává následující výsledky:</p><pre><code>╔═════════╦════════════╦════════╗ ║ $action ║ categoryid ║ itemid ║ ╠═════════╬════════════╬════════╣ ║ delete ║ 2 ║ 1 ║ ║ insert ║ 2 ║ 2 ║ ║ delete ║ 2 ║ 3 ║ ╚═════════╩════════════╩════════╝ ╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 1 ║ 1 ║ ║ 1 ║ 2 ║ ║ 1 ║ 3 ║ ║ 2 ║ 2 ║ ║ 3 ║ 5 ║ ║ 3 ║ 6 ║ ║ 4 ║ 5 ║ ╚════════════╩════════╝ </code></pre><p>realizační plán je: <img src="https://i.stack.imgur.com/fovhu.jpg" alt="merge plan"></p><p>všimněte si, že jsou obě tabulky plně naskenovány. můžeme to považovat za neefektivní, protože pouze řádky, kde <code>categoryid = 2</code> bude ovlivněno v cílové tabulce. zde přicházejí varování v books online. jeden chybný pokus o optimalizaci, aby se dotkl pouze nezbytných řádků v cíli, je:</p><pre><code>merge into @categoryitem as target using ( select categoryid, itemid from @datasource as ds where categoryid = 2 ) as source on source.itemid = target.itemid and target.categoryid = 2 when not matched by target then insert (categoryid, itemid) values (categoryid, itemid) when not matched by source then delete output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>logika v klauzuli <code>on</code> je použita jako součást spojení. v tomto případě je spojení úplným vnějším spojením (viz <a href="http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx">tento záznam books online</a> proč). použití kontroly kategorie 2 na cílových řádcích jako součásti vnějšího spojení nakonec povede k odstranění řádků s jinou hodnotou (protože neodpovídají zdroji):</p><pre><code>╔═════════╦════════════╦════════╗ ║ $action ║ categoryid ║ itemid ║ ╠═════════╬════════════╬════════╣ ║ delete ║ 1 ║ 1 ║ ║ delete ║ 1 ║ 2 ║ ║ delete ║ 1 ║ 3 ║ ║ delete ║ 2 ║ 1 ║ ║ insert ║ 2 ║ 2 ║ ║ delete ║ 2 ║ 3 ║ ║ delete ║ 3 ║ 5 ║ ║ delete ║ 3 ║ 6 ║ ║ delete ║ 4 ║ 5 ║ ╚═════════╩════════════╩════════╝ ╔════════════╦════════╗ ║ categoryid ║ itemid ║ ╠════════════╬════════╣ ║ 2 ║ 2 ║ ╚════════════╩════════╝ </code></pre><p>kořenová příčina je ze stejného důvodu, proč se predikáty chovají v klauzuli vnější spojení <code>on</code> odlišně, než když jsou specifikovány v klauzuli <code>where</code>. syntaxe <code>merge</code> (a implementace spojení v závislosti na zadaných klauzulích) jen ztěžují vidět, že tomu tak je.</p><p> <a href="http://technet.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx">navádění v books online</a> (rozšířeno v položce <a href="http://technet.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx">optimizing performance</a> ) nabízí navádění, které zajistí, že správná sémantika je vyjádřena pomocí syntaxe <code>merge</code>, aniž by uživatel nutně musel muset pochopit všechny podrobnosti implementace nebo zohlednit způsoby, kterými by optimalizátor mohl legitimně přeskupit věci z důvodů efektivnosti provádění.</p><p>dokumentace nabízí tři možné způsoby implementace časného filtrování:</p><p><strong> zadání podmínky filtrování v klauzuli <code>when</code> </strong> zaručuje správné výsledky, ale může znamenat, že více řádků je čteno a zpracováno ze zdroje a cíle tabulky, než je nezbytně nutné (viz první příklad).</p><p><strong> aktualizace prostřednictvím zobrazení </strong>, která obsahuje podmínku filtrování, také zaručuje správné výsledky (protože změněné řádky musí být přístupné pro aktualizaci prostřednictvím zobrazení), ale vyžaduje to vyhrazené zobrazení, které splňuje liché podmínky pro aktualizaci zobrazení.</p><p><strong> použití společného výrazu tabulky </strong> přináší podobné riziko při přidávání predikátů k klauzuli <code>on</code>, ale z poněkud odlišných důvodů. v mnoha případech to bude bezpečné, ale vyžaduje to odbornou analýzu prováděcího plánu, aby se to potvrdilo (a rozsáhlé praktické testování). například:</p><pre><code>with target as ( select * from @categoryitem where categoryid = 2 ) merge into target using ( select categoryid, itemid from @datasource where categoryid = 2 ) as source on source.itemid = target.itemid and source.categoryid = target.categoryid when not matched by target then insert (categoryid, itemid) values (categoryid, itemid) when not matched by source then delete output $action, isnull(inserted.categoryid, deleted.categoryid) as categoryid, isnull(inserted.itemid, deleted.itemid) as itemid ; </code></pre><p>výsledkem jsou správné výsledky (neopakované) s optimálnějším plánem:</p><p><img src="https://i.stack.imgur.com/tazmk.jpg" alt="merge plan 2"></p><p>plán čte pouze řádky pro kategorii 2 z cílové tabulky. to může být důležitým hlediskem výkonu, pokud je cílová tabulka velká, ale je příliš snadné tuto chybu získat pomocí syntaxe <code>merge</code>.</p><p>někdy je snazší zapsat <code>merge</code> jako samostatné operace dml. tento přístup může dokonce <strong> vést lépe </strong> než jediný <code>merge</code>, což je skutečnost, která lidi často překvapuje.</p><pre><code>delete ci from @categoryitem as ci where ci.categoryid = 2 and not exists ( select 1 from @datasource as ds where ds.itemid = ci.itemid and ds.categoryid = ci.categoryid ); insert @categoryitem select ds.categoryid, ds.itemid from @datasource as ds where ds.categoryid = 2; </code></pre> <p>tabulky auditu se používají ke sledování transakcí s konkrétní tabulkou nebo tabulkami. umožňují vám vidět pokračující „deník“ (pro nedostatek lepšího slova). řekněme například, že máte takovou tabulku:</p><pre><code>create table sensitiveinformation ( sensitivenumber int not null, sensitivedata varchar(100) not null ) go </code></pre><p>mohou existovat uživatelé a/nebo aplikace, které mají přístup k vkládání, aktualizaci a mazání z této tabulky. ale vzhledem k citlivé povaze těchto dat možná budete chtít mít rychlý a snadný způsob, jak sledovat, kdo dělá to, co na této tabulce.</p><p>takže máte tabulku auditů, která sleduje, co se v této tabulce děje. obvykle to bude zahrnovat základní <strong>kdo</strong>, <strong>co</strong>, <strong>kdy</strong>.</p><p>tabulka auditů by mohla vypadat takto:</p><pre><code>create table sensitiveinformationaudit ( sensitivenumbernew int null, sensitivenumberold int null, sensitivedatanew varchar(100) null, sensitivedataold varchar(100) null, action varchar(50) not null, auditdate datetime not null, lastupdateduser varchar(100) not null ) go </code></pre><p>tabulky auditu se obvykle vyplňují pomocí databázových spouštěčů. jinými slovy, když se akce <code>x</code> stane na <code>sensitiveinformation</code>, vložte její podrobnosti do <code>sensitiveinformationaudit</code>.</p> <p>tradičně je "seřadit podle" označeno pomocí šipek nahoru/dolů, které představují třídění vzestupně nebo sestupně. názvy sloupců by měly mít vyšší úroveň kontrastu než hodnoty, například odvážnější text nebo stínované pozadí.</p><p>osobně při obarvení střídavých řádků obvykle přecházím na světlejší než na tmavší, protože efekt se zvyšuje tím výrazněji, čím déle vaši uživatelé interagují s tabulkou.</p><p>zde je několik skvělých příkladů možností rozvržení tabulky: <a href="http://patterntap.com/tap/collection/tables">http://patterntap.com/tap/collection/tables</a> </p> <p>tady je to, co jsem udělal předtím:</p><pre><code>(select 'tablea', * from tablea except select 'tablea', * from tableb) union all (select 'tableb', * from tableb except select 'tableb', * from tablea) </code></pre><p>funguje to dost dobře na stolech, které mají asi 1 000 000 řádků, ale nejsem si jistý, jak dobře by to fungovalo na extrémně velkých stolech.</p><p><strong>přidáno:</strong></p><p>spustil jsem dotaz proti svému systému, který porovnává dvě tabulky s 21 poli běžných typů ve dvou různých databázích připojených ke stejnému serveru se systémem sql server 2005. tabulka obsahuje asi 3 miliony řádků a liší se přibližně 25 000 řádků. primární klíč v tabulce je však zvláštní, protože se jedná o složený klíč z 10 polí (je to kontrolní tabulka).</p><p>plány provádění dotazů mají celkové náklady 184,255879 pro <code>union</code> a 184,228383 pro <code>union all</code>. cena stromu se liší pouze v posledním kroku před vrácením řádků, zřetězením.</p><p>skutečné provedení jednoho dotazu trvá asi 42 s plus asi 3 s, než se řádky skutečně přenesou. čas mezi dvěma dotazy je stejný.</p><p><strong>druhý dodatek:</strong></p><p>toto je ve skutečnosti <em> extrémně </em> rychlé, každý z nich běží proti 3 milionům řádků přibližně za 2,5 s:</p><pre><code>select checksum\_agg(binary\_checksum(*)) from tablea select checksum\_agg(binary\_checksum(*)) from tableb </code></pre><p>pokud se výsledky těchto neshodují, víte, že tabulky jsou odlišné. pokud se však výsledky <em> shodují </em>, nemáte <em> </em> zaručeno, že tabulky jsou totožné kvůli [velmi nepravděpodobné] možnosti kolize kontrolního součtu.</p><p>nejsem si jistý, jak změny datového typu mezi tabulkami ovlivní tento výpočet. spustil bych dotaz proti zobrazením <code>system</code> nebo <code>information\_schema</code> zobrazení.</p><p>zkoušel jsem dotaz proti jiné tabulce s 5 miliony řádků a ten běžel asi za 5 s, takže se zdá být převážně o (n).</p>