Az „Excel” fórum célja, hogy keretet adjon az Excel felhasználók széles táborának tapasztalataik megosztására, és lehetőséget a segítséget kérőknek. Az alábbi összefoglaló azért készült, hogy segítse a helyes kérdésfeltevést.
– Írd le szabatosan a problémát. Úgy fogalmazz, hogy ne csak te magad, de a szakértő is megértse, mire szeretnél választ kapni.
– Írd le, hogy milyen verziójú Excellel dolgozol. (Vagy ha nem – ill. nem csak – Excel, akkor micsoda?)
– Írd le, hogy milyen úton indultál el, és hol akadtál el rajta.
– A kérdés megértése szempontjából sokat segíthet, ha feltölteszt egy képet, amin látszik, hogy mit szeretnél, vagy illusztrálja azt.
– Még jobb, ha feltöltesz egy minta munkafüzetet valahová (pl. data.hu). Feltöltés előtt távolítsd el belőle a nem publikus adatokat.
– Ha a feladat jellege olyan, célszerű az "előtte" és "utána" állapotokat bemutatni. (Miből kellene csinálni mit?)
– Ha VBA kódon kell javítani, másold be a releváns kódrészt. Rövid kód mehet hozzászólásba, hosszú kód inkább ide: http://pastebin.com/
– Ha valami nem úgy működik, ahogy kellene, add meg a rendellenes viselkedés jellemzőit, a hibaüzenetet, és a hibát okozó programsort.
Köszönöm a segítő szándékot. Időközben volt egy kevés egyéb feladatom, ezért csak most válaszolok.
Hát sajnos a szövegfelvágottba még jobban belekeveredtem.
Egyszerűen képtelen vagyok programból megoldani a SumIf függvényt. És hogy ne rébuszokban adjam elő a panaszomat, készítettem a tesztfájlt, benne egy nyúlfarknyi tesztprogrammal, amin bemutatom a problémámat: http://data.hu/get/7127187/sumif_teszt.xls
A tesztfájlban néhány mintaadat van. A program először a munkalapot átmásolja egy új lapra, és azon dolgozik, hogy tesztelés közben ne rontsuk el az adatokat, hátha újra akarjuk indítani.
Ezután az új teszt munkalapon létrehoz egy új fejlécsort, és ebbe átmásolja a fejléc oszlopok első néhány karakterét, hogy ezek felhasználásával tudjuk a feltételes összegzést végrehajtani.
A teszt kedvéért bemásoltam értékadásra a makrórögzítővel kapott eredményeket, és az működik. Ezeket kommentekben be is írtam a programba.
A programmal létrehozattam (látszólag) ugyanazt a kódot, amit a makrórögzítővel kaptam.
És az új munkafüzet végén az első szabad oszlopba be is másoltam ezt az értéket. De valamiért nem működik. Pedig nem látom a különbséget a programom által előállított kód és a makrórögzítős kód között.
Másrészt, mivel gondolom a telefonköltségekre vagy elsősorban kíváncsi, az még egyszerűbben megoldható a Szumha függvénnyel:
A magán-céges listádat tartalmazó táblázatod utolsó oszlopa után írd be a következő képletet:
= szumha(híváslista telefonszám oszlopa;telefonszám;híváslista költség/díj oszlopa), azaz
=szumha(munka2!A:A;munka1!A2;munka2!G:G), ha a híváslista a munka2 munkafüzetben van, A:X oszlopig, a táblázatod pedig az A oszlopban tartalmazza a telefonszámot és az első sor az fejléc.
Ezt a képletet húzd végig a táblázatodon lefelé.
Az oszlopot megcímezheted a hónappal és így minden hónapot behozhatsz ide egy-egy oszlopba.
Természetesen a copy irányított beillesztés érték manőver itt is kell, hogy a képletek "eltűnjenek".
A táblázatodban az első oszlopban vannak a telefonszámok, a második oszlopban a minősítése, hogy magán vagy céges (azaz ugyanabban az oszlopban!), mellette lehet a harmadik oszlopban pl. a név.
Nevezd el ezt a táblázatot pl. maganceges (kijelölöd a táblázatot, utána E2010-ben képletek, név megadása, munkafüzet szintű név)
Gondolom, a híváslistád is Excelben van, vagy oda betehető. Legyen a híváslista ugyanabban a munkafüzetben, mint a táblázatod, de külön munkalapon az A1 cellától.
Az fkeres függvénnyel megoldható a feladat, a híváslista utolsó oszlopa után írd be a képletet az első cellába:
=ha(hibás(fkeres("A1";maganceges;1;0));"Nincs ilyen szám";fkeres("A1";maganceges;2;0))
Ezt a képletet húzd/másold végig a híváslista végéig. Utána jelöld ki az oszlopot, másol, majd irányított beillesztés--értéket. Utána Esc, hogy visszatérj normál módba.
Ezután már bármilyen szűrést tudsz a kiegészített híváslistában csinálni.
Ha a "maganceges" táblázatodban a név van az első oszlopban, akkor cseréld meg az oszlopokat, hogy a telefonszám legyen az első oszlop. (Ha ezt nem akarod, akkor az index függvényt kell használni a hol.van függvénnyel kombinálva.)
A neveket ugyanezzel a módszerrel adhatod hozzá a híváslistához, ott azt az oszlopot kell a második fkeresbe írni, amelyikben a név található.
És te miben kapod ezt a listát a szolgáltatótól, xls-ben vagy csv-ben?
Tudom, hogy unalmas vagyok, de azért kérdezem, mert ez megint egy tipikusan adatbázis-kezelőre való feladat. Én úgy fognék hozzá, ha választhatnék. Határeset, mert meg lehet még oldani viszonylag normálisan Excelben is, de mivel ismétlődően kell elvégezni a munkát, szerintem fölöslegesen bonyolult lesz.
Segítséget szeretnék kérni. Az adott havi híváslistából kellene kigyűjtenem a magán és céges hívásokat. Ehhez adott egy lista az aktuális hónapban, hogy melyik céges számról milyen hívást indítottak. Ez a lista excel-ben van. Van egy másik táblázatom, amiben céges számonként gyűjtöm a munkavállalók által lenyilatkozott számokról, hogy céges szám vagy magán. A kérdésem az lenne, hogy melyik függvénnyel tudom azt megcsinálni, hogy kikeresse, hogy az adott eszközhöz (céges számhoz) tartozó hívás az az én nyilvántartásomban ennél a céges számnál magán vagy a céges oszlopban van?!
A VB szerkesztőben a lapodra állsz. A tulajdonságainál (properties) a ScrollArea értékeként add meg a területet, ahova a felhasználó írhat, pl. A1:C15. Ezután csak ebbe a területbe léphet be a júzer, de a többi cellában a fenti területre történő hivatkozások, képletek továbbra is működnek.
Az adott tartományt (Range-et) rendeld a makróban egy változóhoz mielőtt dolgozni kezdesz vele.
Pl:
dim tartomany as range,talal as range
set tartomany=activesheet.range("A1:Z25")
set talal=tartomany.find(what:="mit",lookin:=xlvalues,lookat:=xlwhole)
if not talal is nothing then
msgbox "Megtaláltad, a cella helye " & talal.address
else
msgbox "Nincs találat"
endif
De activesheet helyett működik másik munkalapon is, akkor ide a munkalapot úgy írd be, hogy
sheets("Munkalapneve").range("A1:Z25")
A keresésnél a lookat paraméter azt határozza meg, hogy az egész cellának meg kell felelnie, vagy részleges találat is jó. Ez utóbbi esetén xlpart a paraméter értéke.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("B5:B10")) Is Nothing Then MsgBox Target.Address End Sub
Tökéletesen bevált a leírásod, sikerült összefűznöm a két táblázatot. Kezdtem a számformátummá konvertálással, majd az első képlettel a hét oszlop átvitele után is az eredmény oszlopokat át kellett konvertálnom számformátumúvá, ugyanis szövegként össze-vissza kutyulta az eredményeket.
A nevek átvitelénél két kis gubanc volt, a képletben az utolsó 3-ast ki kellett javítanom 2-esre, mert a 3-ik oszlopot hozta a nevek helyett, ami ugye a második oszlop. Szerencsére magamtól rájöttem a megoldásra. A másik gond, hogy egyszerre nem sikerült a művelet a 10K tételnél. Sok cellába hibás (üres) eredményt írt, így megpróbáltam 6-700 cellánként lehúzni fokozatosan, így tökéletes lett a végeredmény!
Nagyon köszönöm mégegyszer, borzasztó nagy segítség volt, több órát mentettél meg az életemből!
Ha számok vannak az első oszlopban, de nem számként, hanem szövegként vannak tárolva, akkor az fkeres csak akkor találja meg azokat, ha a keresendő érték is szöveges formában van. Tehát ilyenkor a keresendő értéket is szöveggé kell alakítani a kereséshez (pl. szöveg(A1;"#"))
Ha az első oszlop számként van tárolva, akkor viszont a keresendő értéket is számértékként kell keresni (pl. érték(A1)).
A dolog szépsége, hogy (ha nincs bekapcsolva a hibafigyelés) első ránézésre nem látszik meg, hogy milyen típusu a cella formája és ezek akár keveredhetnek is. Aztán törheted a fejed, hogy az egyik "számot" miért találta meg, a másikat pedig miért nem. (Emlékezz a szöveges dátum problémára.)
Szám vagy dátumértékek keresése alkalmával ügyeljen arra, hogy a tábla első oszlopában az adatok ne szöveges értékként legyenek tárolva. Ebben az esetben az FKERES hibás vagy váratlan eredményt adhat.
A két táblázat egy munkafüzetben van, két külön munkalapon. Ha nem így lenne, azt gondolom meg tudod csinálni.
Az 5000 cikkszámos táblázatban levő minden cikkszám előfordul a 10000-es táblában.
Javaslat:
Az 5000 cikkszámos táblázatot nevezd el pl. jocikk -nek. (2010-ben képletek - név megadása, munkafüzet szintű, korábban név hozzáadása vagy valami ilyesmi.)
C-I oszlopok adatainak áthozatala
Ezután a 10000-es táblázatban a J1 cellába (mivel az I oszlopban még van adat) írd be a következő képletet
és így tovább, egészen a P1 celláig, minden oszlopban növeld a számot 1-el.
Ha megvan, akkor az egész első sorban (J-P oszlopok) levő képleteket húzd le a 10000 sor végéig.
Eredmény: azokban a cellákban, ahol "jocikk" táblában van adat, az ide belekerül, egyébként látszólag üres marad a cella (de a képletet látod benne!)
A képletek "eltüntetése": kijelölöd a J-P oszlopokat, másolás, kijelölöd a J1 cellát, irányított beillesztés értéket.
A nevek áthozása:
Itt is az fkeres függvényt használjuk, de nem a B oszlopba írjuk a képletet (mert akkor az eredeti neveket "hazavágnánk"), hanem a sorok következő, Q1 cellába:
Ennek az lesz az eredménye, hogy minden olyan cikknél, ami szerepel a "jocikk" táblában, az új név lesz itt, amelyik nem szerepel, a régi neve a B oszlopból.
Segítséget szeretnék kérni, sajnos csak alapszinten értek az Excelhez, viszont van egy nagyobb feladatom, ami bőven meghaladja a tudásom:
Van két táblázatom: 1. táblázat 10000 tételes raktárkészlet A oszlop: cikkszám B oszlop: név C,D,E,F,G,H oszlopok egyéb adatok
2. táblázat 5000 tételes kiegészített és javított raktárkészlet A oszlop: cikkszám B oszlop: név C,D,E,F,G,H,I oszlopok egyéb adatok (teljesen különbözik az 1. táblázat egyéb adataitól)
A feladat:
egyesíteni a két táblázatot az alábbiak alapján: 1. A tételek cikkszáma mindkettőben megegyezik, a többi adat nem. Tehát A oszlop fix. 2. Egyező cikkszám esetén a 2. táblázat név oszlop adata felülírja az 1. táblázat név oszlopát 3. A 2. táblázat C,D,E,F,G,H,I oszlopai az egyesített táblázatban I,J,K,L,M,N,O oszlopba kerülnek (tehát a 2. táblázat C,D,E,F,G,H,I oszlopai nem írják felül az 1. táblázat C,D,E,F,G,H és üres I oszlopaitt hanem eltolódnak)
Nagyon szájbarágós leírást kérnék szépen, tényleg csak alap szinten kezelem a szoftvert.
Próbálkozásként oda eljutottam, hogy van már egy táblázatom, ahol az oszlopok a helyükön vannak, viszont 5000 tétel kétszer szerepel. Esetleg lehet innen könnyebb kiindulni. Itt már csak az egyező cikkszámú sorok összefűzése lenne a feladat, megtartva minden oszlop adatait, és a sorrendben második tétel neve felülírja az elsőt.
E helyett: ws.Cells(i, j).Formula = "=sumif(C1:CT1," & j & ",B" & i & ":CT" & i &")"
A formulákban nem kell idézőjelbe tenned az oszlopok betűjeleit, írhatod úgy, mint ahogy a cellában írod a képletet. Viszont a változók értékeit csak hozzáfűzéssel lehet a képlethez hozzáadni. Ezért akárhányszor változó értéket használsz, ott mindig hozzáfűzés kell.
Ha idézőjel kellene valamiért a képletbe, pl. egy szövegdarabot akarsz beletenni, akkor ott dupla idézőjelet kell használni az elején és a végén is ( & ""szöveg"" &).
Továbbá a számértékeket nem kell átalakítani szöveggé, azt "magától" megteszi az összefűzésnél (pláne nem trim(str(i)).
Nem egészen értem a j szerepét a feltétel helyén. ide annak a cellának a címét kell megadni, vagy azt a szöveget, amire az összesítés ki van hegyezve. (Persze, ha a hónapot számmal jelölted és csak annyi a feltétel, akkor nem kérdés a kérdés.)
Kösz, tbando képlete segítségével kézből kiadva tökéletesen működött. (Valóban be kellett hozzá illesztenem egy új 1. sort, hogy az eredeti oszlopok végén szereplő dátumot mutató részek kiessenek. Tehát az új első sorba csak az oszopok első néhány karaktere került, amivel így az azonosakat felismerte az Excel.
Viszont programba képtelen voltam beírni. Lehet, hogy a 2003-as verzióm volt az oka, és most nincs energiám megnézni ugyanezt 2007 alatt.
Szóval makrórögzítéssel sikerült megtalálni a formulát:
ActiveCell.FormulaR1C1 = "=SUMIF(R1C2:R1C156,""valami"",RC2:RC156)", és ez természetesen makróban futtatva is működött. De ezt nem tudtam szabályos függvénnyé alakítani:
Amig a kódot írtam, a VBA felismerte a sumif-et és szépen átírta SumIf-nek, de amikor ráfutott a vezérlés, akkor azt mondta, hogy Sub or Function not defined, és a SumIf nevét jelöli meg a hiba okaként.
A gond az, hogy egyelőre nem tudom a fentieket átalakítani a makrórögzítő és az általad is megadott range(cells(1,1),cells(1,utolsóoszlop)).formula="=sum(A3:A22000)" formára.
Mára fel is adom, majd a napokban átrágom magam rajta.
A későbbi kérdésedre: Az első két oszlopban termékkód és név van, de ez az adott feladat szempontjából most nálam érdektelen
Még valami, az nem derült ki, hogy az oszlopokban levő hónapok évekkel hogyan vannak megkülönböztetve, illetve van-e az első oszlopban bármi egyedi (pl. termék neve).
Mert akkor termékre, évekre a szumha(több) függvény kiválóan használható.
Beszúrsz egy sort a táblázat elejére (a fejlécek elé):
majd beírod a képletet mondjuk az A1 cellába =szum(A3:A22000) (ha itt kezdődnek az adataid).
Ezután végighúzod az utolsó oszlopig a képletet.
Persze, ez az árakat is összeadja...
Viszont, ha a képletet makróval írod be: range(cells(1,1),cells(1,utolsóoszlop)).formula="=sum(A3:A22000)", akkor egy ciklussal a felesleges képleteket ki is tudod törölni.
