MS Excel

Zdravím všechny, prosím o pomoc se sestavením vzorce, který mi vrátí odkaz na umístění buňky. Níže se pokusím popsat blíže.

V buňce A1 bude hodnota(text, číslo), který chci vyhledat v oblasti C1:E357 a odkaz zapsat do buňky A2. Řekněme, že hledaná hodnota je v buňce D27. Výsledkem zapsaným do A2 tedy bude D27.

Takto získanou hodnotu (odkaz) bych využil jako "startovací" pro další funkce.

Výsledkem celého snažení má být dynamicky definovaná oblast pro oblast součtu.

Děkuji.

Zaslat odpověď >

Strana: « předchozí 1 2

bedha(17.1.2018 16:31)#039111

prozatím nemám 100% otestováno, ale zdá se, že by mohl fungovat vzorec nalezený na jiném webu:
=ODKAZ(SOUČIN.SKALÁRNÍ((B1:Z2000=A1)*ŘÁDEK(B1:Z2000));
SOUČIN.SKALÁRNÍ((B1:Z2000=A1)*SLOUPEC(B1:Z2000)))
sice bude zřejmě vždy nutné zvolit vhodný rozsah, ale zatím je to nejblíže tomu, co hledám.citovat

elninoslov(17.1.2018 20:40)#039120

Najskôr ste zmienil oblasť dát

...vyhledat v oblasti C1:E357...

potom

...to co chci hledat se může nacházet kdekoliv (v daném sešitě)...

následne

...SOUČIN.SKALÁRNÍ((B1:Z2000=A1)*ŘÁDEK(B1:Z2000))...

Či už {matica} alebo SUMPRODUCT/SOUČIN.SKALÁRNÍ (to je tiež matica), musí mať obmedzený rozsah. Nemôže to pri každom prepočítaní listu hľadať niekoľkokrát v 1048576x16384
=17179869184 buniek, to je výpočtovo neúnosné.
Hľadať v celom zošite pomocou vzorca nieje možné. Navyše vôbec nie univerzálne, pretože ak by aj šiel použiť 3D odkaz na viac listov, tak to určite nepôjde na všetky listy, lebo vzorec nedokáže zistiť mená prvého a posledného listu v hocijakom súbore a následne ich použiť v 3D odkaze.

...odkaz zapsat do buňky A2...

...abych dokázal určit umístění (souřadnice)...

...odkaz(souřadnice) hledané buňky...

- odkaz odkazuje na bunku/oblasť a má typ Range. Ak nazveme odkaz ODKAZ, tak ho môžeme použiť napr. =SUM(ODKAZ)
- umiestnenie (povedzme, že myslíte adresu), ktorá má typ String, teda napr. ak toto umiestnenie nazveme rovnako ODKAZ, použijeme ju =SUM(INDIRECT(ODKAZ))
- súradnice určujú pozíciu X a pozíciu Y, teda sú typu 2x Long. Ak si ich nazveme SURADNICA_X a SURADNICA_Y tak ich môžeme použiť napr. =INDEX(oblasť; SURADNICA_Y; SURADNICA_X)

... i bez přiloženého excelu (který by pouze směřoval ke konkretizaci výsledného vzorce)...

Záleží na tom, čo chcete ďalej s tým robiť, a nie že nie. To je dôležité pre tvorbu riešenia.

Předtím jsem neuvedl, nevím jak moc je to podstatné, že se hodnota bude v oblasti dat vyskytovat právě jednou.

Veľmi podstatné. Matica s funkciou SMALL nájde prvý (alebo X-tý) výskyt hľadanej hodnoty, čo je potrebné v prípade viacnásobného výskytu, kdežto SUMPRODUCT/SOUČIN.SKALÁRNÍ "nájde" všetky a urobí súčet súradníc, teda výsledok je blbosť.
Aspoň toto je uvedené na správnu mieru.

Ak chcete riešenie, ktoré nájde hocičo, kdekoľvek v akomkoľvek zošite s koľkokoľvek riadkami a stĺpcami a v hocijakom počte a názvoch listov, tak jedine makrom, napr. tak, že si umiestnite makro do "Osobného zošitu makier" PERSONAL.XLSB, alebo urobíme doplnok s danou funkcionalitou a ten si doinštalujete do Excelu. Každopádne taká funkcia je neprenosná na iný PC obyčajným prenesení vzorca, ale je potrebné aj makro (PERSONAL.XLSB alebo doplnok).

"elninoslov" napsal/a:

Vy viete čo chcete, ale neviete to dať na papier.
My vieme čo ste dal na papier, ale nevieme čo chcete.

Práve som snáď vymyslel príslovie alebo čo

citovat

elninoslov(18.1.2018 0:50)#039124

Napr takouto UDF, ale treba ju ešte doošetriť napr. na to, aby preskočila bunku,ktorá je zároveň parameter.
Function HLADAJ_BUNKU(Co As Variant, Optional Typ As Byte = 1) As Variant
Dim Bunka As Range, WS As Worksheet, CX As Long, CY As Long

On Error GoTo KONIEC:
With ActiveWorkbook
With Worksheets(1)
CY = .Rows.Count: CX = .Columns.Count
End With

For Each WS In .Worksheets 'Prehľadá všetky listy
With WS
Set Bunka = .Cells.Find(What:=Co, After:=.Cells(CY, CX), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With
If Not Bunka Is Nothing Then Exit For
Next WS
End With

If Not Bunka Is Nothing Then
Select Case Typ
Case 1: Set HLADAJ_BUNKU = Bunka 'Odkaz na bunku
Case 2: HLADAJ_BUNKU = Bunka.Address 'Adresa bunky
Case 3: HLADAJ_BUNKU = Bunka.Parent.Name 'List bunky
Case 4: HLADAJ_BUNKU = Bunka.Worksheet.Parent.Name 'Zošit bunky
Case 5: HLADAJ_BUNKU = Bunka.Address(External:=True) 'Celá cesta
Case 6: HLADAJ_BUNKU = Bunka.Row 'Súradnica Y - riadok
Case 7: HLADAJ_BUNKU = Bunka.Column 'Súradnica X - stĺpec
End Select
Else
HLADAJ_BUNKU = CVErr(xlErrNA) 'nenájdené
End If

KONIEC:
If Err.Number <> 0 Then HLADAJ_BUNKU = CVErr(xlErrValue) 'iná chyba
Set WS = Nothing: Set Bunka = Nothing
End Functioncitovat

bedha(18.1.2018 9:18)#039125

Tím příslovím jste to vystihl zřejmě naprosto přesně a patří Vám obrovský respekt a obdiv za trpělivost. Poprosím Vás, aby jste to se mnou ještě chvilku vydržel. Ještě se musím omluvit, že neovládám přesnou terminologii excelu, díky které taky mohlo dojít k některým nedorozuměním a také za svou zbrklost, díky které jsem uvedl naprostý nesmysl a to, že se hodnota může nacházet v celém sešitě, myslel jsem list. Omlouvám se, kaji se, sypu si popel na hlavu a pochopím, když nade mnou zlomíte hůl.
Kvůli zmíněnému problému s přenositelností makra a tomu, že je to pro mě prozatím neprobádaná oblast bych se jim rád vyhnul, i tak děkuji za postup jak by to mohlo fungovat.
Omezení souřadnic chápu, bylo by tedy možné velikost prohledávaného pole určit tak, že by další funkce prohledala list a zjistila poslední obsazený řádek a sloupec a tím byla velikost pole definována (adresa buňky, do které se dostaneme stisknutím ctrl+end)?
Pokud by definice velikosti pole takto fungovala, dá se z pole nějaká buňka vyjmout? Aby to neprohledávalo buňku, do které píšu zadání?citovat

Strana: « předchozí 1 2

Téma: odkaz na vyhledanou buňku

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje