Remélem nem mondod komolyan, hogy ha képlet volt, akkor maradjon képlet.
Az érték beillesztésnek a lényege, hogy a képleteket értékké alakítja.
Persze másik lényege, hogy megtartja a cél formátumot.

Amúgy nálam lefut az a kód, nem tudom, h nálad miért nem, de szerintem felesleges erre makrót használni

Én például azt csináltam, hogy az érték beillesztés gombot kitettem az eszköztárra magamnak, így egy kattintás az értékbeillesztés.

1. Fájl -> Beállítások -> Gyorselérési eszköztár
2.Középső oszlopban a gyakori parancsok helyett a menüszalagon nem szereplő parancsok
3. Megkeresed az Értékek beillesztése sort és nyomsz rajta egy dupla klikket.
4. Megjelenik a gomb az eszköztáron felül az utolsó gomb után
5. Ennyi

Ha mindenképpen billentyűkombinációt szeretnél hozzá, akkor az eszköztáron ezt a gombot tedd az első nyolcba.
Például ha ez a hetedik, akkor Így ALT-7-el működni fog. Ez már majdnem a Ctrl-É 🙂

Imre

• A hozzászólás módosításra került: 2 years telt el-horvimi.

Látom, értem, köszönöm.
Speciális használati eset.
Azért készíts gyakran biztonsági mentéseket.

Ha lenne még ilyen, akkor arra is figyelj, hogy ha egy munkalapot másolsz másik munkafüzetbe, akkor azok a képletek, amik másik MUNKALAPRA hivatkoznak, vissza fognak hivatkozni az eredeti munkafüzetbe.
Emiatt indításkor frissítési kérdés jelenhet meg. Ellenőrizd az Adatok -> csatolások szerkesztése menüt, hogy aktív-e és mik vannak benne.

Imre

Én azt csinálnám, hogy a képletet is makróval írnám, ugyanazzal, ami az F1-F2-F3-ban változtat, majd a makró értékké alakítaná az eredményeket. Így a képletet csak a makró ismeri.
Ez elég kommon megoldás.

Imre

Próbáld ezt:

=SZŰRŐ(Táblázat1;BAL(Táblázat1[foksz];2)="12");"nincs")

Imre

Nagyon örülök! 🙂

Teljesen hibás, ahogy próbálod.
A logikai függvények összetett használata mondjuk nem egyszerű.

De látok még egy problémát, miszerint te csak azt az esetet fogalmaztad meg, amikor a bal oldali figura nyert, viszont a szettlistában a jobb oldali is nyert 3 szettet, így a meccset is.

Szóval a logika egy kicsit bonyolultabb szerintem.
Szabad szöveggel:

(H6=11 és I6 <10) vagy (I6=11 és H6 <10)
vagy
(H6>10 és ABS(H6-I6)=2) vagy (I6>10 és ABS(I6-H6)=2)

Utóbbi esetleg egyszerűsíthető:
(H6>10 vagy I6>10) és ABS(H6-I6)=2

Először vegyük csak az első szettet, amit a H3-ba írt képlettel ellenőrzünk:
=HA(VAGY(VAGY(ÉS(H6=11;I6<10);ÉS(I6=11;H6<10));ÉS(VAGY(H6>10;I6>10);ABS(H6-I6)=2));0;1)

Most épp magyaron áll az Excelem, nézd el nekem 🙂 Az angolban neked angol függvényneveket és a pontosvessző helyet vesszőt kell használnod.

Szóval a VAGY (OR) függvényt ugyanúgy kel használni, mint az AND-et. Akárhány paramétere lehet, bármelyik IGAZ, IGAZ-at fog adni.
Ha jól megnézed, akkor azt látod, hogy a két AND-et betettem egy OR-ba, aztán van ott még egy OR és azt nézem az IF-el.
A szabad szöveges egyszerűsített verzióját írtam le függvényekkel.
Jól megdolgoztattál így az éjszaka 🙂

Ha sikerül ezt nálad beüzemelni, akkor én a helyedben azt csinálnám, hogy ugyanezt a képletet egy kis módosítással beírnám a
J3, L3, N3, P3 cellákba.

Így, ha bármelyik nem OK, akkor ott van valami gond.

Ha mégis 1 cellában szeretnéd látni az 5 szett ellenőrzését, akkor egy meglehetősen bonyolult kifejezést kell készítened.
VAGY
Változtatsz a logikai vizsgálat kimenetén és ha egy szett rendben, akkor a kimenet legyen nulla, különben pedig 1-es.
Én most ezt a képletet küldöm.
Ezután írd le az 5 ellenőrzést összeadva. Ha mind jó, akkor nulla lesz a vége, különben pedig a hibás szettek számát kapod.

A szettenkénti verziót megoldással visszatöltöm.

Imre

• A hozzászólás módosításra került: 2 years telt el-horvimi.

Ez is gyönyörű megoldás!
Köszönjük!

Csaba, arra kérlek, hogy ha nem annyira triviális függvényt vagy képletet használsz, akkor fűzz hozzá némi magyarázatot!
A célom az egész fórummal, hogy ne „csak” megoldásokat adjunk, amit a kérdező felhasznál vagy implementál, de igazából nem tudja, hogy mi történik.
A SZÖVEG függvényre gondolok itt most konkrétan. Ez nem annyira ismert. A PIVOT nem szorul magyarázatra, az több oldal lenne, ha nincs meg valakinek.

Nem veszem el előled a lehetőséget 🙂

Köszi,

Imre

Én a képernyőképen az E3-ban látom a dátumot.

Akkor örülünk! 🙂

Nem kell korábbi bejegyzést keresned, most egy új kérdést tettél fel. Én csak arra utaltam,, hogy ha akkor makróztál, akkor biztosan most is makrózni akarsz, hiszen az oszlopok átmásolása egy több lépéses folyamat.
Ennek vázlatos megoldását adtam meg.

Imre

Én azért vetettem fel ezt a megoldást, mert sokkal gyorsabb, mint ha soronként iterálsz végig 44 ezerszer.
Tehát Excel szinten oldom meg a problémát, de makróval írom meg a képletet.
A FORMULAR1C1 egy Excel képletet ír a kijelölésbe.
He azt érted, akkor nem kérdezhetsz olyat, hogy hivatkozhat-e saját magára (pl. RC), mert Excelben ilyen nincs (körkörös hivatkozás)
Én megadtam neked azt a képletet, ami pont azt csinálja, amit most újra megkérdeztél:
„Nincs arra valami megoldás, hogy a p oszlopban, ha talál kötőjelet azt cserélje le az o első 3 karakterére?”
=HA(BAL(P2;1)="-";HELYETTE(P2;"-";BAL(O2;3)))
Ha az a probléma, hogy makró közben új segédoszlopok keletkeznek, akkor másképp kel megoldanod.
Én azt javasoltam, hogy az oszlopokra bontás után:
1. Az első oszlop gondolom változatlan marad
2. A második-harmadik-negyedik lehet potenciálisan olyan, hogy kötőjellel kezdődik, ezért erre a 3 oszlopra csinálsz 3 ilyen IF-es számítást 3 újabb oszlopba. (javaslom értékké alakítani
3. Az első, változatlan és az utolsó 3 oszlopot átmásolod az első lapra, a helyére
4. Kitakarítod az átmeneti oszlopokat.

A jelenlegi tudásoddal ezt meg tudod csinálni szerintem.
Ha ez nem felel meg, vagy meg akarsz küzdeni egy programozás szempontjából nagyobb kihívással, akkor memóriatömböket kell használnod.
1. Eszerint az eredeti M oszlopot beolvasod egy tömbbe, majd egy ciklusban szétdobod 4 külön tömbbe
2. a 4 külön tömböt feldolgozod egyesével és átalakítod vagy sem a tételeit az ismert szabályok szerint
3. A 4 tömböt visszaírod az első lapra, a helyére.

Ha nem foglalkoztál még tömbökkel, szerintem ez nagy kihívás elsőre.

A FORMULA2R1C1 akkor van, amikor a rögzítővel nem egyszerű képletet, hanem tömbképletet veszel fel.
Ez jelen esetben most nem releváns.

Ha érdekel, nézz utána a tömböknek, tömbfüggvényeknek, tömbképleteknek az Excelben.

Nem ugyanaz, mint a VBA memóriatömbök, de hasonló.

Ha megoldást akarsz, és az a prioritás, akkor azzal főzz, amid már megvan.
Ha tanulni akarsz, akkor menj a nehezebb irányba.

Imre

Én már nem követem a folyamatot, elvagytok nélkülem is 🙂
De tennék bele inputot.
Ha jól értem, az egyes részek az M oszlopban alulvonással vannak most elválasztva.
Ha jól értem, bármelyik részben lehet kötőjel.
Elég lényeges kérdés, hogy több részben is lehet kötőjel, és akkor többször kell a helyettesítést megcsinálni?
Remélem az első tagban nem lehet 🙂

Ha ez igaz, akkor alaposan el kell gondolkodni a dolgon, hogy el lehet-e kerülni a soronkénti feldolgozást, ami a kezdeti sebesség problémát okozta, azaz valami oszlop formula. Vagy az egészet memóriatöbmbbe kell tenni, ott feldolgozni, és visszaírni.

Másik lehetőség, hogy előbb oszlopokra bontod, és a kapott 4(?) oszlop mellé ugyanannyi kalkulált oszlopot csinálsz, ami megvizsgálja, hogy tartalmaz-e kötőjelet, és ha igen, megcsinálja a cserét. Ebből talán 3 is elég lehet, ha kijelenthető, hogy az első oszlopban nem lehet kötőjel.

A kapott oszlopokat végül átmásolod értékként az előző lapra, a helyére, utána takarítasz.
A „TextToColumns” helyett ajánlom figyelmedbe az O365 „SZÖVEGFELOSZTÁS” (TEXTSPLIT) függvényét, ami az egész M oszlopot az alulvonás mentén oszlopokra bont. Például.:
=SZÖVEGFELOSZTÁS(M2;"_")
Ezt beírva (makróval) az M oszlop után valahová, mondjuk O2-be, kapsz egy oszlopokra bontott tömböt az M2 celláról
Ha az egész oszlopba akarod betenni (44e sor?), akkor előbb ki kell jelölni, és utána betenni a formulát. Javaslom felvenni, mert a tömbképletek formula makrója kicsit eltér a hagyományos formuláktól. Nekem ez lett:
Selection.Formula2R1C1 = "=TEXTSPLIT(RC[-1],""_"")"
A Formula2 a lényeg.
Különben pedig, ha a TextToColumns már működik, akkor ezzel nem biztos, hogy kell szenvedni.

Végül ezen tömb után lehet megcsinálni a kötőjelek cseréjét formulával oszloponként.
Nekem 3 oszlopot csinált a szétbontás a te mintád alapján, ezért az első szabad oszlopom az R lett.
R2-be írtam a képletet, ami a feltételes cserét csinálja.
=HA(BAL(P2;1)="-";HELYETTE(P2;"-";BAL(O2;3)))
Megnézi, hogy a szétbontott oszlopok közül a másodikban van-e kötőjel, és ha igen, akkor a kötőjelet kicseréli az előző oszlop első 3 karakterére.
Mivel relatív címeket használ, ezt elég jobbra lemásolni annyiszor, ahányszor ezt akarod, és előállnak a megjavított oszlopok.

Imre

Delila megoldása újabb gyönyörű példája a brute force cellánkénti loopolás és az oszlopkba egy lépésben illesztett képletek sebesség különbségére.
Minden munkalap interakció (olvasás, de főleg az írás) lassú. Ez 44e soron már elég jól látszik.

És ezen még tovább lehetne finomítani a 365 új függvényeivel, de ha így jó neked Norbi, akkor íme egy lehetséges megoldás.
Én az „M” oszlopra írtam volna egy univerzális formulát, aminek az erdménye vessszővel tagolt szövegrészek, amit szétdobtam volna például TEXTSPLIT függvénnyel, ami 365-ös.
Még ciklus sem kell hozzá.

Imre

Szia!

Pár kérdés:
– Milyen Excel verziót használsz?
– Az egyes részek mindig ugyanannyi karakter hosszúak-e?
– Jól értem, hogy az egész eredetileg 1 cellában van, és ezt te 3 cellába szeretnéd?
– Tudnál feltölteni mintát, ami több adatot tartalmaz, de valós, vagy ahhoz közeli?

Sokszor járunk úgy, hogy egyszerűsített adatot és kérdést kapunk, dolgozunk vele, majd kiderül, hogy nem is az a feladat valójában, ami a kérdésben szerepelt.

Feltételezve, hogy 1 cellában van és 3 cellát szeretnél, de nem fix a részek hossza, de a szabály az, amit írtál én ezt csinálnám:
1. Vessző mentén szétbontanám 3 oszlopra (Pl. szövegből oszlopok / Text2Columns paranccsal), esetleg a 356-ös Excel erre való függvényével úgy, hogy mindegyik szöveges maradjon
2. Kivenném a kötőjelet a középső elejéről (HELYETTE / SUBSTITUTE függvény)
3. A középső elejéhez fűzném az első elejét (BAL/LEFT és összefűzés & jellel)

A válaszok és minta után konkrét megoldás is születhet.

Imre

Én ezt a módszert hasznátam már 2010-nél biztosan…
Nem értem Delila, hogy nálad mi lehet.

Új eposzi jelzőt kaptam. Már megérte 🙂

Félreértetted a CELLA függvény első argumentumát.
Oda nem a fájl útvonala kell, vagy a neve, hanem az a konkrét szöveg, hogy:
Magyar esetben „filenév” így dupla idézőjelek között
Angol esetben „filename”

Imre

Szia!

én így szoktam:

=MID(CELL("filename";$A$1);SEARCH("]"; CELL("filename";$A$1))+1;40)

A CELL (CELLA) függvény egy ritkán használt függvény. Segítségével különböző információk kérhetők le az aktuális munkafüzetről vagy munkalapról vagy valamelyik celláról.

Pl. a CELL(„filename”; cellacím) az aktuális munkafüzet útvonalát és az aktuális munkalap nevét adja vissza ebben a formátumban:
"C:\Users\DELL\Desktop\[pppp.xlsx]valami"

Ebben megkeresem az első záró kapcsos zárójel pozícióját, és onnantól kezdve kiveszek 40 karaktert. Mivel a lapnév az Excelben max 35 karakter, ezért max annyit vesz ki.

Ha ezt a képletet beírod egy létező, elmentett munkafüzet egyik lapjára, akkor visszaadja a lapnevet.
Ha ezt a lapot átmásolod egy új munkafüzetbe, akkor hibát fog adni, mert az új munkafüzet még nincs mentve.
Ha mented, akkor meggyógyul, de még kell neki valami miatt egy frissítés.
Én kettőt szoktam kattintani a képletet tartalmazó cellán, de nem nyúlok hozzá, csak Enter-rel lezárom.
Kész.

Imre

Szia!

Megnéztem, azt hiszem rájöttem mi volt a te problémád.
A fő ok az, hogy még az elején tartasz a PowerQuery felfedezésének.
Előbb utóbb jönnek olyan feladatok, amiket nem lehet kattintgatással megoldani.
Ez is ilyen, mert az egyes darabok ilyen bénán vannak megtervezve.

A fő gond az volt, hogy a mintafájl feldolgozásánál a három oszlop létrehozása után át is kell nevezni őket.
De mivel az előző lépés a fejléc előreléptetése, ezért az utolsó 3 fejlécbe bekerül a mintafájl 3 konkrét értéke (Konkrét dátum, megrendelés száma és megrendelő neve).
Az átnevezés során ezeket keresi, de nem fogja megtalálni, mert minden darabban ezek változhatnak.

Emiatt oszlopszám alapján kellett az átnevezést csinálni. Pl. a 13. oszlop neve legyen „valami”.
Ez csak M kóddal lehetséges most.

Megcsináltam így, és az összefűzés szépen elkészült.
A mintafájl esetén a „Mit szabnak vele” oszlopból vettem ki a null értékeket, hogy eltűnjön minden olyan sor, ami nem kell.
ha ez nem jó, mert lehet olyan darab, ahol ez nincs kitöltve, akkor majd változtasd meg.

Visszatöltöm a megoldást.

A mappa útvonalat a lekérdezések közül a „Mintafájl” nevű lekérdezés Forrás lépésében és a külső (utolsó) lekérdezés forrás lépésében tudod elvégezni.

Imre

Valahogy eltüntetted az összefűző lépést a külső lekérdezésből.
A fejlécben az egyik tétel neve mellett kell lennie egy ide-oda mutató nyilacska ikonnak.
Elvileg azzal fűzi őket össze.
Így utólag még nem próbáltam, de talán jó lehet.

Szia!

Ez csak makróval oldható meg.
Itt van pl. egy megoldás, amit kis módosítással tudnál használni:
https://stackoverflow.com/questions/17241302/independently-sort-multiple-columns-in-excel

A kód alatti egyik komment leírja a módosítást is, ha esetleg bármelyik oszlopban lenne üres cella is.

Ezen kívül vannak még megoldások is a neten, ha ezzel nem boldogulnál…

Imre

Az 5. és a 6. oszlop is alkalmas a dologra.

Ha nem mindenhol van Zöld szám oszlop, de az oszlopokat különben mindegyikben ugyanúgy hívják, és mindegyikben a Megrendelés munkalaphoz kell csatlakozni, akkor ez nem baj, használd a szűrésre az 5. oszlopot. Ha nem lenne Zöld szám oszlop, akkor a 6. oszlop megy előre 5. oszlopnak, ami szintén alkalmas null-ok kiszűrésére.

Viszont a Dátum, Megrendelésszám, Megrendelő cellák kirakása oszlopokba konkrétan a 10, 11, 12 oszlopokat használja a második sorból. Ha nincs Zöld szám oszlop, akkor összesen csak 11 oszlop lesz, nem 12, és ez a három oszlop előbbre tolódik.
Jól látom ezt?
Ha igen, akkor ez elég nagy probléma

Az a fontos, hogy küszködj! 🙂
Nem kell táblázatot csinálni és nem kell átírni a cellát kötőjel nélkülire.
Nem a táblázattá alakítás miatt lett Column10 a K oszlop.
Valószínűleg az történik, hogy a mintafájl átalakítás nálad tartalmaz egy automatikus lépést, amivel az első sort fejlécbe lépteti.
Mivel abban a sorban csak a K1 van kitöltve, ezért azt a tartalmat lépteti a fejlécbe.
Töröld azt a lépést, ami a fejléc előléptetést csinálja! Az elején van.
Így minden marad a helyén, és felül Column1…Column10 fejlécnevek lesznek.

Imre

Nos, akkor van egy megoldás, amit értesz és meg is tudnád csinálni, és van egy még jobb, amit meg nem.

Egyik lehetséges út, hogy sikerül kiguglizni, és sikerül is implementálni.
Másik lehetséges út, hogy „valaki” megcsinálja neked 🙂
Az biztosan nem fér bele, hogy meg is tanítsa.

A PowerPivot-DAX világában idáig eljutni, hogy az alapokat értsd és tudd alkalmazni, majd ezt a problémát is meg tudd oldani, vagy legalább értsd, egy hosszabb tanulási utat igényel.

Mi lesz a te utad?

Ha az egyben átmásolás azt jelentette, hogy az egész munkalapot, nem pedig a tartományt, akkor simán lehet, hogy a munkalap objektum tartalmazza a linket valahol. Roppant kíváncsi lennék, bizonyára tanulságos.

de ha neked elég, hogy megoldódott, akkor boldogság van 🙂

Imre

Annak örülök, hogy ha próbáltad.
A kulcslépést akkor valószínűleg nem találtad meg, de ez nem is elvárható, ha az elején jársz még a PQ-nek.
Szóval van lehetőség abszolút módon meghivatkozni egy „cellát” a táblában a sor és oszlopszámával. A megfogott értékből lehet csinálni egy új oszlopot.
Ezt kell csinálnod a Dátum, megrendelés szám, megrendelő cellákkal egyesével a J2, K2, L2 cellákból.
A hivatkozás úgy néz ki, hogy Táblanév{sor száma}[oszlop neve]]
A sorokat a PQ nullától sorszámozza, tehát az első sor a nulladik sor.
Például a dátum a második sor Column10 oszlopában van:
#"előző lépés neve"{1}[Column10]

A mintafájlban a lépések
– Utolsó két sor törlése
– A dátumot a leírás szerint egy új oszlopba teszed
Új, egyéni oszlop, aminek a neve „Dátum” és a képlete: = #”előző lépés neve”{1}[Column10]
– Ugyanezt megcsinálni a megrendelés száma és a megrendelő cellákkal (Column 11 és Column 12)
– Most már törölhető az első két sor
– Fejléc előreléptetés
– Az új 3 oszlop átnevezése

Ezután a külső, eredmény lekérdezésben ott lesz az összefűzött változat.
A mintát, ahol egy táblázatra építve megcsináltam ezt, csatolom neked.

Imre

Ja, ez egy makrós fájl (xlsm)?
Én még megpróbálnám, hogy csinálnék egy új munkalapot, majd az összes többit kitörölném, és menetenék.
Ezután is megnézném, hogy mit mutat.
Így próbálnám kideríteni, hogy a link forrása egy munkalapon van-e vagy valahol a makró oldalon?

Imre

Szia!

Hát enyhén szólva nem egyértelmű a feladat, de homályosan sejthető. Amit sejtek:

Részletfizetésről van szó?
Van egy számla egy eredeti összegről, és azt törleszti, így minden befizetéskor csökken a tartozása?
Emiatt a számlaszám minden befizetéskor ugyanaz?

Ha ajó a modell, akkor ez megcsinálható viszonylag egyszerűen.
Pl. Az eredeti, teljes összeg pozitív, a befizetés negatív értékekkel, akkor egy sima összesítés számlaszámonként

Bárhoyg is, az eredeti összegnek meg kell lennie egy számlához, és a hozzá tartozó befizetéseket is azonosítani kell, és kapcsolnio az eredeti számlához.
A befizető is a számlához tartozik egyértelműen, de a számla azonosító fontosabb.

Kb ilyesmire gondolok, de minta nélkül nem tudok tovább menni.

Imre

Szia!

A sebesség leginkább a lekérdezéshez használt adatkészlet méreténél szoott gondot okozni frissítéskor.
A lekérdezés megnyitása és létrehozása során nem annyira jellemző.

Okozhat problémát az is, hogy az adatforrás, amihez csatlakozik, az a hálózaton van, és nincs megfelelő sávszélesség.

Milyen forráshoz csatlakozol?

Imre

Hagyományos excel eszközökkel nem. Olyan formula nem létezik, ami saját maga értékét megváltoztatja.
Eseménykezelő makróval megcsinálható.

Imre

Klasszik.
A windows magyar, az excel angol vagy angol nyelvi interface van beállítva.
Ha ezen a gépen dolgozol különben, akkor ez így van már egy ideje…

Örülünk, hogy megoldódott.

Hát akkor hajrá, a videotanfolyam oldalon tudod megrendelni a hozzáférést.
Utalásos számlát küldünk, és ha megjött a pénz, aktiváljuk a hozzáférést.

De lényeges, hogy ez egy alap kurzus.

Imre

Hát, ingyenes magyar nyelvű makró kurzus nem nagyon van.
Angol nyelven pedig elég sok, csak rá kell keresni.
A Youtube-on is vannak 10 órás bevezető VBA kurzusok.

Én kb. 30 éve programozok különböző nyelveken, ezért a VBA-t már könnyű volt megtanulnom x-dik nyelvként magamtól is.

Ugyanis véleményem szerint nem a programnyelv a lényeges, hanem a gondolatmenet, az algoritmus, amit te kitalálsz, és leprogramozol.
Ehhez sok kisebb-nagyobb projektet kell végigcsinálni és tapasztalatot szerezni.
Meg persze kell hozzá egy attitűd, ami nem annyira tanulható…
Bármilyen tanfolyam ehhez egy jó kezdő lökést adhat, a többi rajtad múlik…

Én is készítettem egy bevezető makró kurzust, amit videotanfolyam formájában is el lehet érni.
https://videotanfolyam.pentaschool.hu/

Elég népszerű, de addig nem megy el, ami a te problémádhoz kell.

A te problémádat programozási szempontból közepes nehézségűnek ítélem meg. Idáig eljutni, hogy önállóan megcsináld, nem kevés idő, legalább is szerintem.

Imre

Értem.

Ha nem manuálisan szeretnéd csinálni, 🙂 ,akkor ezzel van feladat.
Valószínűleg nem csak így lehet megoldani, de én ezt csinálnám:
Makróval kipótolnám a hiányzó sorokat a 6500 termékben úgy, hogy legalább a sor neve meglegyen mindenhol
Utána Excel 365-ben még egy kis függvényezés, de az már nem sok.

Szerintem az első lépés lényeges, bármivel is próbálja bárki megcsinálni.

Részemről ez túlmutat egy ingyenes fórumtémán.

Ha egyszeri feladatról vagy szó, akkor munkaként el tudom vállalni.
Megkapom az eredeti listát és visszadom a táblázatot.

Ha tudsz rá büdzsét szerezni, akkor örülünk…

Kb. 1 napos munkának saccolom.

Ha érdekes, írj email-t (horvath.imre@pentaschool.hu)

Szia!

Makrókkal lehet eseményeket kezelni, és leprogramozni, hogy mi történjen pl. egy cella módosulása esetén.
Olyan esemény viszont nincs, hogy feltételes formázás érvényesülése.
Olyan van, hogy cella értékének változása. Meg lehet nézni, hogy melyik cella változott, mi az új értéke, és a változás után más celláknak vagy tartományoknak mi lett az értéke vagy szummája.
Nagyon vigyázni kell ezzel, mert ha egy cella változik, az kiváltja az eseményt, és ha az eseménykezelővel módosítasz más cellákat, azok ismét kiváltják a change eseményt, stb…

De nekem nagyon gyanús, hogy ha egy kicsit jobban leírod a feladatot, akkor kiderül, hogy nem csak így lehet megoldani a kérdést.
Talán a feltételes formázás feltételét vizsgálva egy HA függvénnyel is meg lehetne csinálni az összegzést. Kérdés, hogy melyik cellában/cellákban kellene megjelennie és melyik 5 cella összege?

Amit kihámoztam:
A oszlop
—————–
Ha az aktuális sor értékeinek összege nagyobb, mint a készlet, akkor legyen piros

Sorok
——————-
Ha az értékek gördített összege átlépi a készletet, legyen piros.

Tehát pirossá válást nem lehet vizsgálni, a change esemény vizsgálata lehet, hogy elég bonyolult lenne, és kérdéses, hogy makrókhoz értesz-e?
Ha nem, akkor marad a feltételes formulás megoldás.

Imre

Ha adott számú kis táblád van, akkor ezt úgy lehet, hogy:
Ha az elsőben megtalálta, akkor vegye onnan, különben nézze a következőt, ha ott sincs, akkor nézze a következőt, stb…
Hogy megtalálta-e vagy sem, azt a HIBÁS vagy a HAHIBA üggvénnyel lehet megnézni.

Megnéztem a fájlt, javasolnám módosítani.
Az élelmiszer nevek táblázatok, a balra mellé írt kód nem része a táblázatnak. Ez biztosan nem lesz így jó.
Első körben javasolnám a kódokat a nevek mellé jobbra tenni (ha mindegy) úgy, hogy a táblázat része legyen.
Így lehet FKERES-t használni.

Másodszorra elnevezném a kisebb táblázatokat rendesen.

Remélem, hogy egy élelmiszer név csak az egyik táblázatban van benne.

Visszatöltöttem

Imre

Szia!

Egy kicsit nézegettem, és pár perc után csak annyit tudok mondani, hogy ez beszarás!!!
Én nem jöttem rá, hogy miért csinálja ezt, és miért csak a 3-nál, és miért…..?

Kipróbáltam, hogy a 3-at képlettel állítottam elő (1+2 vagy 2,4+0,6) és akkor nem csinálta
Ez olyan, mintha valami Excel BUG lenne.

Mindenesetre megoldás orientáltan megnéztem a maradékszámítást egy alternatív képlettel, ami az eredeti értékből kivonja annak az egész részét =$C$12+(C12-INT(C12))

Ez helyesen működik a 3,00 esetén is

Én ennyit tudok hirtelen hozzátenni.

Imre

Szia!

Ez egy egyszerű FKERES (VLOOKUP) függvény szituációnak tűnik. (Lehet XKERES is, ha olyan Excel verziód van, ami ezt ismeri)
Ha nem ismernéd őket, akkor nézz utána..

Keres egy értéket egy megadott tartomány első oszlopában és ha megtalálja, akkor visszaad ugyanabból a sorból egy másik oszlopban lévő értéket.

Imre

Szia!

Azt hiszem értem, hogy mit szeretnél.
Két egymás után következő idő között a legnagyobbat ugye?
Azt látom, hogy a lista idő szempontjából növekvő.

Ha táblázattá van alakítva a tartomány, akkor alakítsd vissza sima tartománnyá!
Feltételezem, hogy a van fejléc, és utána jönnek az adatok.

A triviális megoldás egy segédoszlop használata, ahol az aktuális időből kivonod az előzőt, majd ezen az oszlopon keresed a legnagyobbat (MAX)
A képen látható értékek között a legnagyobb különbség a 12:55 és a 17:51 között van, 4:56 perc.
Ez szépen ki is jön a fenti módszerrel.
Ha rendes órában akarod látni, akkor szorozd fel 24-el, és formázd számmá: 4,933 óra

Segédoszlop nélkül egy kicsit trükkösebb tömbképlet kell, de megoldható úgy is.

Haladjunk sorjában. Ha ez a megoldás elég, akkor kész vagyunk.

Imre

Szia!

A DARABTELI nem veszi figyelembe a manuális szűrést. Úgy is fogalmazhatnék, hogy saját maga csinálja meg a szűrést, és utána összesít.
Te itt valójában két feltétel szerint szeretnél megszámoltatni.
Egyik, hogy a „C” oszlop = „Alma” és a másik, hogy a „D” oszlop = 2.
Erre vannak külön, több feltétel szerint összesítő függvények. Azok is ugyanúgy, előbb szűrnek, utána összesítenek.

Ezek közül neked most a DARADHATÖBB kell.

Imre

• A hozzászólás módosításra került: 2 years, 5 months telt el-horvimi.

Sziasztok!

Megnéztem, amit feltöltöttél. Mondjuk egyáltalán nem korrelál azzal, ami a kérdésedben van, de azért talán érthető lesz a segítség.
Én úgy értem, hogy ha bármi van az aktuális sorban a cellában, akkor színezze meg az A oszlopban a cellát az aktuális sorban.

Szóval ahogy nézem a B:H oszlopok tartalmazhatnak számot és szöveget is.
Ha ez igaz, akkor Nem lesz jó a SZUM függvény az ellenőrzéshez, hanem a DARAB2 kell.

1. A1:A10 kijelölése
2. Feltételes formázás menü -> Új szabály…
3. Formázandó cellák kijelölése képlettel
4. A képlet: =DARAB2($B1:$H1)>0
5. Formátum… -> megadod, hogy milyen legyen
6. Kipróbálás

Imre

Örülünk 🙂

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 egy Application.ConvertFormula fü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 Sub

Haszná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

• A hozzászólás módosításra került: 2 years, 6 months telt el-horvimi.

Szia!

Olyasmire gondolsz ugye, mint a Ctrl-F kereső ablaka, ami mögött simán lehet bármit csinálni, majd visszatérni a kereséshez.

Első felindulásomba azt akartam írni, hogy ilyesmit nem lehet egy programon belül csinálni. Arra gondoltam, hogy a kereső ciklus a hiba esetén elmenthetné az aktuális futási környezetet, és a következő indításnál lehetne opció, hogy onnan folytassa.

Aztán mégis utánakerestem a dolognak, és a sok NEM LEHET között találtam két linket, ahol úgy néz ki, van megoldás.
Nem próbáltam, de nézd meg!

Ez az első
https://www.mrexcel.com/board/threads/pause-macro-then-resume-macro.673602/
Itt Gary’s Student nevű user megad egy linket

Második
https://answers.microsoft.com/en-us/msoffice/forum/all/vba-script-to-pause-macro-allow-editing-then/53027408-ed41-4300-b42e-ed2fc20b8e9c
Itt meg Andreas Killer kommentjei lehetnek hasznosak.

A kulcs a DoEvents parancs, ami egy gyorsan pörgő ciklus közben megcsináltatja az Excellel a közben történt eseményeket.
Szóval kiteszi a form-ot nem modális módban, tovább megy, de belefut egy ciklusba, ami addig pörög, amíg a for látszódik.
És közben megcsinálja azt, amit a user tesz.
Én legalább is ezt képzelem.

Jelezz vissza, hogy mi lett!

Imre

Szia!

Kicsit eljátszottam vele, le is egyszerűsítettem.
Kacifántos amit akarsz, de megcsinálható.
Visszatöltöm.

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

• A hozzászólás módosításra került: 2 years, 6 months telt el-horvimi.

Szia!

Lényeges kérdés még, hogy ezt a több szintű választó menüt egy táblában soronként akarod, (pl. egymás utáni oszlopokba), vagy egy táblához csak egy 3 szintű szűrés tartozik, pl. a tábla felett 3 cellában?

Előbbi esetben nem annyira látom a megoldást SZŰRŐ függvénnyel VBA nélkül. DE más se:
https://answers.microsoft.com/en-us/msoffice/forum/all/using-unique-filter-formula-inside-the-data/53a292ad-5d7a-468d-8764-e10442d8421e?page=1

Utóbbi esetben a SZŰRŐ függvénnyel van azonnali megoldás. Annyi szűrő függvény kell, ahány szintje van a menünek (jelenleg 3)
1. szint: Az érvényesítés forrása: A felelősök egyedi listája (EGYEDI függvény). Ha kiválasztotta a felelőst, akkor egy SZŰRŐ függvénnyel kiveszed egy tartományba azokat a városokat, ahol a kiválasztott a felelős
2. szint: Az érvényesítés forrása: Az előző lépésben kivett városok. Ezután még egy szűrő függvény, ami egy másik tartományba kiveszi az első kettő ÉS kapcsolatából az intézmények listája
3. szint: Az előző lépésben kivett intézmények listája (tömbje)

Mivel a szűrt listák mérete változhat, ezért dinamikusan kel hivatkozni a tömbökre a legördülő menü forrásában (#)
Tervezek erről írni egy cikket, de addig is itt egy példa:
https://www.excel-university.com/dependent-drop-downs-with-filter/

Imre

A kettes pontot PIVOT-tal és VBA-val úgy csinálnám, hogy:
– Kitenném checkboxoxra, hogy milyen mezőket szeretne megjeleníteni
– Ha bejelölte őket, akkor megnyom egy gombot, és egy VBA kód törli a PIVOT-ből az éppen ott lévő mezőket, és betenné a megjelölteket.

Imre

Ha külön-külön jó, akkor együtt is jónak kell lennie.
A mintádat átalakítottam, visszacsatoltam. Szerintem jó.

A feltételes formázás fronton elég gáz a helyzet.
Ha bármelyik színes cellára kattintasz és megnézed a szabályokat (szabályok kezelése), akkor látod, hogy rengeteg szabály van, pedig csak kettőnek kellene lennie.

Amíg működik és nem lassú, nem érdekes, csak olyan izé…

Imre

Az első sorhoz tartozó képletet úgy kell megírni, hogy az adatsor hivatkozása nincs rögzíthető, viszont az alsó és felső határokat tartalmazó tartomanyi hivatkozások van a sor le van rögzítve, de az oszlop nincsen. Akár mindkettőt is le lehet rögzíteni, azzá dollarjeletni.
Ha a képletet lehuzod és nem jó, akkor valószínűleg ez a hiba.
Próbáld.ki, hogy lehuzas után kettőt kattintasz az egyik eredményen. Így megmutatja színekkel, hogy honnan veszi az adatokat

Imre