-
GAMEPOD.hu
A Microsoft Excel topic célja segítséget kérni és nyújtani Excellel kapcsolatos problémákra.
Kérdés felvetése előtt olvasd el, ha még nem tetted.
Új hozzászólás Aktív témák
-
dudikpal
aktív tag
válasz dudikpal #19026 üzenetére
De dinnye vagyok, nem is az a gondom, azt már megoldottam
A keresés lefut A1:A324.ig, eredmények megvannak.
A valódi gondom az, hogy az A oszlop itt még nem ér véget, csak innentől új kategóriába mennek az adatok, a másik kategória keresési tartománya A324:A428. Ezt az értéket nem veszi be a Hol.van, hanem az előző kategória eredményeit adja ki újra.Az első kateg képlete: ÖSSZEFŰZ($H$1;HOL.VAN($H$2;INDIREKT(V6):INDIREKT(W2);0)+HELYETTE(V6;"A";"")+1)
a másodiké:
ÖSSZEFŰZ($H$1;HOL.VAN($H$2;INDIREKT(W6):INDIREKT(X2);0)+HELYETTE(W6;"A";"")+1)tehát csak jobbra másoltam, mégis az első kateg eredményei jönnek ki megint.
Sztem az indirektet nem jól használom itt, olyan mintha figyelembe se venné, vagy nem tudom...Ötlet vki?
-
Mutt
aktív tag
válasz dudikpal #19027 üzenetére
Hello,
A megadott képletekben a INDIREKT(cella1):INDIREKT(cella2) részt rövidítheted így:
INDIREKT(cella1&":"&cella2)Azonban ez még nem oldja meg a problémádat, oszd meg a fájlt hogy tudjunk egy jobb megoldást adni a problémára.
üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Mutt
aktív tag
válasz dudikpal #19052 üzenetére
Hello,
A lenti példára ezt a képletet tudom javasolni:
=HAHIBA(INDEX($A$1:$A$14;ÖSSZESÍT(15;6;SOR($B$1:$B$14)/(($B$1:$B$14=E$1)*($C$1:$C$14=E$2));SOROK($E$3:$E3)));"")Az alábbi elven megy:
A képlet magja a ($B$1:$B$14=E$1)*($C$1:$C$14=E$2) rész, amely a B és C-oszlopokban lévő értékeket hasonlítja össze E1 és E2-ben lévő fix értékkel, és IGAZ vagy HAMIS értékeket add vissza ott ahol vagy van vagy nincs találat. Ha logikai változókat szorzunk össze, akkor pedig 1 és 0 lesz az érték Excelben. "a" és "b" értékre keresve tehát ezt kapjuk vissza: {1;1;1;1;1;1;0;0;0;0;0;0;0;0}
Ezek alapján tudjuk hogy mely esetekben vannak találatok, most már csak a helyük kérdéses. Ehhez jön a képbe a SOR($B$1:$B$14) rész, ez ugye ilyet ad vissza alapból: {1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Ha ezt elosztjuk azzal az eredménnyel, amellyet korábban megkaptunk - amely megmondja hogy hol van a feltételeknek megfelelő egyezés - akkor kapunk egy olyan halmazt amelyben számok (1-el való osztás eredményeként) és hibák (0-val való osztás) lesznek. A SOR($B$1:$B$14)/(($B$1:$B$14=E$1)*($C$1:$C$14=E$2)) eredménye ez lesz: {1;2;3;4;5;6;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!;#ZÉRÓOSZTÓ!}Most már tudjuk, hogy melyik sorban vannak a nekünk szükséges értékek már csak sorban ki kell venni őket. Erre a KICSI(halmaz;hanyadik elem) függvény az ideális, de az csak akkor műkődik ha a halmaz nem tartalmaz hibát, nekünk pedig van egy csomó #ZÉRŐOSZTÓ! hibánk benne így ez nem járható (CSE-vel megoldható lenne). Az ÖSSZESÍT egy olyan függvény, amely képes "meghívni" egyéb függvényeket és kezeli a hibákat is. Az ÖSSZESÍT(15;6;halmaz;hanyadik elem) ugyanaz mint a KICSI(halmaz; hanyadik elem) és közben kezeli a hibákat (az első paraméter a 15-ös jelenti a KICSI-t, a második pedig hibakezelést).
Már csak azt kell megadnunk, hogy sorban az első, második, harmadik stb. elemet adja vissza a képlet.
Ilyet sokféleképpen lehet előállítani, én gyakran ezt használom: SOROK(induló cella fixen;futó cella).
Példánkban: SOROK($E$3:$E3), ez 1-et fog visszaadni, de ha lemásoljuk akkor már SOROK($E$3:$E4) lesz belőle és 2-es lesz az értéke.Ezek alapján az ÖSSZESÍT képlet teljesen így néz ki: ÖSSZESÍT(15;6;SOR($B$1:$B$14)/(($B$1:$B$14=E$1)*($C$1:$C$14=E$2));SOROK($E$3:$E3))) és az eredménye 1-es lesz mivel pont az első találat kell nekünk, amely az első sorban van.
Meg van a sorszám már csak ki kell iratni, ehhez az INDEX(tömb;sorszám) képletet lehet használni.
A végén egy HAHIBA függvénybe zárva az egészet eltüntethetűk az esetleges hibák.üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Mutt
aktív tag
válasz dudikpal #19057 üzenetére
Hello,
Én néztem be, mivel jól írtad le a kérést.
Az eredeti probléma megoldásához az INDEX függvény kell, amelynek a képlete INDEX(tartomány;kívánt sor száma).
Röviden ezek a képletek működnek 2007 vagy újabb Excelben:
A1-be:
=HAHIBA(INDEX(VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G);DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)+2);"")B1-be:
=HAHIBA(INDEX($E$1:$G$1;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1);"")C1-be:
=HAHIBA(INDEX($E$2:$G$2;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1);"")Felraktam mindkét változtatot ide.
Hosszabban:
Az INDEX-nél nem a tartománnyal van gond igazán, hanem hogy mindig a szükséges sort adja vissza a tartományból. Itt tehát ennek a képlete a kulcs, hiszen a példádon is látható hogy először 1-től 6-ig kell egy sorszámot visszadni, majd után 1 és 3 és végül 1 és 5 között kell nekünk egy szám.Na erre a problémára ezt a képletet alkottam:
ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))Hozzáteszem, hogy természetesen van egyszerűbb változat is (HA függvények egymásba ágyazása), de annak bővíthetősége nehézkes.
A fenti képlet valójában 3x ugyanaz, nézzük egy példán:
ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))A függvény 3 értéket adhat vissza (-1;0;1) attól függően, hogy az éppen vizsgált sor száma (SOR függvény) az E:F tartományban lévő számokat tartalmazó cellák számánál több-e vagy kevesebb vagy egyenlő.
Konkrétan: ha az 5-ös sorban vagyunk, akkor 0-t fogunk kapni (=(5-1)/9), ami azt jelenti hogy az E:F oszlopokból elég választanunk cellát, mivel abban 9 van de nekünk ebből a 9-ből csak az 5 kell.
Ha a 14-es sorban vagyunk akkor pedig 1 lesz, az érték ami azt jelenti hogy nem az E:F oszlopokban van a keresett cella.A 3 db előjeles képlet csak a DARAB függvény paraméterében változik, az elsőben csak az E-oszlopokat nézzük, a másodikban már az E:F oszlopokat és a harmadikban már az E:G oszlopokat. Mindegyik rész 0-t vagy 1-et fog visszadni a fentiek szerint, de példánál maradva az első 6 sorra 0-t kapunk, 7-9-es sorokra 1-et, 10-14-es sorokra 2-öt, felette pedig 3-at.
Ezzel a függvénnyel a neheze már meg is van, de nézzük meg a többit.
Kezdem a B-oszlophoz tartozóval, mert ez az egyszerűbb.
Itt semmi más nem kell mint az E1:G1 tartományból kiírni vagy az első vagy a második vagy a harmadik értéket. A kulcs függvényünk pont erre lett kifejlesztve, így a képlet gyorsan meg is van:=INDEX($E$1:$G$1;ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1)
A C-oszlophoz ugyanez a logika kell csak ott E2:G2 tartományból kell kivenni az értéket, a képlet csak ebben változik.
Az A-oszlop ezek után már könnyedebb dolog lesz, de itt az a gond hogy eleinte E-oszlopból kell érték, majd F-ből és később G-ből. Itt az a megoldásom, hogy a VÁLASZT függvényt használjuk. Amelynek a szintaxisa: VÁLASZT(index szám;érték1;érték2.....).
Egy példán keresztül: VÁLASZT(2;"Férfi";"Nő";"Ismeretlen") eredménye "Nő" lesz mivel a második értéket kértük vissza a függvénytől.
A kulcs függvényünk pont egy sorszámot ad vissza - valójában egyet hozzá kell adnom, mert a tömb nem a nulladik hanem az első elemmel indul -, vagyis először E:E oszlop kell nekünk, majd F és végül G:G. Ezek alapján a
VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G)
attól függően, hogy melyik sorban vagyunk E:E; F vagy G:G-t fog visszadni, amit az INDEX függvényben azonnal fel is tudunk használni.Az INDEX-nek azonban kell egy sorszám hogy hanyadik értéket kell a bemeneti tartományból visszadni. Erre kell egy újabb képlet. Itt a DARABHATÖBB-et használtam (Excel 2007 alatt SZORZATÖSSZEG segít).
Egyszerűen megszámolom az aktuális sorban hogy mellette a B és C-oszlopban szereplő értékek hányszor szerepeltek. A példára visszatérve pl. az első sorban az "a" és a "b" először szerepel, így E-oszlopból az első szám kell, a 4-ik sorban pedig már 4x szerepel így 4. szám kell az E-oszlopból.
A képlet így néz ki: DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)
Itt fontos, hogy mindegyik esetben a tartomány első fele fixen van rögzítve a második pedig fut!Megis vagyunk mert tudjuk a tartományt és hogy hanyadikelem - itt kettőt hozzáadtam mert az első kettőt figyelmen kívül kell hagynunk - kell belőle. A képlet vagyis ez lesz:
INDEX(VÁLASZT(ELŐJEL(INT((SOR()-1)/DARAB($E:$E)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$F)))+ELŐJEL(INT((SOR()-1)/DARAB($E:$G)))+1;E:E;F:F;G:G);DARABHATÖBB(B$1:B1;B1;C$1:C1;C1)+2)Ezzel vége is, persze egy kis HAHIBA függvénnyel kozmetikáztam.
üdv.
[ Szerkesztve ]
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Mutt
aktív tag
válasz dudikpal #19312 üzenetére
Hello,
Esetemben a tartomány A5:A10 között van.
CSE-képlet:
{=HOL.VAN(1;--SZÁM(SZÖVEG.KERES("/ db";A5:A10));0)}Sima képlet (Excel 2007 vagy felette műkődik):
=ÖSSZESÍT(15;6;(SOR(A5:A10)-SOR(A5))/--SZÁM(SZÖVEG.KERES("/ db";A5:A10));1)+1üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Mutt
aktív tag
válasz dudikpal #19335 üzenetére
Hello,
Tehát a rövidített mennyiségi egységnévhez akarod kiíratni a rendes megnevezését.
Kell egy segédtábla, amely tartalmazza a rövid és hosszú nevet. Nálam ez D:E oszlopokban van.A CSE képlet pedig (A2-ben van az eredeti szöveg) ez:
{=INDEX($D:$E;HOL.VAN(1;--SZÁM(SZÖVEG.KERES($D:$D;A2));0);2)}üdv
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Mutt
aktív tag
válasz dudikpal #19342 üzenetére
Hello,
Ez tömbösítés nélkül nem működik?
Nem, alternatív megoldás lehet egy UDF használata...mentés-kilépés kb 20 percig tartott, addig 100%-on pörgött a gép...
Ha tudsz akkor ne az egész oszlopra hivatkozz a képletekben, pl. a D:E hivatkozás helyett ha tudod hogy 500-nál nincs több mértékegységed, akkor D1:E500 sokkal gyorsabban fog lefutni.
Vagy dinamikus tartományokat is használhatsz.üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
dudikpal
aktív tag
válasz dudikpal #19405 üzenetére
Vagy az is jó lenne nekem, ha a meglévő 3 mlban rá tudnék keresni adott cikkszámra, így meglenne a cella pozíciója. Ezt viszont nem tudtam megcsinálni, mert 2 dimenzióban nem tudom hogyan kell keresni.
Ahogy utánaolvasgattam, nem is egyszerű, makrókhoz meg nem konyítok sajna. -
Mutt
aktív tag
válasz dudikpal #19405 üzenetére
Hali,
Azt mondtad, hogy a munkalapokat sorrendbe rendezted, tehát mindegyik lapon a termék adatai ugyanazon sorban vannak. Akkor nem jó neked, ha az eredmény munkalapon
A1-be =csz!A1
B1-be =me!A1
C1-be =mvm!A1 képletek vannak? Ezt lemásolva jön a többi termék adata is.Ha félreértettem, akkor elnézést.
üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
dudikpal
aktív tag
válasz dudikpal #19413 üzenetére
Ha minden igaz megvan a megoldás, csak nem értem.
=OFFSET(DataTable,MOD(ROW()-ROW(ColumnData),ROWS(DataTable)),TRUNC((ROW()-ROW(ColumnData))/ROWS(DataTable),0),1,1)
Behelyettesítettem amiket kell, de hibát jelez az excel, és én sem igazán értem mit keres a képletben az a ",MOD"...ha egyáltalán azzal van a gond
-
lappy
őstag
válasz dudikpal #19415 üzenetére
Szia!
Kipróbáltam és működik!
Létre kell hozni az alábbi tartományokat:
DataTable;ColumnData;ColumnDataRA két képlet magyarul:
ColumnDate
=OFSZET(DataTable;MARADÉK(SOR()-SOR(ColumnData);SOROK(DataTable));CSONK((SOR()-SOR(ColumnData))/SOROK(DataTable);0);1;1)ColumnDateR
=OFSZET(DataTable;CSONK((SOR()-SOR(ColumnDataR))/OSZLOPOK(DataTable);0);MARADÉK(SOR()-SOR(ColumnDataR);OSZLOPOK(DataTable));1;1)[ Szerkesztve ]
Bámulatos hol tart már a tudomány!
-
Delila_1
Topikgazda
válasz dudikpal #19421 üzenetére
Nem azonos a két érték. Az E48-ban idézőjelek között mutatja a számot, ami szöveg típusú értéket jelent, az A29-ben pedig azonos küllemű, de szám tip. érték van. Számokat tartalmazó oszlopban keresel egy szöveget.
A keresési értéknek és a keresés helyén lévő adatoknak azonos típusúaknak kell lenniük.A szóközön kívül sok olyan karakter van, ami nem látható. Töröld ki ezeket a sorokat.
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Delila_1
Topikgazda
válasz dudikpal #19423 üzenetére
Sajnos a cellaformázás az előzően bevitt adatok típusát nem változtatja meg. A számjegyekből álló adataidat kell szöveg típusúvá alakítani.
Ezt úgy teheted meg, hogy hozzájuk fűzöl egy üres stringet egy üres oszlopban az =A1 & "" képlettel – feltéve, hogy az A oszlopban vannak az átalakítandó értékek. A képletet lemásolod a többi adatod mellé, majd az új oszlopot kijelölöd, másolod (ctrl+c), ráállsz a módosítandó oszlopra, jobb klikk, irányított beillesztés, értéket. A segédoszlopot törölheted.
Ha az oszlopot beállítottad szöveg formátumúra, akkor az ezután bevitt adatokkal már nem kell megcsinálnod ezt a cirkuszt, azok már eleve szövegesek lesznek.
Az Excel nem mutatja a nem látható karaktereket. Próbáld meg a következőt oszloponként:
ráállsz az alsó cellára, ami látható karaktereket tartalmaz. Shift+Ctrl+le nyíl megnyomására kijelöli az oszlopnak azt a tartományát, amiben valamilyen karaktereket talál. Nyomd meg a Delete gombot.
Lehet, hogy a füzetben lévő alsó sorig jelöli ki a cellákat, ami azt jelenti, hogy üresek a cellák. Akkor is alkalmazhatod a törlést.Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
Új hozzászólás Aktív témák
- Eredeti Windows, telepítéssel! Digital Doctor Számítógép Szerviz
- Windows 10 11 Pro Office 19 21 Pro Plus Retail kulcs 1 PC Mac AKCIÓ! LEGOLCSÓBB! Automatikus 0-24
- Bontatlan - BATTLEFIELD 1 Collectors Edition - Játékszoftver nélkül
- Windows 10/11 Home/Pro , Office OEM/Retail kulcsok
- Steam,EA,Epic és egyébb játékok Pc-re vagy XBox!