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.
Számomra a felrakott képből nem derúl ki, hogy mit akarsz.
1. Ott van az A-oszlop, semmi kapcsolata a többivel.
2. Az E oszlop forrása rejtély. Ha feltételezem, hogy az A-oszlopbeli ID-khez kapcsolódnak, akkor a B-oszlopnak is ki kéne lennie töltve az ID-khez tartozó terméknevekkel. Ha így lenne, akkor egy pótlólagos oszlop beszúrásáva az E-oszlop FKERES-sel lenne kitölthető.
3. A H az E-oszlop Darabteli-s értékelésének tűnik. Ha így van, problémamentes.
1.A magán/céges telefonszámlista (legyen a neve tlista).
Szerkezete:
A oszlop: telefonszám
B oszlop: "magán" vagy "céges"
C oszlop: eszköz (céges telefonszám, ahonnan hívják) de ez szerintem felesleges, mert céges számot általában többen is hívnak és magán számot is hívhatnak többen is (tehát több oszlop is kellene, vagy ebben az oszlopban több szám). Viszont az adott havi híváslistából egyértelműen kiderül, hogy éppen ki(k) hívták a számot.
2.Lenne egy listám az eszközök (céges telefon) használójáról (ez biztosan meg is van), hogy személyhez tudjam kötni (legyen a neve nlista).
Szerkezete: A oszlop eszköz (céges szám)
B oszlop használó neve
3.A híváslistát bemásolnám ide (legyen a neve hlista).
Ez megkeresi azokat a számokat a híváslistában, amelyek még nincsenek benne a tlistában. Amelyek benne vannak, oda pedig beírja, hogy magán vagy céges a szám.
A munkalapra autoszűrő, D oszlopban kiválasztod az "Új szám" -ot, a B oszlopban levő számokat kijelölöd, copy, majd a tlista A oszlopának végére bemásolod.
A tlista új számainak B oszlopába beírod, hogy magán, vagy céges.
Ha ezzel megvagy, visszamész a hlista munkalapra és kikapcsolod az autoszűrőt. El kell tűnnie az "Új szám"-oknak és helyettük a magán/céges megjelölés látszik.
D oszlopot kijelölöd. Copy majd irányított beillesztés ugyanide! értéket, utána ESC. Ezzel eltüntettük a képletet.
Most már minden telefonszámhoz megvan, hogy magán vagy céges.
A hlista sorbarendezése: fejléc van.
A oszlop emelkedő majd D oszlop emelkedő.
Ezután adatok részösszeg:
csoportosítási alap: A oszlop illetve a fejléce
összesítő függvény: Összeg
összesítendő oszlop: C Hívásdíj
összegek az adatok alatt
Ezzel megkapod minden eszközhöz a céges és a magán hívások díját.
Próbáld ki, eddig sikerül-e eljutni. Utána lehet még egy kicsit tovább javítani a megoldáson.
Igen, jól érted. Az eszköz a céges telefonszám és erről lehet céges vagy magánhívást is kezdeményezni. Minden hónapban vannak új számok is de amiket már leadtak nem kérdem meg újra.
Két dolgot gyűjtik de külön táblában.
Van egy táblázat amiben gyűjtöm a kollégák által megadott céges és magánszámokat. Ezt minden hónapban frissítem.
Utána pedig az adott havi listából kigyűjtöm mi a céges és mi a magán hívás. Illetve mennyi a magánhívás összege.
A hívás listából kellene megállapítani a b oszlop - azaz a hívott szám - alapján, hogy a hívás magán vagy céges volt-e.
Mivel azt mondod, hogy az eszköz(ami a céges telefon számának felel meg), nyilván hívhat magán és céges számokat is, a telefon használójának kell megmondania, hogy melyik hívott szám milyen célú. Gondolom, ezeket folyamatosan "bemondják" a munkatársak, ha új szám keletkezik.
Még azt nem értem, hogy a Te táblázatodban akkor mit és hogyan gyűjtesz:
A céges telefonhoz (eszközhöz) tartozó konkrét telefonszámokat, vagy a magán és céges hívások díját?
Ezeket legalább ismerni kellene ahhoz, hogy értelmes megoldást tudjunk adni.
Köszönöm szépen a válaszod. A probléma abból adódik, hogy lehet, hogy ami nálam céges szám, az a kollégámnál magán. Így szükséges, hogy az eszközt (céges számot) is figyelembe vegye.
A hívás lista tábla:
a oszlop: eszköz (céges szám)
b oszlop: hívott szám
c oszlop: hívás díja
Az én táblázatom, amiben a számokat gyűjtöm:
a oszlop: eszköz (céges szám)
b oszlop: magán hívás
c oszlop: céges hívás
A saját táblázatom úgy módosítom, ahogy szükséges. Illetve a híváslista táblát is tudom másolgatni, szerkezgetni.
Eddig ezt úgy oldottam meg, hogy a híváslistára rászűrtem céges számonként. Lementettem egy új excel-ben. FKERESSEL a hozzá tartozó általam kígyűjtött táblázatomból megkerestem a céges számokat és a másik oszlopból a magán számokat. Viszont ez nagyon időigényes, mert sok a céges szám. Így segítséget kértem, hátha lehetne egy képlettel könnyebben megoldani és utána csak rá kelljen szűrni.
Lenne megint egy eszement kérdésem: Azt hogy csinálja az excel, hogy az =INDEX($A$4:$A$10;0;0) képlet bizonyos cellákban nem #ÉRTÉK hibát eredményez? Az indexelt tartomány sávjában levő cellákban ugyanis a vele azonos sorban levő értéket adja vissza.
This statement declares that a variableXis an integer — a whole number between –32,768 and 32,767. If you try to setXto a number outside that range, an error occurs. If you try to set X to a fraction, the number is rounded. For example:
Nem tudtam róla. Sehol sem láttam az eltérő kerekítésre történő utalást, így aztán igencsak meglepődtem amikor hülye eredményt kaptam. Eltartott egy darabig, amíg rájöttem az okára....
Eddig jobb híján ciklusban szaladtam végig a Cells.SpecialCells(xlLastCell).Row/Column után hátulról tesztelve a cellák üres voltát. Ez különösen akkor érdekes, ha sorokat/oszlopokat töröltem a táblázat végén.
A válasz az integer és a double közötti tulajdonság különbség. Az integer - és a long - a törteket a kerekítés szabályai szerint kerekíti, azaz neki az 5,51 már 6.
Próbáld ki: k%=5.51 : debug.print k% => 6
k%=5.31 : debug.print k% => 5
Ezzel szemben a double és a variant lebegőpontos és nem kerekít, neki tehát az 5 után következő 6 már nagyobb, mint az 5.51.
Az xlLastCell eredménye nem hamis, hiszen a formázás is a használatba vétel egyik formája - hiszen az xlLastCell a munkalap használt tartományának utolsó celláját adja meg - csak az eredmény nem a mi elképzelésünknek felel meg...., mi nem ezt akartuk megtudni, de az Excel ezt tudja nyújtani.
Ezért én nem is használom ezt a paramétert.
Akkor már inkább a specialcells xlcelltypeblanks (vagy xlblanks) paraméterét érdemes használni. Ez megmutatja, hogy hány üres cella van és azok hány területen helyezkednek el, a címét is megmondja területenként.
Szerintetek mi a logikája annak, hogy az alábbi makróban a k-változót integernek vagy longnak definiálva 6x fut le a ciklus (azaz kerekít), míg double vagy variant k-k esetében 5x (azaz csonkol)?
Sub ciklusszam() Dim k Range("o1:o10").Clear For k = 1 To 5.51 Cells(k, 15) = k Next k End Sub
sorok hamis eredményt adhatnak, ha az adatokon kívüli celláknak valamilyen formát adtam.
Sub mm() Dim usor As Long, uoszlop As Long usor = Cells.SpecialCells(xlLastCell).Row uoszlop = Cells.SpecialCells(xlLastCell).Column MsgBox usor & Chr(10) & uoszlop End Sub
A formázott terület utolsó sorát-, és oszlopát adja ereményül (szegélyezés nagyobb területen, mint az adatok).
Ha új adatot kell beírnod, akkor sem kell a képleten módosítani: az utolsó adatokat tartalmazó 3 oszlop elé szúrj be 3 oszlopot. Ekkor az összegző oszlopok képletei automatikusan alkalmazkodnak! (Ha az összegző oszlopok elé szúrod be, akkor nem!) Ha fontos az adatok sorrendje, akkor az "eltolt" 3 oszlop adatát másold be a beszúrt oszlopba és a helyére kerüljenek az új adatok.
Szerintem még szebb, ha az összegző oszlopokat közvetlenül a név oszlop után teszed (azaz az adatok elé). Ennek az az előnye, hogy az összegzőképletet kiterjesztheted az ezután beírandó (még meg sem lévő) adatokra a beíráskor (akár a munkafüzet utolsó oszlopáig!). Az új adatokat egyszerűen csak be kell írni a meglevőek mögé és máris kész az új összegzés.
Az összegzést megcsinálhatod egy új munkalapra is, hogy csak azt lássák mások. Ekkor az összegző képletbe fel kell venni az adatokat tartalmazó munkalap nevét: (az adatok az Adat nevű munkalapon vannak)
És csak most veszem észre a végső trükköt, ahogy veszem át soronként a programot a sajátomba.
Így még azzal sem kell bíbelődni, hogy a rövidített neveket /left(C1,7)/ bemásolgassam az összegző oszlopok fejlécébe. Egyszerűen a képlet tartalmazza a rövidítést. És így még a képletek értékké való átalakításával sem kell foglalkozni. Nagyon jó!!!
„Bár ez a R1C& ""*"" szintaxis nekem új, de majd megpróbálom megérteni.”
Ezzel kicsit pontatlanul fogalmaztam. Azt értettem, hogy a * itt egy wildcard karakter, csak nem igazán értettem az alkalmazásának módját, illetve a szintaxisát.
A megoldásod "=SUMIF($C$1:$N$1,left(C$1,7) &""*"",$C3:$N3)"
(a fejléc hiányában persze $C3:$N3 helyett persze $C2:$N2-t alkalmazva) tökéletesen működik. Köszönöm.
Igazad van, ugyanakkor a ws.cells() formát január óta használom következetesen, amikor kiderült, hogy az Excel 2007-es verziója (vagy a 2003-asra konvertáló funkció) nem kompatibilis a 2003-assal. Ezt megírtam a http://forum.index.hu/Article/viewArticle?a=123557575&t=9009340 hozzászólásban, amire – az azóta a fórumról sajnálatosan eltűnt és törölt – robbantómester adta meg a kulcsot. A gondot az okozta, hogy hiába választottam ki worksheets(2).select paranccsal egy másik munkalapot, a cells() parancs munkalapnév nélkül csak a 2003 alatt működött jól, 2007 alatt a munkalapváltást figyelmen kívül hagyta. Aztán kicsit később Retro Image (http://forum.index.hu/Article/viewArticle?a=123577102&t=9009340) magyarázta el, hogy miért célszerű egzakt hivatkozásokat használni. Ráadásul most veszem észre, hogy az általa javasolt Application qualifier használatáról időközben meg is felejtkeztem.
Igazad van a"=SUMIF($C$1:$N$1,left(C$1,7) &""*"",$C3:$N3)" képlettel. Ennek a segítségével a 3 oszlop képleteit 1 lépésben beírhatjuk. Nincs szükség sor beszúrására, és törlésére, képletek helyett értékek beillesztésére.
A makró tömörebben:
Sub sumifteszt() Dim utolsósor As Long, utolsóoszl As Integer
Application.DisplayAlerts = False If Worksheets.Count = 2 Then Sheets(2).Delete Application.DisplayAlerts = True
Az hasznos szerintem, hogy a munkalapra is hivatkozik, mivel nem feltétlenül marad mindig az adott munkalap aktív (jó, ebben az esetben nem változik az aktív lap). Így legalább biztosan nem téved el a képlet.
Bár ez a R1C& ""*"" szintaxis nekem új, de majd megpróbálom megérteni.
A szöveg keresésben vannak speciális karakterek (ezt biztosan ismered), a csillag, a kérdőjel.Ezt lehet használni a szumha függvényben, meg más számolós,hasonlítós függvényben is. A fenti formulában azt jelenti, hogy minden olyan számot összegez, ahol az oszlop első cellájában levő érték kezdő karakterei (esetünkben 7 db karakter) megegyezik a megadott karakterekkel. Tehát pl. "Tranzak*" eredménye minden Tranzak-kal kezdődő oszlop összesítése, akármivel is folytatódik.
szöveg(j) itt az zavart meg, hogy a munkalapfüggvények között is van ilyen nevű függvény - az álmoskönyvek szerint nem szerencsés a rendszer neveit bekeverni a saját neveink közé.
chr(34) ahogy már írtam, korrekt a chr(34) használata, ha idézőjelet akarsz a szövegben megjeleníteni - de akkor csak egy kell belőle. A formula képleteknél gyakran előfordul, hogy idézőjel kell a szövegbe, itt az idézőjel megduplázása az elegánsabb megoldás (lásd az első felvetésed és Delila képlete).
Nem tudom, felfigyeltél-e rá, hogy az új lapnak nem adtam nevet, és a Set ws= ... sort sem használtam, a továbbiakban a hivatkozások sem kezdődtek ws. -tal. Mikor a másolatot elkészíted az első lapról, hivatalból a másolt lap lesz aktív, ezért nincs szükség laphivatkozásokra.
A Delila által írt képletet ("=SUMIF($C$1:$N$1," & Chr(34) & "Tranzak" & Chr(34) & ",$C3:$N3)") is át lehetne úgy alakítani, hogy ne kelljen új fejlécsort beszúrni? Hogy a "Tranzak" helyén valami hasonló automatizmus működjön? Próbálgattam, de azzal nekem nem jött össze.
