-
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
-
Delila_1
Topikgazda
válasz Gandalf80 #14837 üzenetére
Egy lap első sorába felviszed a kerületeket. Mindegyik alá az ott található utcákat beírod.
Kijelölöd a tartományt az utolsó sorig. Képletek | Definiált nevek | Kijelölésből új. A párbeszéd ablakban a 'Felső sorból' mellett maradjon pipa, OK. Kaptál 23 névvel ellátott tartományt.Az első érvényesítés lista forrása A1:W1. Legyen pl. ez a másik lap A1 cellájában. A B1 is érvényesítés lesz, szintén lista. Ennél a forrás: =INDIREKT(A1)
Szerk.: figyelmesebben olvasva a kérdésedet nem az egyes kerületek, hanem az irányítószámok szerinti utcákat akarod listázni. A felső sorba az irányítószámokat írd, de itt cselezni kell, mert nem szereti névadásnál a számokat. Mindegyik elé tegyél egy alsó kötjelet, pl. _1027.
[ Szerkesztve ]
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
-
lappy
őstag
-
Delila_1
Topikgazda
válasz Gandalf80 #14847 üzenetére
Lemaradt a "Miért"-re a válasz.
A névadásnál tartományokat adtál meg, amik az "_1. ker", "_2. ker", stb. névre hallgatnak. Az INDIREKT függvény a neve alapján a tartományt hívja elő az érvényesítésben.
Nézz rá az indirekt függvényre a súgóban, és világosabb lesz.
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 Gandalf80 #14861 üzenetére
A csúf #HIÁNYZIK és #ÉRTÉK adatokat nem tudod eltüntetni egy HA függvénnyel, vagy feltételes formázással?
D1 feltételes formázása: =hibás(d1), és itt a háttér színére állítod a karakter színét.
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
-
-
-
Delila_1
Topikgazda
válasz Gandalf80 #14898 üzenetére
Ilyent nálam is írt ki érthetetlen módon, hiszen a D:\valami\ könyvtárból olvasta be a füzet nevét. Bezártam az Excelt, újra indítottam, és helyreállt a lelke.
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 Gandalf80 #15393 üzenetére
A második kérdéshez meg kell adnod, melyik cellába akarod bevinni a kezdőbetűt, hol lesz az érvényesítő cella, és még azt is, melyik oszlopba tehetek egy segédoszlopot, ami az ideiglenes adatokat tartalmazza.
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 Gandalf80 #15396 üzenetére
Feltöltöttem ide.
Az 'Összes utca' A oszlopába bemásoltam Bp. I-III. kerületének az utcáit egymás alá. Másold alá az összes többit, a nem pestieket is. Mikor megvan, rendezd emelkedő sorrendbe, töröld az egyformákat.
A Rögzítő lap A1 cellájába írd be a kezdő karaktert (nagybetű!), és az E oszlopban csakis az ezzel kezdődő közterületek jelennek meg a legördülőben.
Csakis az E2-ben adtam meg az érvényesítést, másold le alá a többi cellába is. Engedélyezni kell a makrókat a füzet megnyitásakor.
[ Szerkesztve ]
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 Gandalf80 #15396 üzenetére
Egy kicsit egyszerűsítettem a képleteiden a Rögzítő lapon.
D2 -> =HA(HIBÁS(FKERES(C2;'Bp. utcái és irányítósz.'!$AG:$AH;2;0));" ";FKERES(C2;'Bp. utcái és irányítósz.'!$AG:$AH;2;0))
H2 -> =HA(HOSSZ(D2)>1;D2&", "&E2&" "& F2;"")
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Mutt
aktív tag
válasz Gandalf80 #16176 üzenetére
Hello,
...egy legördülő listából válassza ki a megfelelő települést a felhasználó?
Megoldható az ELTOLÁS (OFFSET) függvénnyel.
Ha C2-ben van a kiválasztandó irányítószám és A és B oszlopokban az irányítószám és a település, akkor ezt a függvényt használd az érvényesítés (validation) forrásának:=ELTOLÁS($B$1;HOL.VAN(C2;$A:$A;0)-1;0;DARABTELI($A:$A;C2))
Ennek a megoldásnak az a hátránya, hogy az igénylőnek akkor is ki kell választania az értéket a legördülő listából, amikor csak 1 találat van.
Ezért csináltam 2 makrót használó megoldást is neked, amely ezeket ki tudja küszöbölni.
Az egyszerűbb megoldásnál (F2 és G2-es mező) a fenti megoldás van makróval kiegészítve, ami ha
- nincs találat kiírja azt vagy
- egyértelmű találatnál a keresett értéket és
- ha több találat van akkor pedig kírja hogy válasszanak.Ennek a program kódja:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("F2").Address Then
'megszámoljuk hány találatunk van
Select Case WorksheetFunction.CountIf(Range("A:A"), Range("F2"))
Case 0
'F2-es cellától jobbra kiírjuk a választ
Range("F2").Offset(, 1).Value = "Nem található település"
Case 1
Range("F2").Offset(, 1).Value = WorksheetFunction.VLookup(Range("F2"), Range("A:B"), 2, False)
Case Is > 1
Range("F2").Offset(, 1).Value = "Válassz a listából!"
End Select
End If
End SubA másik megoldás (F10 és G10-ben), annyival több hogy több találat esetén fel dob egy ablakot, amiből választhat a felhasználó.
Feltettem ide a mintát.
üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Delila_1
Topikgazda
válasz Gandalf80 #17666 üzenetére
NE VOND össze a cellákat az A oszlopban, és minden sorba írd be a dátumot, akkor már erre az oszlopra is hivatkozhatsz a szumhatöbb függvénnyel.
Ha zavar a sok dátum, az elsőn kívül mindegyiket a háttér színével írd.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
-
Mutt
aktív tag
válasz Gandalf80 #17669 üzenetére
Hali,
Delila_1 megoldása mellett itt van egy másik megközelítés.
Az eredeti koncepcióban én úgy értelmeztem hogy 5-20 sorok közötti értékeket kell összeadni, amelyet meg lehet szimplán így csinálni:
=SZUM(INDIREKT("C"&F2&":C"&H2))vagy flancosan így:
=SZORZATÖSSZEG(C:C;--(SOR(C:C)>=F2);--(SOR(C:C)<=H2))Aztán nézve, hogy inkább dátum oszlop alapján akarsz összegezni jött ez ki:
=SZUM(INDIREKT("C"&HOL.VAN(F2;A:A;0)&":C"&HOL.VAN(H2;A:A;0)))Itt a lényeg, hogy a HOL.VAN (MATCH) függvénnyel megkeressük, hogy melyik sorban van a dátum.
Majd ezt felhasználva előállítjuk az INDIREKT-el a cella hivatkozást és összegzünk.üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
veterán
válasz Gandalf80 #19835 üzenetére
szia, az első kérdésedre tudom a választ, biztos leeht jobban is csinálni, de működik, én is csak most tanulom a dolgot.
Sub transzponalas()
ActiveSheet.Range("A1:B7").Copy 'itt adod meg a másolandó ranget amire épp szükséged van
'itt megadod a másolás helyét, a Munka2-t átírod a kívánt munkalap nevére, jelen esetben azt hiszem Kiküldve I, A1 cella
'helyére pedig a tartomány első celláját írod
Sheets("Munka2").Range("A1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
'Sheets("Munka2").Activate ha szeretnéd, hogy a másik munkalap legyen aktív ezután, akkor vedd ki e sor elejéről a '-t
Application.CutCopyMode = False 'ez megszünteti a kijelölést
End Sub[ Szerkesztve ]
"a jövötsajnos nemlehet tudni csakhamárotvagy deakormegmár azajelen"
-
Mutt
aktív tag
válasz Gandalf80 #19835 üzenetére
Hello,
2. Feladat:
Van-e arra mód, hogyha valaki áll egy cellába, akkor az adott sor színes legyen?Erre nem láttam még választ, de itt van pár lehetőség. A minta fájl letöltethető innen.
1. Tisztán VBA-val vhogy így megoldható:
Private korabbi As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim kijelol As Range
'elég csak 1 oszlopot megjegyeznünk
Set kijelol = Target.Resize(, 1)
'ha még nincs mit eltüntetni, akkor csak szinezünk
If korabbi Is Nothing Then
kijelol.EntireRow.Interior.Color = RGB(127, 127, 127)
Else
korabbi.EntireRow.Interior.Pattern = xlNone
kijelol.EntireRow.Interior.Color = RGB(127, 127, 127)
End If
Set korabbi = kijelol
End SubA kód a kijelölt sorokat átszinezi, ezért ha eleve ki van szinezve az adatsor, akkor ez elrontja azt.
2. Feltételes formázás
Ehhez kell egy rövid kód:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'válassz a lapon egy üres cellát, ahol tárolhatjuk az aktuális sor számát
Range("G1") = ActiveCell.Row
End SubHa ez megvan, akkor már csak egy feltételes formázást kell beállítani.
3. Add-in használata
A Kutools tud ilyet.ü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 Gandalf80 #20069 üzenetére
Hello,
..A oszlopban dátumok vannak...terjedö idöszakban számolja össze az adatokat. De ezt úgy,h két külön cellàba adom meg a kezdö és végdàtumot.
Ugyanazt a tartományt többször is felhasználhatod szűrési feltételenek, ha pl. a B-oszlopot akarod összeszámolni és a kezdődátum D1-ben, a végdátum E1-ben van, akkor:
=SZUMHATÖBB(B:B;A:A;">="&D1;A:A;"<="&E1)üdv.
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
LordVader80
senior tag
válasz Gandalf80 #20967 üzenetére
Köszi, de egyszerűen nem jövök rá
most ez van a cellában: =FKERES(A2;Adatok!A2: B250;2;HAMIS)
Ha most az A2 cellába nem írok be adatot, nincs, amit keressen, így #HIÁNYZIK jelenik meg. Ezt szeretném eltüntetni úgy, ha később írok az A2 cellába, akkor jól működjön az FKERES függvény.
[ Szerkesztve ]
May The Force Be With You!
-
Delila_1
Topikgazda
válasz Gandalf80 #21244 üzenetére
C2 –> =HA(BAL($B2;3)="Be ";JOBB($B2;HOSSZ($B2)-3);"")
D2 –> =HA(BAL($B2;3)="Ki ";JOBB($B2;HOSSZ($B2)-3);"")
E2 –> =HA(HOSSZ(D2)>0;INDIREKT("A" & HOL.VAN(D2;D:D;0))-INDIREKT("A"&HOL.VAN(D2;C:C;0));"")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 Gandalf80 #21244 üzenetére
Előfordulhat, hogy a megkezdett műszakot másnap fejezi be valaki. Ennek megfelelően kibővítettem a táblázatot.
C és D oszlop képlete marad, ahogy volt,E2 –> =HA(HOSSZ(D2)=0;"";INDIREKT("A"&HOL.VAN(D2;C:C;0)))
F2 –> =HA(HOSSZ(D2)=0;"";INDIREKT("A"&HOL.VAN(D2;D:D;0)))G2 képlete látszik a képen.
Kissé valószínűtlenek az adatok, pl. az 502-es azonosítójú embernél.
[ Szerkesztve ]
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
-
Gandalf80
csendes tag
válasz Gandalf80 #24727 üzenetére
Kicsit "fapadosan", de megoldottam. Az A oszlop cellaformázása "szöveg" lett. Létrehoztam egy segédoszlopot, ahol egy szövegfüggvényt alkalmazva megkaptam a hónap neveit (=BAL(A2;5).
Ezt követően már csak egy SZUMHA függvényt kellett segítségül hívnom.
SZUMHA($Z$2:$Z$1000;BAL($A1012;5);D$2$1000)Ennyi
"Az élet pókerjátszmájában a nőket elviszi a bank." http://mikrolimitpoker.blog.hu
-
-
Fferi50
őstag
válasz Gandalf80 #26260 üzenetére
Szia!
1. kérdés:
"Megpróbáltam JOBB, ill. BAL szövegfüggvénnyel, de "számokká" váltak"
Mivel a dátumot számformátumban tárolja az excel, ezért ha a cella dátumformátumú, akkor nem ad helyes eredményt a szövegfüggvény manipuláció.
A dátum egész része a napot, a tört része pedig az órát jelenti. Tehát az egyik oszlopba a dátumod egész részét kell tenni - éééé.hh.nn formátumra formázva a cellát, a másik oszlopba pedig a "maradékot", idő formátumra formázva a cellát.2. kérdés:
"egy oszlopban található (adott naphoz tartozó) két nevet valamelyik keresőfüggvény kiszűrje, és beírja automatikusan"?
A kérdés az, hogy mivel van elválasztva a két név. A Szöveg.keres függvénnyel megtalálhatod az elválasztó karakter helyét, ellőtte van az egyik név (bal függvénnyel), utána van a másik név (közép függvény, vagy jobb függvény, hossz függvény kombó). A műszak helye pedig index és hol.van függvény kombóval található meg.Üdv.
-
azopi74
addikt
válasz Gandalf80 #26268 üzenetére
Szia!
Még mindig nem értem, miért kéne szétválasztani.
pl ha A oszlopban vannak az időadatok, a következő függvény pont a műszakot adná vissza,=IF(HOUR($A2-1/3)<12,"I","II")
=HA(ÓRA($A2-1/3)<12;"I";"II")
Aztán ezt használhatod tovább bármilyen függvényben, darabteliben, vagy amiben akarod
Persze ha ragaszkodsz a szétszedéshez, akkor azért leírom, hogy tudnád szöveggé konvertálni az időadatot:
=TEXT($A2,"YYYY.mm.DD h:mm")
magyarul
=SZÖVEG($A2;"YYYY.mm.DD h:mm")
(de lehet, hogy Y helyett é, m helyett h, d helyett n, h helyett ó, m helyett p - nem ismerem a magyar excel hülyeségeit )
Ezután már használhatsz rajta bármilyen szöveg manipuláló függvényt, (BAL, JOBB, SZÖVEG.KERES, vagy amit akarsz)
De outputként szintén szöveget fog visszaadni, amivel viszont nem nagyon fogsz tudni bánni. CSak ha vissza konvertálod érték (idő) adattá, de annak az ég világon semmi értelme nem lenne -
bteebi
veterán
válasz Gandalf80 #26268 üzenetére
Szia!
A dátumot és az időt egyszerűen a kiindulási dátum tört, illetve egészrésze adja. A dátumot 1900. január 1-jétől eltelt napok számaként tárolja (1900. január 3-a számként így 3 lenne), csak a formátuma más. Az egészrészt az INT függvénnyel tudod meghatározni, a törtrészt pedig úgy, hogy kivonod az eredeti számból az egészrészét.
A dátum: =SZÖVEG(INT(A1);"éééé.hh.nn")
Az idő: =SZÖVEG(A1-INT(A1);"óó:pp")Ebben az esetben az A1-es cellában van az átalakítandó időpont. A formátumokat úgy adod meg, ahogy jólesik. "hhh" esetén 3 betűs rövidítést ad (pl. már, ápr), "hhhh" esetén kiírja a hónap nevét (március, április), "óó" esetén az egy számjegyből álló órák elé nullát tesz (pl. 08, 09, de 10), "ó" esetén pedig egyszerűen kiírja a számot (8, 9, 10).
#26266: Pedig prózai a megfejtés. Egyszerűen nem ismerik az &-es összevonást . Én spec. az ÖSSZEFŰZ függvényt nem ismertem viszonylag sokáig.
Cancel all my meetings. Someone is wrong on the Internet.
-
Delila_1
Topikgazda
válasz Gandalf80 #26268 üzenetére
=HA(ÉS(IDŐ(ÓRA(D1);PERC(D1);0)>=IDŐ(6;0;0);IDŐ(ÓRA(D1);PERC(D1);0)<=IDŐ(14;0;0));"I";"II")
Rövidebb formában:
=HA(ÉS(D1-INT(D1)>=0,25;D1-INT(D1)<=0,583333333335759);"I";"II")
[ Szerkesztve ]
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
azopi74
addikt
válasz Gandalf80 #26295 üzenetére
Csak egy kérdés, mielőtt javaslatot adnék: Ha jól értem, az A:C oszlopokban szereplő adatok alapján kéne kitölteni az F:J táblát, ugye? De mi alapján? a dátum alapján, vagy a Műszak (B oszlop) alapján? Mert itt-ott ellentmondásos (hibás a Műszak oszlop). Miért nem függvénnyel van kalkulálva?
[ Szerkesztve ]
-
Delila_1
Topikgazda
válasz Gandalf80 #26297 üzenetére
Másik megoldás, mikor a dátumot és a műszakot kiválasztva a H1 és H2 cellában, a H4 érvényesítésében a választásnak megfelelő nevek jelennek meg.
Itt is elrejthető a J oszlop, az L-ben pedig bővíthető a névlista. Ez utóbbi a C oszlop érvényesítéseiben jelenik meg.
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
- Magga: PLEX: multimédia az egész lakásban
- Luck Dragon: Asszociációs játék. :)
- Vicces képek
- Okos Otthon / Smart Home
- Milyen autót vegyek?
- MasterDeeJay: Volta a bányából azaz CMP 100-210 kisteszt (Tesla V100 mining)
- Telekom mobilszolgáltatások
- Kertészet, mezőgazdaság topik
- exHWSW - Értünk mindenhez IS
- Facebook és Messenger
- További aktív témák...
- Steam, Windows, Origin kulcsok, előfizetések közvetlenül a kiadótól, a LEGJOBB ÁRON!
- Bitdefender Total Security 3év/3eszköz! - "Tökéletes védelem most kedvező áron..."
- Vírusirtó, Antivirus VPN kulcsok
- Steam,EA,Epic és egyébb játékok Pc-re vagy XBox!
- Játékkulcsok olcsón: Steam, Uplay, GoG, Origin, Xbox, PS stb.