Hozzászólások
-
Szia!
Nem tudom, hogy kaptál-e már választ máshol esetleg.
Ezt a problémát VBA kóddal lehet megoldani.
Van egyApplication.ConvertFormulafüggvény a VBA-ban.Egy lehetséges megoldás:
Sub ConvertFormulasToAbsolute() Dim rngCell As Range For Each rngCell In Selection With rngCell If .HasFormula Then .Formula = Application.ConvertFormula(.Formula, xlA1, , xlAbsolute) End With Next rngCell End SubHasználat / Működés
————————-
Kijelölöd a tartományt, amiben minden hivatkozást abszolúttá szeretnél tenni, majd lefuttatod a fenti kódot.
Végigmegy a kijelölés összes celláján, és ha képlet van benne, akkor megcsinálja a hivatkozások módosítását dollárjelesre (abszolútra)Kipróbáltam, nekem működött.
Források
https://www.mrexcel.com/board/threads/how-change-multiple-cells-with-ranges-from-relative-to-absolute-references.793992/
https://www.mrexcel.com/board/threads/convert-a-range-of-cells-formulas-from-relative-to-absolute.27670/Imre
Nekem simán megcsinálja a # jelek visszacseréjét.
Próbáld meg egyesével a cserét (Nem az összes cseréje, hanem Csere egyesével). Lehet, hogy csak az egyik cella vagy oszlop miatt dob hibát.
Ha csak mindent kijelölsz, és = jel csere nélkül próbálod átvinni, akkor vissza fog hivatkozni, ahogy le is írtam az előző válaszban.
És olyankor a neveket is viszi, vinni akarja.Teljesen lemodelleztem a dolgot, tehát két munkafüzet között csináltam, nevekkel.
Ha előbb minden képletben lecseréltem az = jelet, majd a mindent kijelöllel átvittem, akkor:
– Nem vitte a nevet újra magával (nyilván, mert nem ismerte fel névként)
– Visszacserélés után működtek a képletek, a neveket használó képletek is az aktuális munkafüzetben (az ugyanolyan nevű lapokra hivatkozások persze)Amikor nem csak a tartalmat vittem át, hanem kitöröltem a régi munkalapot, és az újat munkalapként másoltam át, akkor a képletek jók lettek a visszacserélés után, de a nevet átvitte még egyszer, és az visszahivatkozott oda, ahonnan hoztam.
Imre
Szia!
A fájlt most nem tudom érdemben megnézni, de néhány dologhoz hozzászólnék:
1. A függvényes megoldáshoz használt dolgok jónak tűnnek, nem hinném, hogy lenne jobb megoldás, de nincs kizárva.
2. Hogy melyik szumma érték jelenjen meg a PIVOT-ban (vagy PIVOT chartban), azt szeletelővel nem lehet szabályozni. Check box-al és VBA-val lehetne.
3. Az ELTOLÁS (OFFSET) függvény az, amit keresel, hogy egy kezdő cellához képest változó méretű tartományt tudj megadni.
Nézz utána, eléggé egyértelmű
4. Sima grafikon (azaz nem PIVOT chart) esetén nincs beépített részletezési lehetőség
5. Az Online Excel jelenleg még nem támogatja (és valószínűleg nem is fogja) a Form vagy ActiveX controll-okat.
6. A Date-Picker tudomásom szerint csak 32 bites verziókban van meg. Ha neked 32 bites az Excel 2013-ad és mégsincs ott, akkor nézd meg ezt:
https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/#Register-date-pickerImre
Szia!
Hozzátennék egy gondolatot:
Ha jól gondolom, akkor nem elég az, hogy melyik sorban van olyan tétel, ami az aktuális oszlop tetején megadott határértékeken kívül van, hanem azt is látni akarod, hogy melyik az a tétel.
Erre a feltételes formázásnál nem jut jobb eszembe.De azt ellenőrizni, hogy van-e legalább egy, ami ne stimmel, lehet képlettel is. És akkor nem kell csak emiatt makrósnak lenni a munkafüzetnek.
Ez egy tömbképlet, ami a teljes aktuális sort hasonlítja az alsó határérétékek sorához , majd szorzással (logikai ÉS) ugyanazt a sort a felső határértékek sorához. Az eredmény egy logikai tömb, amiben ott lesz egyes, ahol a határokon belül van, és ott lesz nulla, ahol a határokon kívül van. Ezeket az egyeseket összeadva megkapjuk, hogy hány stimmelő oszlop van az aktuális sorban.
=SZORZATÖSSZEG((B3:E3>=B$1:E$1)*(B3:E3<=B$2:E$2))Ha ez megegyezik az oszlopok számával, akkor minden tétel OK, tehát a kimenet is OK, különben nem OK
Csatolok egy minta munkafüzetet.
Imre
Nagyon klassz megoldás!
Jó nagy hátraszaltót csináltál a dátum témában.Én nem is vettem észre, hogy a 127. sorig szóközök vannak a dátumban, ezért szöveg és utána valamiért rendes dátum lesz.
azt azért szögezzük le, hogy ez adathiba. Ha minden dátum rendes dátum lenne az első táblában, akkor erre nem lenne szükség.
Szval amit a „C” oszlopban csináltál, az baráti 🙂Aztán a B oszlopban átveszed a C oszlopban előállított dátumot, ha az L és a H oszlop is pozitív számot tartalmaz.
De az igazán brilliáns a D oszlopban képzett RANKING. Nem találkoztam még ezzel a megoldással, nagyon tetszik!
Megnézi, hogy az aktuális dátumnál hány nagyobb dátum van az egész oszlopban, és az eredmény+1 lesz a nagyságrendi sorrendje (rank-je).
Ha nulla nagyobb van, akkor az aktuális dátum a legnagyobb (0+1)
Ha egy nagyobb van, akkor az aktuális a második legnagyobb (1+1) és így továbbZseniális! Feltéve, hogy egyik dátum sem fordul elő többször.
Ahol meg nulla jött ki a B oszlopban, ott a Rank a legnagyobb lesz (mindenhol ugyanaz, 111), de az nem érdekel minket, mert csak 15-ig kell nézni.
És akkor az alsó, kis táblában már csak meg kell keresni az 1-15 sorokat a felsőből. Ez a tisztázat.
Azért is klassz, mert régebbi (2013) Excelen is működik.
Bocsánat az „okoskodásért, de lehet, hogy valakinek jól jön a magyarázat.
Köszi @fjns a munkádat!
Kiegészítés
Az Excelben van egy SORSZÁM nevű függvény, ami a rangsorolást megcsinálja ugyanúgy, mint a D oszlopban lévő képlet.
Ha a D4-be ezt írod: =SORSZÁM(B4;B:B), akkor ugyanaz jön ki.Ez a függvény már csak kompatibilitási okok miatt van az Exelben, hogy 2007-es és régebbi Excelben is működjön.
2010-ben behozták a RANG.ÁTL és a RANG.EGY függvényeket, amik kicsit árnyaltabb megoldásokat tudnak.Imre
Szia!
Első blikkre szerintem neked az INDIREKT függvényre van szükséged.
Ezzel a függvénnyel lehet egy hivatkozást szövegesen előállítani.
Ha jól értem, akkor olyasmit szeretnél, hogy az első sorban a hivatkozásban lévő munkalap neve ‘0401’ legyen, de ahogy húzod lefelé, ez magától változzon ‘0402’ re és így tovább.
1. Elsőre javasolnám, hogy ne két munkafüzetben legyenek a dolgok.2. A napokat ’01’-től írd be ’31’-ig, így használhatók lesznek a képletekben az adott hónap adott napjának előállításához.
Az első cellába szimpla aposztróffal beírod, hogy ’01, majd lehúzod 31-ig. Így szövegesen képzi a napok sorszámait.3. A mukalapneveket a napok számozása mellé egy külön oszlopba képezd le. Így egyszerűsödik a dolog.
Pl. a 01 szám mellé jöjjön a 0401, a 2-es mellé a 0402, és így tovább. Ezeket lehet majd befűzni a hivatkozásba.
Itt persze felmerülhet, hogy nem mindig áprilisról van szó, ezért az aktuális hónap számát kitehetnéd egy cellába. (Pl. A2)
Tegyük fel, hogy a 01-es sorszám a B4-ben van. A C4-be mehet az, hogy: =$A$2&B4, majd lehúzva, kijönnek a napokhoz tartozó munkalapnevek.4. A következő két oszlopba (D és E) menő FKERES keresési tábláját dinamikusan, INDIREKT függvénnyel rakhatod össze. D4-ban van ez:
=FKERES($A$1;INDIRECT(C4&"!$A:$C");2;FALSE)
A1-ben van a keresett neved (Sándor), C4-ban van 04015. Lehúzod az FKERES képleteket
Imre
Szia!
A feladat többféleképpen is megoldható, én most egy HA + ÉS fv megoldást mutatnék.
=HA(ÉS(C1>=0;C1<5%);D1*(1+7%);HA(ÉS(C1>=5%;C1<10%);D1*(1+12%);D1*(1+15%)))Ez egy háromszoros elágazás. Azt nem írtad, hogy az felsorolt eseteken kívüli esetbe mi történjen, ezért én önhatalmúlag 15%-os növekedést írtam elő.
Balról jobbra haladva:
Ha C1 0 és 5% közé esik (azaz nagyobb vagy egyenlő nulla ÉS kisebb mint 5%), akkor az eredmény legyen a D1 szorozva 1+7%, azaz D1*1,07.
Különben HA 5 és 10% közé esik (a vizsgálat ugyanúgy megy ÉS függvénnyel), akkor D1*1,12
Ha egyik sem, akkor D1*1,15Imre
Szia!
Alakul a dolog 🙂
Szép megoldások. Majd ha váltasz (mert előbb-utóbb fogsz) újabb Excelre, meglátod, hogy a tömbképletek mennyivel egyszerűbbek lettek.
Gyertya diagramot (sem) lehet csinálni sajnos Pivot tábla alapján. Van még pár ilyen.
Erre azt szoktam csinálni, hogy dinamikus névtartományt húzok a Pivotra, a kezdő celláját (Nálad ez G1) fixnek tételezve.Nálad most az „L” oszlop végét kellene megtalálni, és máris megvan a G1:L_oszlop_vége tartomány.
Ha az L oszlopban nem pontos egyezőséggel keresünk egy jó nagy számot, ami biztosan nagyobb az összes előforduló számnál, akkor a szokásos függvények az utolsót fogják megtalálni, így tesz a HOL.VAN is, visszaadva a 39.sort
HOL.VAN(9999999;L:L;1)Ha ezt odaadod az INDEX-nek, az megtalálja a 39. sorban lévő értéket.
INDEX($L:$L;HOL.VAN(9999999;$L:$L;1))És most jön a lényeg, hogy az INDEX fv valójában mindig a megtalált elemre mutató referenciát ad vissza, nem magát az értéket, tehát az L oszlop utolsó sorában lévő érték címét, azaz jelen esetben „L39”-et
Tehát a teljes PIVOT tartománya jelenleg: „G1:L39”, ami természetesen követi a Pivot méretét, ha ebben az oszloptartományban marad különben.Utolsó lépésként csináltam egy ennek megfelelő Nevet a névkezelőben, (Pivot_adat) ami így néz ki:
=Munka1!$G$1:INDEX(Munka1!$L:$L;HOL.VAN(9999999;Munka1!$L:$L;1))Végül a Chart létrehozása, ami még egy csavar, mert előre ki kellene jelölni az adatokat, hogy Diagramot csinálj, a Pivot alapján meg nem lehet. Szóval én értékként kimásoltam a Pivot táblát, megcsináltam a diagramot, majd módosítottam az adatforrását, hogy a fent létrehozott névre mutasson.
=Munka1!Pivot_adatA „Munka1” munkalapnév fontos benne, mert a Chart adatforrásként csak akkor fogad el nevet, ha a nevet tartalmazó munkalap is bele van írva. Nem tudom miért, de így van.
Nagyon érdekes, mert valahogy érzékeli, hogy Pivot-ból van, és a szeletelő mindkettőt vezérli.
Utólag pedig valami miatt átvette az előző Chart forrását és nem lehet módosítani a forrását.
Kicsit még nézegetem…Visszacsatolom, és köszönöm ezt a jó kis tanulságos kihívást!
Imre
Szia!
Nem egy egyszerű feladat ez.
Gondolom ez csak minta adat, és automatizálni szeretnéd az átalakítást.
Ebből a kiinduásból még makróval sem olyan egyszerű megcsinálni.Ha jól gondolom, akkor ez valamilyen nyilvántaró rendszer kimenete, ami már valamiféle összesítés, és ebből kellene egy másikfajta összesítést csinálni.
Külön kihívás az egy cellában két név téma.PowerQuery-vel sikerült csinálnom valami hasonlót, de el van forgatva 90 fokkal.
Így néz ki:Név Tanítási nap Lilli 2022. 01. 24. Lilli 2022. 01. 24. Lilli 2022. 01. 17. Lilli 2022. 01. 17. Domi 2022. 01. 24. Domi 2022. 01. 24. Domi 2022. 01. 19. Domi 2022. 01. 19. Ádám 2022. 01. 25. Ádám 2022. 01. 25. Ádám 2022. 01. 25. Ádám 2022. 01. 27. Emese 2022. 01. 25. Emese 2022. 01. 27. Lara 2022. 01. 17. Lara 2022. 01. 17. Csabi 2022. 01. 17. Csabi 2022. 01. 17. Csabi 2022. 01. 19. Csabi 2022. 01. 19. Ambrus 2022. 01. 19. Ambrus 2022. 01. 19.Ez maradhat így, vagy lehet még belőle eyg kicsit áttekinthetőbb PIVOT táblát csinálni, ahol a nevek nem ismétlődnek, csak az első előfordulásnál jelenek meg.
Ha ez is megfelel, akkor van megoldás, de elmagyarázni nem biztos, hogy tudom…Imre
Ha jól értem, akkor 10-et kell hozzáadni, ha az utolsó előtti karaktere 9-es VAGY az utolsó két karaktere 99.
Ezt így (is) le lehet írni:HA(VAGY(KÖZÉP(E1;9;1)=""9;JOBB(E1;2)="99");E1+10)Ezt kellene befűznöd a már működő fenti kifejezésbe.
Imre
Sziasztok!
Péter! Több önbizalmat! 🙂
A kérdezőnek mondanám, hogy eddig bármit kérdeztél, egyszer sem köszöntél és egyszer sem reagáltál a válaszokra. Egy fórumnál az első mondjuk személyiség kérdése, de a második annyira alap, hogy egy ingyenes fórum közösségi dolog, egymástól tanulunk és visszajelzünk.
Gondolom több helyre is feltetted a kérdéseidet, és az is lehet, hogy közben megoldódtak máshogy, de ha nem sértődsz meg ezeken a szavakon annyira, hogy soha nem térsz vissza, akkor kérlek, adj visszajelzést!
Péter, A képlet teljesen jó szerintem, magam sem tudnék jobbat.
Amivel kiegészíteném:
– HAELSŐIGAZ fv angol neve: IFS. A többszörösen egymásba ágyazott HA függvényeknek egy új alternatívája,
a 2019-es Excelben van először benne.
– Ha ezek eredetileg számok, akkor szerintem működik ÉRTÉK és SZÖVEG függvény nélkül is. (Kipróbáltam)=HAELSŐIGAZ(JOBB(B2;1)="9";B2+1;KÖZÉP(B2;9;1)="9";B2+10;IGAZ;B2+11)Klasszikus HA függvénnyel pedig így néz ki:
=HA(JOBB(E2;1)="9";E2+1;HA(KÖZÉP(E2;9;1)="9";E2+10;E2+11))Ez amiatt van, mert az Excel automatikusan konvertál, azaz ha számhoz hozzányúlsz szöveg függvénnyel, akkor előbb szöveggé alakítja, majd elvégzi a műveletet, és ha az eredmény szám, akkor azt megint számmá alakítva írja a cellába eredményként.
Nem annyira korrekt, de így működik.
Imre
Szia!
A probléma az, hogy a 3-ast tartalmazó cellák végén a 3-as után van egy speciális, 10-es kódú karakter.
Ez egy vezérlő karakter, ami sortörést idéz elő a cellában.
Ha a szerkesztőlécen a hármas mögé kattintasz, és nyomsz egy lefelé nyilat, akkor egy üres sort fogsz látni a 3-as alatt.
Néha előfordulnak ilyen, és ehhez hasonló érdekességek, főleg netről lehozott C/P adatokban.Hogy miért gondoltam erre? mert már „öreg” vagyok 🙂
Én különben nem is így csináltam, hanem a CODE/KÓD függvénnyel megnéztem, hogy mi az utolsó karakter kódja. Az jött ki, hogy 10. Bocs, most épp angolon áll az Excelem…
=CODE(RIGHT(H3))A 3-as kódja viszont az 51 kellene, hogy legyen
=CODE("3")Na, miután megvan a diagnózis, jöhet a gyógyítás.
Képlettel
Van egy CLEAN / TISZTÍT nevű függvény, ami kitakarítja a 32-esnél kisebb kódú vezérlőkaraktereket a megadott cellából. A kapott eredmény még szöveges lesz, de a VALUE / ÉRTÉK függvénnyel számmá lehet alakítani.=VALUE(CLEAN(G2))Ezt aztán lehúzva meg jobbra megkapod az eredeti tartomány javított változatát, amit visszarakhatsz a fenti tartományba értékként.
Csere és szorzás
1. Kijelölöd az átalakítandó oszlopokat
2. Ctrl-H Csere)
3. Amit keres: Jobb oldali ALT+010 a billentyűzeten. Ez beviszi a 10-es kódú karaktert
4. Cserélje a semmire (ezzel mindenhonnan kimegy a 10-es kódú cucc.
5. Meg kell mindent szorozni 1-el, hogy a szöveges számok igazi számok legyenek
5.1 Egy üres cellába beírsz egy 1-est, majd ezen a cellán Ctrl+C
5.2 Kijelölöd az átalakítandó oszlopokat -> Bármelyiken jobb klikk -> Speciális beillesztés -> SzorzásVOILA !
Jelezz vissza itt, hogy sikerült-e!
Boldog karácsonyt!
Imre
Szia!
Én igazából kérdeznék.
Mi okoz neked nehézséget a megoldásban?A függvény nevét ismered, gondolom egy új oszlopban alkalmazni is tudod, majd visszateszed értékként
Azt gyanítom, hogy nem csak ennyit szeretnél.
Ha ez a feladat túl gyakran merül fel, akkor Delila megoldása tökéletes, ha maga az adatfájl tartalmazhat makrókat, és meg is tudod oldani a használatot.A feladat makró nélkül automatizálható PowerQuery-vel is, az megint egy másik dolog
Szóval mi is a kérdésed pontosabban?
Imre
Eseménykezelő
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Munka1" And Target.Address = "$B$3" Then Call valami End If End SubModulban, hogy mit kel csinálni
Sub valami() Sheets("Munka2").Range("A2").Value = "hello" End SubHát egyedül nem könnyű.
Ajánlom figyelmedbehttps://videotanfolyam.pentaschool.hu/courses/excel-automatizalas-makrok-alapjai
Imre
