Excel - makro pro navýšení souřadnice sloupců?

[sleapy]

Dobrý den,
potřeboval bych poradit s jedním makrem v Excelu. Jedná se o to, že mám vytvořený dokument, kam každý měsíc dávám data z měření - na základě nich se mi generují grafy a porovnání s předchozím měsícem a rokem. Problémem je, že mám dva zadávací listy, kde vyplňuji konkrétní hodnoty a pak cca 10 dalších listů, kde se mi hodnoty porovnávají. S historického hlediska potřebuji zachovávat i starší data, proto musím každý měsíc vkládat nové hodnoty a tím i manuálně přepisovat všechny funkce, respektive se jedná pouze o přepis/navýšení souřadnice sloupce u každého vzorce zvlášť - takže například vzorec =C6/'zadavaci-list1C '!BQ73-1 upravuji na =C6/'zadavaci-list1C '!BR73-1.

Stejný problém je i u grafů, kde pro porovnání s minulým měsícem či rokem z aktuálního měsíce musím také všechny grafy "přelinkovat" o sloupec dále s tím ale, že musím o jeden sloupec posunout i začátek oblasti výběru dat grafuy.

Doufám, že jsem to napsal dostatečně srozumitelně.

Moc děkuji za jakoukoliv pomoc či radu.

[Reklama]

[navstevnik]

K prvni casti dotazu: zde muzes pouzit funkci NRPRIMY.ODKAZ (vice napoveda Excelu), priklad:
=C6/NEPŘÍMÝ.ODKAZ("'zadavaci-list1C '!" & A1 & "73-1") kde v A1 je odkaz na sloupec BQ, BR, .... nebo ve vzorci odkazovat na ofsetovane dynamicke pojmenovane oblasti (nazvy), pr: oblast ma nazev NovaData a ve vzorci bude: =C6/NovaData-1
Ke druhe casti: zde bude vhodne pouzit pro oblast dat grafu dynamicke pojmenovane oblasti ofsetovane na zaklade hodnoty v urcene bunce. Jak nato koukni se na viewtopic.php?f=35&t=60386 a prilozeny sesit GrafVysecivtDynamicky.xlsx
Pozn. Zakladem dynamicke pojmenovane oblasti je pouzit fce Posun(), kde parametry Radky, Sloupce odkazuji na bunky s hodnotou ofsetu.

[sleapy]

Děkuji za rychlou odpověď. Co se týče první části, tak z nějakého důvodu mi po aplikování vzorce skončí vždy výsledná hodnota #REF!, pokud si vzorec krokuji, dostávám tvar "'zadavaci-list1C '!BQ73-1", ale hodnota z buňky se mi nezobrazí.

Graf s dynamickými oblastmi jsem asi špatně pochopil a díky tomu si nejsem jistý, zda to bude efektivní řešení - na jednom listě mám cca 9 grafům a dle vlákna i nápovědy bych musel pro každý graf vytvářet listbox a pomocí něj pak například vybírat měsíce. Jeden univerzální listbox udělat pravděpodobně nepůjde, jelikož grafy mají odlišné vstupní buňky/řádky.

Asi jsem to ale jen špatně pochopil.

[navstevnik]

ad 1. =C6/NEPŘÍMÝ.ODKAZ("'zadavaci-list1C '!" & A1 & "73")-1

ad2. Listbox v ukazce (uvedeno pouze jako ukazka mozneho reseni) slouzi pouze jako zdroj offsetu, hodnotu offsetu do odkazovane bunky lze generovat jakkoliv.

Dotaz je prilis obecny na konktretni navrh reseni. Nejaka demo ukazka odpovidajici vsak realite by byla zadouci.

[sleapy]

Aha, ještě jsem zkoušel se s tím poprat, ale nějak jsem se v tom zamotal. Časem jsem navíc zjistil, že toto řešení je pak trochu nepřehledné - u každého listu jsem musel nastavovat jiné buňky ze zadávacího listu a když jsem se k vyhodnocování vracel později, ztratil jsem přehled, v kterém období se nacházím u daných listů. Napadlo mne, zda by se nějak nedalo realizovat například na úvodním dashboardu výběr pomocí jednoho listboxu konkrétní rok a pomocí druhého konkrétní měsíc. Po jejich výběru by se aktualizovaly všechny listy (tabulky dat s trendy, grafy), kromě posledních dvou zadávacích. Zkoušel jsem toto řešit pomocí nápovědu v excelu, ale nakonec bez úspěchu. Navíc se mi zřejmě podařilo nějak zacyklit excel a po tvrdém vypnutí jsem o část neuloženého dokumentu přišel. V příloze proto přikládám poslední uloženou - jde jen o ukázkovou část souboru s náhodnými daty, soubor jsem kvůli velikosti musel zabalit.

Doufám, že to bude pochopitelné.

[navstevnik]

Podivam se, co lze udelat, ted jenom jedno. Jakekoliv experimentovani, overovani novych reseni se zasadne delaji na kopii nebo napred vytvori zaloha. Budiz ti nynejsi pripad poucenim.

[sleapy]

Já vím, bohužel kopie uložená na flashce je nenávratně pryč - poškozená FAT tabulka vlivem tvrdého restartu PC, ale možná z toho data ještě nějak zachráním.

Každopádně moc děkuji za pomoc.

[navstevnik]

V priloze je demo sesit (popis na listu) ukazujici postup, jak generovat dynamicke tabulky a grafy.

[sleapy]

No, nemám slov - moc děkuji, mne jako laikovi by to určitě trvalo roky.

Rád bych se jen zeptal ještě na jednu věc - zajímalo by mne, kam se do událostní procedury ukládá informace o tom, z kterého listu (Data celkem) se mají brát hodnoty. Zkoušel jsem tuto proceduru rozšířit i pro list dat z PPC a další co mám v původním souboru, ale bez úspěch. Chtěl jsem jen, aby se při výběru konkrétního data braly i hodnoty z ostatních zadávacích listů.

Ještě jednou (a opět) moc děkuji.

[navstevnik]

Udalostni procedura reaguje pouze na udalosti listu, ve kterem je vlozena, tedy zmena v bunkach I45 a K45.
V danem pripade demo souboru udalostni procedura listu Dashboard pouze vykonava prepocet roku a mesice na sloupec pripadne offset sloupcu vuci vychozi bunce ve sloupci B3 respektive sloupci B:B, coz muze byt a nasledne je vztazeno k listu Data celkem. Teprve ve vzorci je vysledek spojen s konkretnim listem: =NEPŘÍMÝ.ODKAZ("'Data celkem'!" & $L$45 & "4") nebo v definici pojmenovane oblasti: =POSUN('Data celkem'!$B$3;0;Dashboard!$O$45;1;12)
Pro jine listy s jinou strukturou dat bude potreba odkazy na sloupce nebo ofsety, pokud jsou odvisle od zadaneho "soucasneho obdobi" na listu Dashboard, dopocitat v udalostni procedure listu Dashboard (rozsirit proceduru a vysledek ulozit bud na list Dashboard nebo na prislusny list), ale vztazeno k vhodne vychozi bunce prislusneho listu, ze ktereho maji byt ziskana ofsetovana data a pak na tyto hodnoty odkazovat.
Je celkem jasne, ze dodatecna implementace dynamickeho odkazovani bude vzhledem k rozsahu tabulek a grafu pracna, ale porad "lepsi pozdeji nez pozde".

[sleapy]

Aha, děkuji za objasnění. Poslední věc, co mi vrtá hlavou jsou grafy - z nějakého důvodu nejsem schopný (ani ve vašem řešení tomu tak není) je mít v horizontu roku, například od ledna do ledna. Vždy jsou počáteční hodnoty o měsíc posunuté dopředu.

[navstevnik]

Prilozeny demo soubor ukazuje postupy, jak vytvorit dynamicke tabulky a grafy. Podle konkretnich pozadavku je pak zapotrebi vykonat upravy.

Pocet mesicu (kategorii) zobrazenych v grafu je dan v definici dynamicke pojmenovane oblasti - posledni parametr ve funkci Posun.
V demu jsem zvolil 12 mesicu, napr. u GrafOsaX: =POSUN('Data celkem'!$B$3;0;Dashboard!$O$45;1;12),
zmen na tebou pozadovanych 13 a take u dalsich oblasti pro hodnoty osy Y.
Dale je nutno upravit vypocet offsetu. Pro 12 mesicu v procedure

Kód: Vybrat vše

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$I$45" And Target.Address <> "$K$45" Then
    Exit Sub
  Else
    Dim SoucMM As String, PredchMM As String, LonskyMM As String
    With Me
      If .Range("i45").Value = vbNullString Then
        .Range("i45").Value = 2008  ' osetreni prazdne bunky!
      End If
      SoucMM = .Range("b3").Offset(0, ((.Range("i45").Value - 2007) * 12) + (Range("k45").Value) - 1).Address
      PredchMM = .Range("b3").Offset(0, ((.Range("i45").Value - 2007) * 12) + (Range("k45").Value) - 2).Address
      LonskyMM = .Range("b3").Offset(0, ((.Range("i45").Value - 2007) * 12) + (Range("k45").Value) - 13).Address
      .Range("l45").Value = Mid(SoucMM, 2, InStr(2, SoucMM, "$", vbTextCompare) - 2) ' sloupec pro soucasny stav
      .Range("m45").Value = Mid(PredchMM, 2, InStr(2, PredchMM, "$", vbTextCompare) - 2) ' sloupec pro predchozi mesic
      .Range("n45").Value = Mid(LonskyMM, 2, InStr(2, LonskyMM, "$", vbTextCompare) - 2) ' sloupec pro soucasny mesic - 12
      .Range("o45").Value = ((.Range("i45").Value - 2007) * 12) + (Range("k45").Value) - 12 ' offset pro grafy 12 mesicu
    End With
  End If
End Sub

plati vztah: .Range("o45").Value = ((.Range("i45").Value - 2007) * 12) + (Range("k45").Value) - 12 ;
uprav na ....+ (Range("k45").Value) - 13 a zmen i text v bunce Dashboard!O44: Souc mes - 13

Pozn.: protoze grafy jsou vztahovany na mesice predchazejici "soucasnemu mesici", lze uvedene dynamicke grafy generovat az po uplynuti jednoho roku od pocatku shromazdovani udaju - omezeni pro vyber roku na 2008-20xx nebo v dynamickych oblastech prubezne upravovat sirku a pro grafy offset az do doby dosazeni pozadovaneho maximalniho ofsetu

Doplneno:
Nektere grafy s pribyvajicim poctem rad ztraceji vypovidaci schopnost v dusledku preplnenosti. Muzes zkusit aplikovat dynamicke grafy.
Postup v demo priloze.