- Mobil flották
- Honor Magic5 Pro - kamerák bűvöletében
- Poco X3 Pro - hardverfrissítés
- NFC Ring, avagy gyűrű, amivel fizetni is lehet
- Prohardver app (nem hivatalos)
- Milyen okostelefont vegyek?
- Csak semmi szimmetria: flegma dizájnnal készül a Nothing Phone (3)
- Magisk
- Telekom mobilszolgáltatások
- iPhone topik
-
Mobilarena
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
-
Mutt
senior tag
Szia,
Ez az Excel 1993-ig használt makró nyelve, "Excel 4 macro" kulcsszóra keresve még találsz hozzá súgót,
Valószínű több sorod van mint amit megadtál, de a sorok a következőket csinálják:1.
=LÉP("i";2;DARAB2(!$B:$B);1)
Ez egy FOR - NEXT ciklus; lesz vhol egy sorod KÖVETKEZŐ() paranccsal. Ami ezen sorok között van azt fogja annyiszor végrehajtani ahány adatod van a B-oszlopban. Létrehoz egy i nevű változót, amit 2-es értékről indít és növeli folyamatosan amíg a kívánt darabszámot el nem éri. A képlet végén az 1, a lépésköz.Szószerinti VBA megfelelője pedig:
Dim i As Long
For i = 2 To WorksheetFunction.CountA(Range("B:B")) Step 1
'lépések
Next i2.
=KIJELÖL("S6O1")
Kijelöli a 6-os sor 1 oszlopának metszetét, vagyis az A6-os cellát. RC (row-column, magyarul SO - sor-oszlop) hivatkozás van használva, Valószínű ez egy felesleges lépés, általában a kijelölés után egy művelet szokott jönni amit lehet kijelölés nélkül is végrehajtani.VBA megfelelője:
Cells(6, 1).Select3.
=KIJELÖL(ÖSSZEFŰZ("S";i;"O3"))
Itt is egy kijelölés van, csak felhasználjuk a FOR-NEXT ciklusban létrehozott változót, vagyis a harmadik oszlop sorain megyünk végig.Dim i As Long
For i = 2 To WorksheetFunction.CountA(Range("B:B")) Step 1
Cells(3, i).Select
'egyéb lépések
Next i4.
=KÉPLET(A10)
Ez nem makró függvény, hanem az egyik beépített függvény, amely megmondja, hogy az A10-es cella képletet tartalmaz-e vagy sem?Szószerinti VBA megfelelője:
van_benne_keplet = WorksheetFunction.IsFormula(Range("A10"))Hatékonyabb változat:
van_benne_keplet = Range("A10").HasFormulaÉrdemes az Excel 4 makrót lecserélni.
Ha feldobod, hogy mi a feladat akkor kapni fogsz segítséget a fórumon.üdv
-
#39954
Mutt
senior tag
dellfanboy
#39949
Mutt
senior tag
válasz
dellfanboy
#39949
üzenetére
Szia,
Munkalaponként kb. 1 millió sorod lehet, szóval egy fájlon belül csak több lapra fog ráférni az adat. Bináris formátumban (XLSB) beférhetsz a 100MB alá, de éppen csak.
Power Pivot/Query-vel lehet több adaton is dolgozni, azonban a gond hogy csak reportálni tudsz ezekkel, vagyis nem tudsz adatot (cellát) közvetlenül módosítani.
Kérdés, hogy mit kell a felhasználóknak a sok adattal kezdeni?
Ha módosítani is kell, akkor az Excel felejtős. Ennyi adatot többfelhasználós környezetben nem mernék Excel-re bízni.
Ha csak reportok kellenek, akkor Excel-ben a Power Pivot/Query/BI (ez utóbbi külön telepíthető).
Kérdés: Miért nem használható a Power Pivot? Excel 2010 előtti van a gépeken? Excel 2013-től része a rendszernek, vagyis ha van Excel akkor ez is van.Access-t azért nézd meg, mert lehet formokat csinálni, amikkel a felhasználók tudnak adatot keresni/módosítani. Illetve gombokkal lehet kész riportokat/grafikonokat futtatni. Multiuser-ben is megy, ennyi adatnál sokkal gyorsabb mint az Excel. Kérdés hogy a 2 millió sor az belefér-e 2 GB méretbe, mert az lesz a probléma.
üdv
-
Mutt
senior tag
válasz
csferke
#39936
üzenetére
Szia,
1. ...Enter után ... szeretném ha balra lépne vagy ha ezt nem lehet akkor sehova se lépjen.
Nincs ilyen beállítás, jobbra TAB-ot nyomva tudsz lépni, balra SHIFT+TAB.
Talán Script-el ez lehet a megoldás (NEM TESZTELTEM!):function onEdit(e) {
var range = e.range;
if (e.range.getColumn() > 1){
range.getActiveCell().offset(0,-1).activate();
}
}2. Ne jelenítse meg a 0-át (nulla) ha a számítás eredménye nulla.
Egyéni számformátumot használj. Formázás -> Szám -> További formátumok -> Egyéni számformátum -ba írd be ezt:#0.00;-#0.00;;@Ugyanazt a logikát használja mint az Excel, vagyis formátum 4 részből (3-ból ha feltéleket is megadsz) állhat: pozítv számok, negatív számok, nulla, szöveg.
üdv
-
Mutt
senior tag
Szia,
...Google sheetben próbálkozott már valaki makróval? Ahogy elnéztem mégiscsak létezik, én úgy tudtam ahhoz a GS buta...
Ez az első Apps Script-em, de szerintem hozza ami neked kell. Eszközök/Tools -> Szkriptszerkesztő/Script editor alá másold be a lenti dolgot, aztán majd kérni fog engedélyeket.
function onEdit(e){
// get range info of edited cell.
var target = e.range;
if (target.getSheet().getSheetName() == "Munka1"
// if edited cell is in range B3:M3 then update cell A1
if (target.getRow() == 3 && target.getColumn() >= 2 && target.getColumn() <= 14)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getRange("A1");
// if edited cell is non-blank and A1 is blank then set TODAY
if (target.getValue() != "" && cell.getValue() == "")
{
cell.setValue(new Date());
cell.setNumberFormat("YYYY.MM.DD");
}
else
{
// clear A1 if nothing is in range B3:M3
if (sheet.getRange("B3").getValue() == "" && sheet.getRange("C3").getValue() == "" && sheet.getRange("D3").getValue() == "" &&
sheet.getRange("E3").getValue() == "" && sheet.getRange("F3").getValue() == "" && sheet.getRange("G3").getValue() == "" &&
sheet.getRange("H3").getValue() == "" && sheet.getRange("I3").getValue() == "" && sheet.getRange("J3").getValue() == "" &&
sheet.getRange("K3").getValue() == "" && sheet.getRange("L3").getValue() == "" && sheet.getRange("M3").getValue() == "")
{
cell.clearContent()
}
}
}
}üdv
-
Mutt
senior tag
válasz
patesz
#39911
üzenetére
Szia,
A másik lapon (Munka2) az első sorban csináltam egy fejlécet, A-F-ig lesznek az értékek amiket a másik lapról (Munka1) kell megjeleníteni, G-oszlop pedig egy segéd lesz.
A G2 cella képlete (megint csak 1000 sornyi adatot feltételeztem, ezt írd át):
=HA(DARABTELI(Munka1!A:A;"D:")>=SOR()-1;ÖSSZESÍT(15;6;SOR(Munka1!$A$1:$A$1000)/(Munka1!$A$1:$A$1000="D:");SOR()-1);"")Ez megadja, hogy a Munka1 lapon mely sorok a "D:" sorok.
Ezek után az A2-ben egy INDEX-el ki lehet íratni a kívánt értéket:
=HA(Munka2!$G2<>"";INDEX(Munka1!A:A;Munka2!$G2);"")üdv
-
Mutt
senior tag
válasz
patesz
#39907
üzenetére
Szia,
Ahogy írod az igazi nehézség, hogy mindig csak egy adott halmazon (tartományon) belül kell megtalaláni az utolsó előfordulást. Tudnunk kell, hogy egy választott sor előtt melyik az utolsó üres sor és utána melyik az első üres sor.
A G2-es cellába ezt a képletet írd be, hogy meg tudd hogy melyik az a sor ahonnan a tartomány kezdődik (eredményt csak akkor fog kiírni ha az A-oszlopban ott a "D:" kulcsszó):
=HA(A2="D:";ÖSSZESÍT(14;6;SOR($A$2:A2)/($A$2:A2="");2)+1;"")A képlet lényege, hogy mindig a 2-es sortól indulva az aktuális sorig (figyeld az abszolút és relatív hivatkozásokat) megnézi, hogy hol vannak üres értékek az A-oszlopban és abból visszaadja a második legnagyobb értéket. Azért kell a második legnagyobb, mert a "D:"-sorok előtt van egy üres sor és ez lesz a legnagyobb érték, de ez még a minket érdekelt tartomány része, így eggyel korábbi üres sor kell (a második legnagyobb szám).
A tartomány vége is hasonló módszerrel jön ki, de itt a legkisebb számot fogjuk keresni az aktuális sortól lefelé indulva. A H2-es cella képlete:
=HA(A2="D:";ÖSSZESÍT(15;6;SOR(A2:$A$1000)/(A2:$A$1000="");1)-3;"")
Itt éltem azzal hogy 1000-sornál nincs több adatod, de ha lenne akkor írd át!A képleteket húzd le az oszlopokban, hogy minden "D:" sorra megkeresse a helyes értékeket.
Ezek után az E2 cellának a képlete:
=HA(A2="D:";KERES(2;1/(INDIREKT("A"&G2&":A"&H2)="Motor1:");INDIREKT("D"&G2&":D"&H2));"")Az F2-nek pedig:
=HA(A2="D:";KERES(2;1/(INDIREKT("A"&G2&":A"&H2)="Motor2:");INDIREKT("D"&G2&":D"&H2));"")Itt az általad is használt KERES megoldást használjuk, de az INDIREKT függvénnyel előállítjuk mindig azt a tartományt amelyben keresni kell a "Motor1:" és "Motor2:" kulcsszavakat.
üdv
-
Mutt
senior tag
válasz
kvoaksz
#39884
üzenetére
Szia,
Ha feltételezzük, hogy szám után mindig szóközzel elválasztva van az előtag jele akkor az alábbi lépések segítenek:
1. Az első szóközig lévő szöveget (az alapszámot) az alábbi képlet megadja
BAL(A2;SZÖVEG.TALÁL(" ";A2))1b. Ha magyar Exceled van, akkor pl. a 0.7-ben lévő tizedesponttal gondban lesz, de egy cserével megoldható ez.
HELYETTE(BAL(A2;SZÖVEG.TALÁL(" ";A2));".";",")1c. Ha van a számban ezres elválasztó is (pl. 1,620), akkor pedig két cserét javaslok.
HELYETTE(HELYETTE(BAL(A2;SZÖVEG.TALÁL(" ";A2));",";"");".";",")2. Opcionális lépés. Ha lehet más szabványos előtag is, vagyis nem csak "k" lehet, akkor kell ez a segédtábla, ami megadja hogy az alapszámot mennyivel kell majd szorozni. Nekem ez a segédtábla z F:G oszlopban van.
3a. Ha csak "k" lehet a szövegben, akkor a végső képlet ennyi (az első lépésben kapott számot szorozzuk 1000-el és a végére csapjuk a "users" szöveget).
=BAL(A2;SZÖVEG.TALÁL(" ";A2)*1000&" users"3b. Ha más előtag is lehet a szám után, akkor előbb ki kell keresni az előtagot és utána ahhoz megkeresni a szorzót.
Az előtag az első szóköz utáni 2 karakter lehet (a deka-nak 2 karakteres a jelölése).KIMETSZ(KÖZÉP(A2;SZÖVEG.TALÁL(" ";A2);3)))Ezek után ehhez kell megkeresnünk a szorzót, ami egy FKERES/VLOOKUP lenne, csak az a gond hogy itt számít a kisbetű/nagybetű is a mega-milli, peta-pico stb miatt. Tömbfüggvénnyel tudjuk megoldani a helyes keresést, ha SZORZATÖSSZEG-be tesszük akkor nem kell CSE. .
SZORZATÖSSZEG(AZONOS($F$2:$F$22;KIMETSZ(KÖZÉP(A2;SZÖVEG.TALÁL(" ";A2);3)))*$G$2:$G$22)
Már csak össze kell rakni a fent említett részeket:=BAL(A2;SZÖVEG.TALÁL(" ";A2))*SZORZATÖSSZEG(AZONOS($F$2:$F$22;KIMETSZ(KÖZÉP(A2;SZÖVEG.TALÁL(" ";A2);3)))*$G$2:$G$22)&" users"üdv
-
#39882
Mutt
senior tag
szilvásbukta
#39880
Mutt
senior tag
válasz
szilvásbukta
#39880
üzenetére
Szia,
Talán egy törölt fájl visszaállító programmal lehet vmit kezdeni.
Próbáld meg a Recuva nevű programot.Illetve a temp könyvtárban is lehet másolat egy korábbi állapotról:
Windows 7: C:\Users\name\AppData\Roaming\Microsoft\Excel\
Windows 8/10: C:\Users\\AppData\Local\Microsoft\Office\UnsavedFiles\üdv
-
Mutt
senior tag
Szia,
Makró nélkül nincs jó megoldás de próbáld ki az alábbit:
1. Egy félreeső cellába írasd ki az aktuális dátumot a MA() függvénnyel.
2. Névkezelőben adj nevet ennek a cellának (pl. aktualis)
3. Ahol az eredeti képleted van onnan töröld a képletet és adatérvényesítést tegyél rá, ami listat tartalmazhat és az értéke a fenti pontban megadott név.
4. Ezen cella mellé/főlé (ahol van helyed) jöhet vmi hasonló képlet:=HA(VAGY(B3<>"";C3<>"";D3<>"";E3<>"";F3<>"";G3<>"";H3<>"";I3<>"";J3<>"";K3<>"";L3<>"";M3<>"";N3<>"");"Válassz dátumot!";"")Amit ezzel elértünk, hogy nem kerül be automatikusan a cellába egy dátum, de kapsz egy értesítést hogy válasszd ki dátumot a cellában legördülő listából. A lista mindig a mai dátumot fogja mutatni, de a cella értéke nem fog automatikusan frissülni.
üdv
-
-
Mutt
senior tag
válasz
PerezT
#39811
üzenetére
Szia,
Nézd meg ezt a változatot.
Arra kell odafigyelned, hogy bérleti díjakat tartalmazó táblázatban az időszakok mindig lefedjék azt az időszakot amire számolni akarsz. A példádban ha Nagyfalu Bt-re számolnád a költségeket 2019. június 30-ig, akkor mivel most csak május végéig van bérleti díj az utolsó 1 hónapod ingyen lesz.
Tömb-képletet (CSE) használtam. Ha sok adatod van, akkor lelassíthatja az Exceled.
A képlet jelenleg a mai nappal számol (pontosabban a tegnap estig felhalmozott díjakkal), ha akarod a MA() függvényt cseréld le egy cella hivatkozásra ami tartalmazza a lejárati dátumot.
üdv
-
#39706
Mutt
senior tag
LionPrince
#39704
Mutt
senior tag
válasz
LionPrince
#39704
üzenetére
Szia,
PowerPivot-ot kell használnod, ha Excel 2016 vagy újabbad van akkor ott integrálva van, Excel 2010/2013-hoz pedig külön letölthető.
Lépések:
1. Az adatosorodnak táblázatban kell lennie! Beszúrás -> Táblázat
2. Jöhet a Pivot, Beszúrás -> Kimutatás, de itt alul pipáld be az adatmodellhez hozzáadást.
3. Szokásosan tervezd meg a kimutatást.
4. A mezőlistában jobb klikk az adatforrás nevén és Mérték hozzáadása/Add measure.
5. Töltsd ki az ablakot vhogy így
Ebben a Pivot-ban lehet használni a DAX függvényeket, amelyek közül most a DISTINCTCOUNT kell, amely az oszlopban lévő egyedi értékeket számolja meg. Ha magára az értékre van szükség, akkor a VALUES függvény kell.
6. Lesz egy új meződ amit a kimutatásba be tudsz húzni (a Fő elnevezésű).
7. Csinosítgatás után ez a végeredmény.
üdv
-
#39703
Mutt
senior tag
DrTechnoid
#39696
Mutt
senior tag
válasz
DrTechnoid
#39696
üzenetére
Szia,
..azt akarom h pl. a B3-B8 oszlop tartományba csak 1 db érték szerepelhessen..
Adatok menűben Adatok érvényesítése (Data Validation) opciót tudod használni ehhez.
Egyszerűen megszámoltatjuk, hogy a B3
8 tartomány hányszor nem üres, Ha 1-nél többször akkor jön a hibaüzenet.üdv
-
Mutt
senior tag
válasz
andreas49
#39686
üzenetére
Szia,
Tömbfüggvénnyel a megoldás az általad vázolt problémára:
=SZÖVEGÖSSZEFŰZÉS(", ";1;HA(DARABTELI(B2:AA2;SOR($A$1:$A$13)-1);SOR($A$1:$A$13)-1&"="&DARABTELI(B2:AA2;SOR($A$1:$A$13)-1);""))
Ha betűket (A-Z) akarsz megszámolni, akkor ez a képlet (B8:AA8 a tartomány ahol számolni kell):
=SZÖVEGÖSSZEFŰZÉS(", ";1;HA(DARABTELI(B8:AA8;KARAKTER(64+SOR($A$1:$A$27)));KARAKTER(64+SOR($A$1:$A$27))&"="&DARABTELI(B8:AA8;KARAKTER(64+SOR($A$1:$A$27)));""))üdv
-
#39674
Mutt
senior tag
Laciahegyrol
#39669
Mutt
senior tag
válasz
Laciahegyrol
#39669
üzenetére
Szia,
Van még lehetőség akár a Windows Registry-ben is tárolni az eredményt.
Call SaveSetting("Valami", "Érték", "Változóm", SajatEgesz)Előhívni pedig így lehet:
SajatEgesz = GetSetting("Valami", "Érték", "Változóm")Kérdés, hogy pontosan mire kell neked a korábbi állapot mentése? Egyáltalán milyen gyakran változik amit írtál?
üdv
-
Mutt
senior tag
válasz
mcwizard
#39648
üzenetére
Szia,
Makróval lehet megoldani automatikusan amit írtál/kértél. A munkalap SelectionChange eseménye segít ebben.
Jobb klikk a lap nevén és Kód megjelenítése opciót válaszd, majd másold be ezt a szerkesztő ablakba:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.UsedRange) Is Nothing Then
If Target.Column >= 5 And Target.Column <= 8 Then
Columns("F:H").EntireColumn.Hidden = False
Else
Columns("F:H").EntireColumn.Hidden = True
End If
End If
End SubKézi megoldás pedig, hogy F:H tartomány csoportba foglalod, ekkor az oszlop nevek felett megjelenik egy gomb amivel szintén el tudod érni amit kértél egy plusz kattintással.
üdv
-
Mutt
senior tag
Szia,
Munkafüzet felépítésének védelme
Azonban pár perces Google search-el egy kezdő is ki tudja ütni ezen védelmeket. Ne várj csodát.
üdv
-
Mutt
senior tag
-
#39641
Mutt
senior tag
Laciahegyrol
#39638
Mutt
senior tag
válasz
Laciahegyrol
#39638
üzenetére
Szia,
Próbáld ki, hogy ez a minta megy-e nálad?
Ha igen, akkor a nálad lévő fájlban más a felépítés. Több infó kell.
üdv
-
#39634
Mutt
senior tag
Laciahegyrol
#39633
Mutt
senior tag
válasz
Laciahegyrol
#39633
üzenetére
Szia,
2 megoldási javaslat:
1. A formon az OK gomb Tag tulajdonságának a használata.
2. Globális változó, amelyet a form is elér.A példában van 3 gombom (Összeadás, Kivonás és Szorzás) mindegyikhez a "Kattintas" makró van társítva.
Sub Kattintas()
'1-es változat - ahol Sajat a form neve, amelyen bOK a gomb
Sajat.bOK.Tag = ActiveSheet.Shapes(Application.Caller).AlternativeText
'2-es változat - ahol a muvelet egy globális változó
muvelet = ActiveSheet.Shapes(Application.Caller).AlternativeText
Sajat.Show
End SubVan egy form "Sajat" név alatt, amin van egy OK és egy Mégsem gomb (bOK és bCancel).
Az OK gomb eseménye ennyi:Private Sub bOK_Click()
Dim a As Long, b As Long
Randomize 3
a = Int(Rnd() * 100) + 1
b = Int(Rnd() * 100) + 1
Select Case bOK.Tag '1-es változat
'Select Case muvelet '2-es változat
Case "Összeadás"
MsgBox a & " + " & b & " = " & a + b
Case "Kivonás"
MsgBox a & " - " & b & " = " & a - b
Case "Szorzás"
MsgBox a & " * " & b & " = " & a * b
End Select
End SubRemélem ezen el tudsz indulni.
üdv
-
Mutt
senior tag
válasz
wednesday
#39624
üzenetére
Szia,
Felraktam ide egy változatot, amely tudja azokat a dolgokat amiket kértél.
Plusz dolog a részemről, hogy tettem adatvalidációt az űrlapon a név és dátum mezőkre, mert simán lehet hogy vki olyan komibinációt választ amihez nincs adat. Ha vki választ egy nevet, akkor VBA kikeresi hogy mely dátumok valósak hozzá. Ez fordítva is igaz, vagyis dátum alapján leszűkíti a VBA a neveket is.
Ha új keresést akar vki, akkor át kell váltani egy másik lapra és visszajönni az űrlapra.üdv
-
#39618
Mutt
senior tag
l.skywalker
#39616
Mutt
senior tag
válasz
l.skywalker
#39616
üzenetére
Szia,
Nem teljesen világos a kérdés, mert azt is írod, hogy az utolsó sort VBA-ban már kikerested de közben kell egy képlet amivel ugyanezt meg tudod határozni.
Ha VBA-ban van egy változód amiben ott az utolsó sor azonosítója és azt VBA-n kívül akarod használni, akkor a Névkezelőbe feltudod venni:
ActiveSheet.Names.Add Name:="Utolsosor", RefersTo:=UtolsoSorEz munkalap körben hozza létre a változót, ha másik lapon akarsz ráhivatkozni, akkor az ActiveSheet-et hagyd el (vagy cseréld le Thisworkbook -ra).
Innentől már az INDIRECT függvénnyel tudsz a tartományra hivatkozni:
=INDIREKT("A5:A"&Utolsosor)Egy lépésben az A5:A_utolsó sora így is leírható:
ActiveSheet.Names.Add Name:="Utolsosor", RefersTo:=Range("A5", Range("A" & Rows.Count).End(xlUp))Ha képlettel kell az utolsó sor, akkor pedig használható:
=KERES(2;1/(A:A<>"");SOR(A:A))üdv
-
Mutt
senior tag
válasz
MZsoltee
#39598
üzenetére
Szia,
A sorok magassága nyomdai pontokban van megadva, 72 pont = 1 inch = 2,54 cm
Az oszlop szélesség pixelben van és függ a használt betűtípus szélességétől. Bővebben itt.Megoldás:
1. Kapcsolj át Lapelrendezés nézetbe
2. Ekkor azonos mértékegységben (centi, pontok) tudod állítani a magaságot, szélességet.
3. Válts vissza normál nézetbe.üdv
-
Mutt
senior tag
Szia,
Az adatbázis függvényeknél a keresési feltételeket meg tudod adni
- egysorban; ekkor ÉS-ként fogja értékelni a rendszer vagyis csak azon rekordok kerülnek a számításba, amelyek mindegyik feltételnek egyszerre megfelelnek
- többsorban; ez pedig a VAGY-ot jelenti, vagyis elég ha az egyik feltételenek megfelel.Nézzük meg ezeken a példákon keresztül:
Adatsor forrása: KSHBal oldalt van az adatbázis. Jobb oldalon feltételek, a 3 szín 3 példa.
1. Az első példában (narancssárga), két feltételt adtunk megy egymás mellett (azonos sorban vannak), így csak azokat a rekordokat adja vissza ahol ez a két feltétel egyszerre teljesül (ÉS kapcsolat).
Vagyis 3 olyan senior beosztású nő van akiről nem tudjuk hogy hány nyelvet beszél.
A képlet:=AB.SZUM(Adatsor;"Ismeretlen";K2:L3)2. A zöld példában egysorban van 2 feltétel: nő + senior vezető (ennek tudjuk már hogy az eredménye 3) és egy másik sorban van férfi + junior vezető. Az új sor VAGYot jelent, vagyis a függvény visszaadja mindkét eltérő feltételre igaz értéket.
A képlet:=AB.SZUM(Adatsor;"Ismeretlen";K5:L7)3. A kék valójában ugyanaz mint a zöld példa, vkinek ez jobban olvasható/értelmezhető, illetve lehetnek olyan esetek amelyeket csak így lehetne megadni. Itt is a két sor VAGY kapcsolatot jelent, de itt igazából már soronként 3 feltétel van, azonban az üres feltétel nem jelent szűrést.
A képlet:=AB.SZUM(Adatsor;"Ismeretlen";K9:M11)Remélem ezek alapján megtudod csinálni a feladatot.
Bónusz feladat az eredeti adatsorodon: add meg a pontszámok szórását azon tanulók esetén akik az 1-es csoportban 65 pont szám alattiak, illetve a 2-es csoportban pedig 65 pont felettiek.
(Az eredmény 11,42365966)
üdv
-
#39550
Mutt
senior tag
l.skywalker
#39543
Mutt
senior tag
válasz
l.skywalker
#39543
üzenetére
Szia,
ÉS/VAGY/NEM logikai operátorok nem működnek tömb-függvényekben, helyette az alábbi trükköt kell használni:
ÉS = (feltétel_1) * (feltétel_2)
VAGY = ((feltétel_1) + (feltétel_2)) > 0
NEM = (1-(feltétel_1))SZORZATÖSSZEG-el még CSE-t sem kell használnod:
=SZORZATÖSSZEG((E5:E14)*($D$5:$D$14)*((($B$5:$B$14="YES")+($B$5:$B$14="DO"))>0)*((($C$5:$C$14="YES")+($C$5:$C$14="DO"))>0))Illetve ha csak YES/DO és NO van a feltételek oszlopban, akkor talán érdemes azt nézni, hogy hol nem NO az érték.
=SZORZATÖSSZEG((E5:E14)*($D$5:$D$14)*($B$5:$B$14<>"NO")*($C$5:$C$14<>"NO"))üdv
-
#39523
Mutt
senior tag
marchello1
#39522
Mutt
senior tag
válasz
marchello1
#39522
üzenetére
Szia,
Képletekkel ez nehezen oldható meg; jó pár segédoszlop kell ahova mindig vissza kell vezetni a még ki nem húzott neveket.
Erre ma még makró a legjobb megoldás. Felraktam ide egy változatot.
üdv
-
Mutt
senior tag
válasz
Heavyrain
#39511
üzenetére
Szia,
Igazi változás nem történt VBA-ban az elmúlt évtizedben, az esetleges újabb funkciók kaphattak támogatást vagy újabb verziójú objektum libraryk jelentek meg. pl ha jól emlékszem Excel 2010-ből a korábban működő DatePicker form control kikerült, ami akkor nekem szívás volt.
Angol nyelven én John Walkenbach Excel könyvét ajánlom.
Érdemes indulásként mások kódjait nézni, mert abból lehet ötleteket meríteni.
Lehet kérdezni is a fórumon.Véleményem szerint előbb érdemes haladó szintre jutni Excelben (vlookup, pivot, sumproduct, CSE) és csak utána makrót programozni.
üdv
-
#39519
Mutt
senior tag
marchello1
#39509
Mutt
senior tag
válasz
marchello1
#39509
üzenetére
Szia,
Ha VBA-ban csinálod, akkor kódban legyen elől egy Randomize.
Ha munkalapon dolgozol, akkor a nevek mellett egy oszlopba vegyél fel véletlenszámokat (VÉL függvénnyel) és utána ha monduk 5 név kell, akkor a KICSI vagy NAGY függvényel kiválsztanám az 5 számot. Ezeket a számokat HOL.VAN függvénnyel megkeresném a véletlen oszlopban és INDEX-el kiíratom.
Ha nem elég véletlenek a számok, akkor F9-el kérhetsz újabat, vagy szorozz/adj össze két véletlenszámot.
üdv
-
Mutt
senior tag
Szia,
CELLA függvény tudja az aktuális lap nevét visszaadni.
=CSERE(CELLA("fájlnév";A1);1;SZÖVEG.TALÁL("]";CELLA("fájlnév";A1));"")Ha szeretnéd az aktuális lap előtti/utáni lap nevét, akkor a 39483-ban mutatott Excel4 függvények kellenek.
A névkezelőben hozz létre
1. "Lapok" változót, ami a munkafüzet összes lapját listázza, ennek a képlete:=CSERE(MUNKAFÜZETET.VESZ(1);1;SZÖVEG.KERES("]";MUNKAFÜZETET.VESZ(1));"")2. Hozz létre egy "AktivLap" változót is, ami mindig az éppen látogatott lap nevét fogja tartalmazni a legelső képlettel.
3. Az aktuális lap előtti munkalap nevének a képlete ez lesz:
=HAHIBA(INDEX(Lapok;HOL.VAN(AktivLap;Lapok;0)-1);"")4. A következő pedig
=HAHIBA(INDEX(Lapok;HOL.VAN(AktivLap;Lapok;0)+1);"")üdv
-
Mutt
senior tag
Szia,
Nem kell a hiperhivatkozásba az aktuális fájl név.
A lenti képlet a fájlban az Elszámolás lap A1-es cellájára ugrik.=HIPERHIVATKOZÁS("#Elszámolás!A1";"Klikk ide")Ha szükséged van automatikusan a fájlban található munkalapok listájára, akkor egy régi függvény tud segíteni aminek az angol neve GET.WORKBOOK, magyarul pedig MUNKAFÜZETET.VESZ.
Azonban ezt a mostani Excel-ekbe nem tudod a megszokott módon bevinni. Hozz létre a Képletek -> Névkezelőben egy új változót, pl. Lapnevek amelynek ez a képlete:
=MUNKAFÜZETET.VESZ(1)
Innentől a Lapnevek változóban mindig bent lesz az akutális füzet lapjainak neve a fájlnévvel együtt, vhogy így:
[Művdok2.xlsx]T
[Művdok2.xlsx]Elszámolás
[Művdok2.xlsx]Számolólap
[Művdok2.xlsx]Jegyzet
[Művdok2.xlsx]SzabJegy
[Művdok2.xlsx]KLTV SegédletHa neked csak a lapnevek kellenek, akkor egy kicsit hosszabb függvény kell a fenti változóba, aminek a képlete:
=CSERE(MUNKAFÜZETET.VESZ(1);1;SZÖVEG.KERES("]";MUNKAFÜZETET.VESZ(1));"")Ahhoz hogy a fenti függvény listáját egy legördülő menübe lehessen tenni sajnos kell még 2 lépés:
1. Vhova ki kell íratni az értékeket, ez lehet egy rejtett munkalap/oszlop.
A képlet:=INDEX(Lapnevek;SOR())
Húzd le a képletet jó sokat, hogy ne csak a mostani lapok de az esetleges jövőbeli lapnevek kiférjenek.Az én példámban a D1:D15-ös tartományba van a fenti képlet.
2. Ha esetleg ABC sorrendbe akarod rendezni a listádat, akkor kell 2 segédoszlop még.
E-ben a képlet ennyi:=DARABTELI(D:D;"<="&D1)
Vagy ha nem akarod hogy a listádban a "T" munkalap választható legyen, akkor ez:=HA(D1<>"T";DARABTELI(D:D;"<="&D1);HIÁNYZIK())
F-ben pedig ez:=INDEX(D:D;HOL.VAN(SOR();E:E;0))
3. A névkezelőbe kell megint egy képlet, ami egy dinamikus tartományt csinál a kapott listából. Én most a rendezett listát adom meg neki vagyis az F1-es cellát, mert onnan indul a rendezett lista.
A képlet ez esetben:=ELTOLÁS(T!$F$1;;;DARAB2(Lapnevek))
4. B1-es cellában adatérvényesítésben megadod a 3-as lépésben létrehozott nevet (nálam Lapok)
5. A1-be pedig kerül a hiperhivatkozás képlete=HIPERHIVATKOZÁS("#"&B1&"!A1";"Klikk ide")üdv
-
Mutt
senior tag
escsé: Elnézést kérek, mert lehet hogy elhamarkodtam a válaszomat.
Ha jól olvasom a képleted, akkor fájlodban a vannak elnevezett tartományok (pl. Elszámolás), ahova ugorni szeretnél. Tehát nem az Elszámolás nevű lapra akarsz ugorni.
Ez esetben ez műkődni fog:
=HYPERLINK("[Művdok2.xlsx]"&B1;"Klikk ide") -
Mutt
senior tag
A HYPERLINK-nek kell egy munkalap név és egy CELLA (pl. C26) ahova ugorhat.
Az általad használt képlet ezt hibássan állítja elő, mert a cellának a lap nevét rakja be.
Ha mindegyik lapon elég hogy az első mezőre ugorj, akkor ezt használd:
=HYPERLINK("#"&B1&"!A1";"KLIKK IDE") -
Mutt
senior tag
Szia,
Az elevator referencia táblázatodba - ahol vannak a típusok műszaki adati felsorolva - kell egy segédoszlop, amelynek a képlete csak ennyi:
=SOR()
Húzd le a képletet, hogy minden referencia sorban ott legyen az érték.Ezek után az E23 képlete:
=INDEX(Referencia!C:C;MINHA(Referencia!Q:Q;Referencia!B:B;E7;Referencia!O:O;">"&E19))ahol Referencia a lap neve ahol van az elevator táblázatod, ennek a lapnak a Q-oszlopa a fenti lépésben említett segédoszlop.
üdv
-
Mutt
senior tag
válasz
wednesday
#39417
üzenetére
Szia,
Itt van mutatott mintához a makró. A kommentek alapján tudod finomítani.
Sub Mentes()
Const urlap_helye = "Urlap" 'munkalap neve ahol van az űrlap
Const mentes_helye = "Mentes" 'munkalap neve ahova menteni kellene
Dim utolsoSor As Long, i As Long
Dim wsForras As Worksheet
Dim wsMentes As Worksheet
Set wsForras = ThisWorkbook.Sheets(urlap_helye)
Set wsMentes = ThisWorkbook.Sheets(mentes_helye)
With wsMentes
utolsoSor = .Range("A" & Rows.Count).End(xlUp).Row + 1 'megkeressük az első szabadsort a mentés lapon
For i = 17 To 35 'az űrlap 17-35 sora között nézzük a felírásokat
If Len(.Cells(i, "C")) > 0 Then
.Cells(utolsoSor, "A") = Now 'A-oszlopba rögzíjük a mentés dátumát
.Cells(utolsoSor, "B") = wsForras.Range("D7") 'B-oszlopba jön az első sorban lévő D-L egyesített cella tartalma
.Cells(utolsoSor, "C") = wsForras.Range("B" & i) 'C-oszlopba jön a B-oszlopbeli sorszám
.Cells(utolsoSor, "D") = wsForras.Range("C" & i) 'D-oszlopba a C-H tartalma
.Cells(utolsoSor, "E") = wsForras.Range("J" & i) 'E-oszlopba a J tartalma
.Cells(utolsoSor, "F") = wsForras.Range("K" & i) 'F-oszlopba a K tartalma
If .Cells(i, "C").MergeCells Then 'ha összevont cellákról van szó, akkor át kell ugornunk az összevont sorokat
i = i + .Cells(i, "C").MergeArea.Rows.Count - 1
End If
utolsoSor = utolsoSor + 1
End If
Next i
End With
Set wsForras = Nothing
Set wsMentes = Nothing
End Subüdv
-
Mutt
senior tag
válasz
marec1122
#39400
üzenetére
Szia,
A IDŐÉRTÉK("N2") részben az hogy az N2 szöveget próbálod meg átalakítani számmá, ami nem fog menni.,
Gondolom te az N2 cella értéket akarod felhasználni, ekkor csak IDŐÉRTÉK(N2) kell neked.Ha az N2-ben az idő dátummal együtt van, akkor N2-INT(N2) fogja megadni az idő értéket.
Delila FKERES-es megoldása ekkor így fog műkődni:
=FKERES(A1-INT(A1);$D$1:$E$4;2;1)üdv
-
Mutt
senior tag
válasz
wednesday
#39388
üzenetére
..az űrlapon 6 sor adat van vagy éppen 3 akkor, azokat pakolja át a mentési táblába.
Tudsz mutatni egy mintát hogyan néz ki egy többsoros űrlap nálad?
A legördülő listánál ActiveX-es elem tud segíteni. Talán ezt a megoldást https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/ tudom javasolni.
-
Mutt
senior tag
válasz
BullZeye
#39396
üzenetére
Szia,
Ha színezni akarod, akkor feltételes formáz marad a kézi színezés mellett.
Ezt lehet makróval automatizálni:
A szinez gomb alatt ez a makró:
Sub SzinezdKi()
Const minta_tartomany = "E1:E5" 'itt vannak azok a minták amik alapján a feltételes formázást létre lehet hozni
Dim rngMinta As Range, cell As Range
Dim i As Long
Set rngMinta = Range(minta_tartomany)
'a kijelölt cellákra rádobjuk a formázást
With Selection
'töröljük a lehetséges korábbi feltételes formázásokat
.FormatConditions.Delete
i = 1
For Each cell In rngMinta
'felveszünk egy új szabályt: cella tartalmát figyelve színezünk
.FormatConditions.Add Type:=xlTextString, String:=CStr(cell.Value), TextOperator:=xlContains
'a szín a minta tartományban használt szinnel legyen egyenlő
.FormatConditions(i).Interior.Color = cell.Interior.Color
i = i + 1
Next cell
End With
End SubHasználata: előbb a makróban megadod a tartományt ahol van a gyümölcs neve és a hozzá társítandó szín.
Kijelölöd a cellákat amikre rá akarod ereszteni és mehet a gombra kattintás.üdv
-
Mutt
senior tag
válasz
marec1122
#39394
üzenetére
Szia,
Az eredeti kérdésre a DÁTUMÉRTÉK és IDŐÉRTÉK összehasonlítható számmá (a dátumok sorszámok: 1900. január 1-e óta eltelt napok száma, az időértékek pedig 0-1 közötti törtszámok)
Az alábbi képlet megmondja, hogy az általad megadott időpont elmúlt-e vagy sem:
=HA(DÁTUMÉRTÉK("2018.09.03")+IDŐÉRTÉK("6:12:49")<MOST();"elmúlt";"még nem múlt el")...hány időpont van 6-8 óráig, 8-12 óráig és 12 óra után...
A DARABHATÖBB (COUNTIFS) függvény használható erre a problémára; a kritériumok között tudunk használni nagyobb, kisebb, kisebb és egyenlő stb. operátorokat.
Vhogy így:=DARABHATÖBB(adatsor;">="&kezdődátum;adatsor;"<"&végdátum)Pl. A lenti képlet megadja, hogy az A-oszlopban hány olyan időérték van ami 0-6 óra között voltszeptember 3-án.
=DARABHATÖBB(A:A;">="&DÁTUMÉRTÉK("2018.09.03")+IDŐ(0;0;1);A:A;"<="&DÁTUMÉRTÉK("2018.09.03")+IDŐ(5;59;59))Az IDŐ fuggvényben megadhatod másodperc pontosan, hogy milyen érték legyen.
üdv
-
#39382
Mutt
senior tag
Csabi890331
#39379
Mutt
senior tag
válasz
Csabi890331
#39379
üzenetére
Szia,
A Worksheet Change eseményét kell használnod.
VBA email küldésre itt vannak megoldások: https://www.rondebruin.nl/win/s1/outlook/mail.htmHa van konkrétumod, akkor az itteni csapat tud segíteni.
üdv
-
Mutt
senior tag
válasz
wednesday
#39378
üzenetére
Szia,
A leírásod alapján vmi ilyen struktúrában van az űrlapod.
Kitettem mellé egy Mentés nevezetű gombot, amihez rendelheted ezt a makrót:
Sub Mentes()
Const urlap_helye = "Urlap" 'munkalap neve ahol van az űrlap
Const mentes_helye = "Mentes" 'munkalap neve ahova menteni kellene
Dim utolsoSor As Long
Dim wsForras As Worksheet
Dim wsMentes As Worksheet
Set wsForras = ThisWorkbook.Sheets(urlap_helye)
Set wsMentes = ThisWorkbook.Sheets(mentes_helye)
With wsMentes
utolsoSor = .Range("A" & Rows.Count).End(xlUp).Row + 1 'megkeressük az első szabadsort a mentés lapon
.Cells(utolsoSor, "A") = Now 'A-oszlopba rögzíjük a mentés dátumát
.Cells(utolsoSor, "B") = wsForras.Range("D1") 'B-oszlopba jön az első sorban lévő D-L egyesített cella tartalma
.Cells(utolsoSor, "C") = wsForras.Range("A2") 'C-oszlopba az A2-es cella tartalma
.Cells(utolsoSor, "D") = wsForras.Range("C2") 'D-oszlopba a C-H tartalma
.Cells(utolsoSor, "E") = wsForras.Range("J2") 'E-oszlopba a J2 tartalma
.Cells(utolsoSor, "F") = wsForras.Range("K2") 'F-oszlopba a K2 tartalma
End With
End SubRemélem a bent lévő kommentek alapján át tudod írni/pontosítani, hogy honnan és hova mentsen a makró.
üdv
-
Mutt
senior tag
válasz
Anulu79
#39334
üzenetére
Szia,
A 38943-as hozzászólásban adtam egy képletet, amellyel lehet egy cella színét (kivétel ha feltételes formázással lett színezve) számoláshoz felhasználni, de körülményes és mivel a színezéskor az Excel nem számolja újra az értékeket nem megbízható.
Lehet egy saját függvényt makróval létrehozni ehhez a feladatohoz (a fent linkelt hozzászólásban van is egy), de annak is vannak problémái.
Jobb megoldás lenne, hogy az említett 7 cella tartalmát/színét megvátloztató logika alapján készülne el a 8-ik cellába egy olyan számítás ami jó lenne.
Ehhez a kérdés: hogyan kerül megállapításra a 7 cella színe? van ott függvény ami ezt csinálja vagy a felhasználó állítja be más cellák alapján?üdv
-
Mutt
senior tag
Szia,
A SZÖVEG.KERES és SZÖVEG.TALÁL függvények tudnak szövegben keresni, de ehhez az kell hogy a szöveg fájl be legyen töltve egy munkalapra.
Azonban ha Excel 2010 vagy frissebbed van, akkor a Power Query-vel lehet listát csinálni külső segédprogram ("dir" parancs) nélkül egy mappában található fájlokról és ott használni a Text.PositionOf függvényt.
üdv
-
Mutt
senior tag
Szia,
Hívatkoz a másik lapon a cellákra így:
=INDEX(Munka1!$A:$AZ;;)Ahol Munka1 az első munkalapod, és $A:$AZ az a tartomány ahol van adatod a lapon.
Alapból az INDEX függvénynek meg kellene adni, hogy melyik sorban (és melyik oszlopban) lévő adatot akarjuk kiíratni, de lehetünk lusták is és ekkor egy érdekes dolgot kapunk, aminek az "implicit intersection" (magyarul talán az egyértelmű metszet?) az angol elnevezése. Az Excel megpróbálja visszaadni a másik tartományban annak a sornak és oszlopnak az értékét, ahol éppen vagyunk.
üdv
-
Mutt
senior tag
válasz
kopogo
#39310
üzenetére
Szia,
Kereteket kell megfelelően megrajzolnod.
Indulj ki abból hogy van 12 oszlopod és 6 sorod.
Az első három sorban csak minden negyedik cellának kell jobb oldalt keret.
A negyedik sorban a harmadik, a nyolcadiknak és az utolsónak van jobb oldalt kerete.
A következő sorban a 4, 10 és 12-nek van jobb kerete.
Az utolsóban pedig a 2, 8, 12-esnek.
Adj alsó és felső vonalat mindegyik cellához.
Adj minden első cellának bal oldara egy keretet.Ha nem akarod látni a rácsvonalakat, akkor vagy kikapcsolod őket (Nézet -> Rácsvonalak) vagy egy fehér színnel kitöltöd a táblázatot.
Végül pedig gondolom ide írni akarnak majd vmit és jó lenne, hogyha szépen el lenne rendezve a szöveg.
Erre is több megoldás van, jelöld ki az első sor 4 celláját és Cellaformázás
1. Igazítás -> Vízszintesen -> A kijelölés közepére
2. Igazítás részben Cellák egyesítésével.A cella egyesítés másolásnál/makróknál tud gondot okozni majd a későbbiekben.
Youtube-on nézegess videókat róla.
üdv
-
Mutt
senior tag
Szia,
1.
2 lépéses megoldás:dir 1*.txt /s /b >fajllista.txt
dir 2*.txt /s /b >>fajllista.txt2.
Batch fájlba teszed a lépéseket és csak a batch fájlt futtatod.3.
Unix/Linux rendszereken használt grep programot telepíted. Része a UnxUtils csomagnak.dir *.txt /s/b c:\temp|grep "^[1-2]" > fajllista.txtüdv
-
#39302
Mutt
senior tag
Sziszilabi
#39294
Mutt
senior tag
válasz
Sziszilabi
#39294
üzenetére
Szia,
A termék beérkezési dátuma alapján van sorbarendezve, s tetszőleges webáruház utolsó 5 beérkezett rendelésének átfutási idejéből szeretnék átlagot számolni.
Angol Excel-ben egy megoldás:
1. Egy segédoszlopban megjelöljük, hogy melyik az adott termékhez tartozó utolsó 5 beérkezés.
ACOUNTIF(A:A;A2)képlet megadja, hogy az A2-es cellában lévő termék hányszor szerepel az A-oszlopban, pl. 231.
ACOUNTIF($A$2:A2;A2)képlet alíg tér el az előbbitől, de itt a tartomány folyamatosan bővül és megmondja hogy az aktuális sorig bezárólag a termék hányszor szerepelt eddig.Ha a két értéket összehasonlítjuk, akkor megtudjuk állapítani hogy honnan kell kezdenünk nézni az átlaghoz használandó dátumot. A
COUNTIF(A:A;A2)-COUNTIF($A$2:A2;A2)<=5igaz eredményt fog visszaadni minden termék utolsó 5 beérkezésekor, tehát ezen sorokban van nekünk a felhasználható adatunk. A duplanegatívjel előtte nem fontos, ez csak az IGAZ/HAMIS-ból 1/0-t fog csinálni.Az átlagszámítást ezek után egy Pivot/Kimutatás segítségével tudod megtenni.
Én egy 20 ezer soros fájlon teszteltem és ott 1 percig számolja a segédoszlopot, ha ez túl lassú akkor Power Query-t tudom javasolni Excel 2010 felett.
üdv
-
Mutt
senior tag
Szia,
Addinként tesztelted vagy egy sima fájlba tetted még a kódot?
Az űrlap új fájlt nyit, esetleg bezárja az aktuálisat?A kód a fájl bezárásakor leszedi az új menűt (DeleteControls program). Ami addin esetén nem okoz gondot, mivel az az összes Excel ablak bezárásakor fog csak lefutni.
üdv
-
Mutt
senior tag
Szia,
1. Makrómentessen úgy tudod megcsinálni, hogy a Windows-ban a regionális beállításoknál a lista elválasztó jelnek beállítod a pipe karaktert. Ennek szerintem több a hátránya, mint az előnye, de ha szinte csak ilyen fájlokkal dolgozol, akkor előnyös lesz.
2. Próbáld ki ezt a makrómat. Nekem egy addin-ban van, így mindig elindul és bármelyik fájlon használható.
A region részben tudod megadni, hogy mit mentsen ki.
A qualifiers részben a separator-ban vannak a lehetséges elválasztó karakterek (válaszd a pipe-ot ).
A text qualifier-ben kérheted hogy a mezők mindig macskakörmök/aposztrófok között legyenek-e.
Alul a Trim megpróbálja a felesleges szóközöket levágni, a Value pedig a felesleges vezető nullákat.
Az Unicode pedig hasznos, ha helyes ékezetes szövegeket tartalmazó fájlt akarsz kapni.Ha sűrűn használod a programot eltérő beállításokkal, akkor a defaults részben el tudod menteni azokat.
üdv
-
Mutt
senior tag
Szia,
A ribbon módosítást nem vágom, de nekem az alábbi kód Excel 2010 óta jól megy saját készítésű addin-ban:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' START ThisWorkbook Code Module
' Created By Chip Pearson, chip@cpearson.com
' Sample code for Creating An Add-In at http://www.cpearson.com/Excel/CreateAddIn.aspx
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Private Const C_TAG = "Makro" ' C_TAG should be a string unique to this add-in.
Private Const C_TOOLS_MENU_ID As Long = 30007&
Private Sub Workbook_Open()
'''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_Open
' Create a submenu on the Tools menu. The
' submenu has two controls on it.
'''''''''''''''''''''''''''''''''''''''''''''''
Dim ToolsMenu As Office.CommandBarControl
Dim ToolsMenuItem As Office.CommandBarControl
Dim ToolsMenuControl As Office.CommandBarControl
'''''''''''''''''''''''''''''''''''''''''''''''
' First delete any of our controls that
' may not have been properly deleted previously.
'''''''''''''''''''''''''''''''''''''''''''''''
DeleteControls
''''''''''''''''''''''''''''''''''''''''''''''
' Get a reference to the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
Set ToolsMenu = Application.CommandBars.FindControl(ID:=C_TOOLS_MENU_ID)
If ToolsMenu Is Nothing Then
MsgBox "Unable to access Tools menu.", vbOKOnly
Exit Sub
End If
''''''''''''''''''''''''''''''''''''''''''''''
' Create a item on the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
' Set ToolsMenuItem = ToolsMenu.Controls.Add(Type:=msoControlPopup, temporary:=True)
' If ToolsMenuItem Is Nothing Then
' MsgBox "Unable to add item to the Tools menu.", vbOKOnly
' Exit Sub
' End If
'
' With ToolsMenuItem
' .Caption = "&Menu Item"
' .BeginGroup = True
' .Tag = C_TAG
' End With
''''''''''''''''''''''''''''''''''''''''''''''
' Create the first control on the new item
' in the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
' Set ToolsMenuControl = ToolsMenuItem.Controls.Add(Type:=msoControlButton, temporary:=True)
Set ToolsMenuControl = ToolsMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
If ToolsMenuControl Is Nothing Then
MsgBox "Unable to add item to Tools menu item.", vbOKOnly
Exit Sub
End If
With ToolsMenuControl
''''''''''''''''''''''''''''''''''''
' Set the display caption and the
' procedure to run when clicked.
''''''''''''''''''''''''''''''''''''
.Caption = "Ékezetek" 'idejön a saját elnevezésed
.OnAction = "'" & ThisWorkbook.Name & "'!Ekezetek" 'ez pedig a saját kódod
.Tag = C_TAG
End With
''''''''''''''''''''''''''''''''''''''''''''''
' Create another control on the new item
' in the Tools menu.
''''''''''''''''''''''''''''''''''''''''''''''
Set ToolsMenuControl = ToolsMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
If ToolsMenuControl Is Nothing Then
MsgBox "Unable to add item to Tools menu item.", vbOKOnly
Exit Sub
End If
With ToolsMenuControl
''''''''''''''''''''''''''''''''''''
' Set the display caption and the
' procedure to run when clicked.
''''''''''''''''''''''''''''''''''''
.Caption = "SQL/BO Converter"
.OnAction = "'" & ThisWorkbook.Name & "'!Converter"
.Tag = C_TAG
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_BeforeClose
' Before closing the add-in, clean up our controls.
''''''''''''''''''''''''''''''''''''''''''''''''''''
DeleteControls
End Sub
Private Sub DeleteControls()
''''''''''''''''''''''''''''''''''''
' Delete controls whose Tag is
' equal to C_TAG.
''''''''''''''''''''''''''''''''''''
Dim Ctrl As Office.CommandBarControl
On Error Resume Next
Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
Do Until Ctrl Is Nothing
Ctrl.Delete
Set Ctrl = Application.CommandBars.FindControl(Tag:=C_TAG)
Loop
End Subüdv
-
Mutt
senior tag
Sziasztok,
Az eddig megszokott abszolút és relatív cellahivatkozások mellé jön majd egy új is, ami az új dinamikus tartományokra való hívatkozást fogja tudni kezelni.
=I2#Ezzel a képlettel az I2-es cellában lévő új dinamikus tartomány függvény (pl. FILTER, SORT, SEQUENCE etc.) eredményére lehet majd hivatkozni. pl. a
=DARAB2(I2#)megszámolja hány elemet tartalmaz az I2-es cellától induló tartomány anélkül hogy tudnánk hol ér véget az.Mr. Excel-től most ingyen letölthető a 60 oldalas füzet, amelyben bemutatja az új dinamikus függvényeket.
A rossz hír, hogy az újdonságok nagyon lassan (több év után) fognak eljutni az asztali Excel verziókba, míg az online előfizetéses változatban hamarabb fognak megjelenni.
üdv
-
#39223
Mutt
senior tag
dellfanboy
#39219
Mutt
senior tag
válasz
dellfanboy
#39219
üzenetére
Szia,
FFeri50 által javasolt megoldások mellett
- Kimutatás vagy
- Power Query vagy
- tömb-függvény is megoldás lehet.Az új Office 365-ben a UNIQUE képletet lesz majd a legegyszerűbb használni.
üdv
-
Mutt
senior tag
válasz
csferke
#39121
üzenetére
Szia,
..PK2 munkalap E oszlopában a "b" betű..
1. Ha a cella csak "b"-t tartalmaz (kis és nagybetű számít), akkor
=IF(SUM((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*(Pk2'!E$5:E500="b"))=0;"";SUM((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*(Pk2'!E$5:E500="b")))Ezt Ctrl-Shift-Enter-el kell majd bevinni.
SUMPRODUCT esetén nem kell CSE:
=SUMPRODUCT((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*('Pk2'!E$5:E500="b"))2. Ha a cella tartalmazza a "b" betűt (pl. abrakadabra), akkor
=IF(SUM((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*ISNUMBER(FIND("b";'Pk2'!E$5:E500)))=0;"";SUM((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*ISNUMBER(FIND("b";'Pk2'!E$5:E500))))SUMPRODUCT-al:
=SUMPRODUCT((MONTH('Pk2'!A$5:A500)=D74*1)*('Pk2'!D$5:D500)*ISNUMBER(FIND("b";'Pk2'!E$5:E500)))3. Ha a cella tartalmazza a "b" vagy "B" betűt, akkor a FIND-ot SEARCH-re kell cserélni.
Végül pedig a cella számformátumával el tudod rejteni a nulla értékét, így nem kell kétszer lefuttatni a tömbképletet. pl. a
# ##0,00;-# ##0,00;;@számformátum, 2 tizedes értékkel megjeleníti a számokat, de a nullát üres cellának fogja mutatni.üdv
-
Mutt
senior tag
válasz
lumpy92
#39107
üzenetére
Szia,
Van már egy makród ami többek között számolja az átlagot? Ha igen, akkor tudod mutatni azt a részletét ami lefekteti a gépet?
Ha még nincs makród, akkor nem az átlag számítás lesz a probléma, hanem más képlet.Tippek:
1. Nézd meg hogy melyik az utolsó használt cellád munkalapon, F5-el nyisd meg az ugrás ablakot, Speciális gombbal és ott válaszd az utolsó cellát. Ha nem a jó sorba ugrik, akkor tud problémát okozni.
2. Sok adat esetén segíthet a Power Query is.üdv
-
Mutt
senior tag
Szia,
Feltételes formázásnál "A formázandó cellák kijelölése képlettel" szabályt választod ki és oda jön az általad is írt képlet és a kívánt formázás.
Ami a furcsaság a feltételes formázásnál, hogy a kijelölés képletében ha nincsenek rögzítve a sor és oszlop azonosítók dollár jellel, akkor azokat a rendszer minden sornál/oszlopnál aktualizálni fogja.
A példádban az első sorban használt =B1>0 képlet a második sorban már =B2>0 lesz és így tovább, de a feltételes formázás képlet szerkesztőben akármelyik sorban állsz mindig =B1>0 lesz a képleted.
Javaslom, hogy próbáld ki mit kapsz ha rögzíted a sor és/vagy oszlop azonosítókat, vagyis írd át a képleted előbb erre: =B$1>0, majd erre =$B1>0 és végül erre =$B$1>0
üdv
-
Mutt
senior tag
válasz
RAiN91
#39101
üzenetére
Szia,
Ezt próbáld ki:
- a javított idő oszlopban van a percre kerekített érték, képlete:
=TÖBBSZ.KEREKÍT(A2;1/1440)
- a különbség oszlop A és B közötti eltérés, abszolút értékkel, képlete:=ABS(B2-A2)
- a legkisebb eltérés megnézi, hogy az adott időpontnál melyik a legkisebb eltérés, képlete:=MINHA(C:C;B:B;B2)
- a végső idő pedig ezek alapján próbálja kitalálni hogy az eredeti vagy a kerekített idő kell, képlete:=HA(C2=D2;B2;A2)üdv
-
Mutt
senior tag
Szia,
Legördülő menüt az Adatok -> Érvényesítés opcióval tudsz csinálni.
Megjelenítésben az INDEX és HOL.VAN páros kell. A HOL.VAN megmondja, hogy a kiválasztott elem hányadik sorban van az eredeti táblázatban és az INDEX pedig egy tartományból a megadott sor és oszlop elemét tudja visszaadni.üdv
-
Mutt
senior tag
-
Mutt
senior tag
Szia,
Ha a felhasználó hozzáfér egy munkalaphoz, akkor már nehezen lehet leszabályozni hogy melyik sorhoz férjen hozzá. Talán az lehetne egy megoldás, hogy mindig az első 2 sorba írnád bele a felhasználóra tartozó adatokat egy másik helyről és a Worksheet_SelectionChange eseménnyel próbálnád meg leblokkolni hogy ne mászkáljon el.
Én inkább egy másik megoldást javaslok, ami kicsit bolondbiztosabb és bonyolultabb is.
Userform-ot, amin keresztül a beléptetjük a felhasználót és megjelenítjük az adott sor adatait, amit ott tud módosítani és visszamenteni az eredeti lapra.
Az előnye ennek, hogy mindegyik lapot el tudod rejteni (akár VeryHidden-ként), így középszintű Excel felhasználóktól is megtudod védeni az adatokat.
Egy csontvázat mutatok be, ami alapján el tudsz indulni.
1. A fájlodban van két munkalap, az egyiken vannak az adatok, a másikon pedig a felhasználói azonosítók és jelszavak. Ezen lapokat nyugodtan elrejtheted.
2. VBA-ban szúrj be egy formot és kezd el rá dobálni az alábbi vezérlőket:
- egy combobox a felhasználói neveknek (ezzel egy legördülő listát lehet csinálni)
- egy textbox kell a jelszóhoz,
- 2 gomb a belépéshez és kilépéshez a programból.Lehet még felrakni labeleket, hogy tudjuk mi mire való, illetve én tettem fel egy pluszat is ahova ki lehet írni a sikeres belépés vagy a hibás felhasználó üzeneteket.
3. Legyen pár globális változónk, hogy tudjuk rájuk minden helyen gyorsan hivatkozni.
Option Explicit
Dim wsUsers As Worksheet, wsData As Worksheet, lastrowUsers As Long, lastrowData As Long
Dim activeRow As Long 'control which row should be displayedA wsUsers és WsData egy változó lesz azon munkalapokhoz ahol vannak az adatok és a felhasználók, illetve megjegyezzük hogy ezen lapokon hol vannak az utolsó sorok.
Az activeRow pedig mindig mutatni fog arra a sorra, ami a belépett felhasználóhoz van rendelve.4. A Userform indulásával feltöltjük a username comboboxot, aminek én a cbUserName azonosítót adtam.
Beállítjuk a többi változót.Private Sub UserForm_Initialize()
Dim i As Long
Const UserSheet = "Users" 'worksheet name with user ids, passwords etc
Const DataSheet = "Data" 'worksheet name with actual data
Set wsData = Worksheets(DataSheet) 'assign data sheet to a variable
Set wsUsers = Worksheets(UserSheet) 'assign user id sheet to a variable
lastrowData = wsData.Range("A" & Rows.Count).End(xlUp).Row 'find the last row on data sheet
lastrowUsers = wsUsers.Range("A" & Rows.Count).End(xlUp).Row 'find the last row on user sheet
'load valid users to username controlbox
For i = 2 To lastrowUsers
Me.cbUserName.AddItem wsUsers.Range("A" & i)
Next i
End Sub5. Ha van felhasználói név és jelszó megadva, akkor lehet csak belépni. Ehhez én figyelem ennek a két doboznak (cbUserName -ről már írtam, a jelszó pedig a txPassword textbox-ban van) a change eseményét nézem, de lehetne az AfterUpdate-t is. A lényeg, hogy a form indulásakor a Login nevű gomb inaktív (ez az alapértelemezett értéke) és csak akkor lesz kattintható ha van név és jelszó megadva.
Private Sub cbUserName_Change()
'if both username and password is filled then enable login button
If cbUserName <> "" And txPassword <> "*" Then
btLogin.Enabled = True
lbComment.Visible = False
End If
End Sub
Private Sub txPassword_Change()
'if both username and password is filled then enable login button
If cbUserName <> "" And txPassword <> "*" Then
btLogin.Enabled = True
lbComment.Visible = False
End If
End Sub6. Ha kattintanak a Login gombra, akkor megnézzük hogy a megadott felhasználó és jelszópáros egyezik-e a menttettel. Ha igen, akkor
- van egy hely (LoginComment) ahova kiírjuk hogy sikeres a belépés
- megkeressük hogy a felhasználóhoz melyik sorban van adat rendelve és az activeRow változóba ezt beállítjuk,
- láthatóvá tesszük azokat a vezérlőket, amelyekben megjelenítjük a felhasználóhoz tartozó adatokat .Ehhez a formra felvittem még pár mezőt, így néz ki a teljes form:
Hibás belépésnél lényegében csak kiírjuk a hibát és töröljük a megadott jelszót.
Private Sub btLogin_Click()
Dim cell As Range
Dim i As Long
For Each cell In wsUsers.Range("A2:A" & lastrowUsers)
'find the selected username on user id sheet
If UCase(cell) = UCase(cbUserName) Then
'if password matches then print out a notification and display the data
If cell.Offset(, 1) = txPassword Then
lbComment = "Successful login"
lbComment.Visible = True
txPassword = "*" 'password is visible on the form, so after a valid login hide it
btSave.Visible = True 'make visible the save button
'find the correct line for the selected user on data sheet
If UCase(cbUserName) = "ADMIN" Then
activeRow = 2
spinRecord.Visible = True
spinRecord.Min = activeRow
spinRecord.Max = lastrowData
Else
For i = 2 To lastrowData
If UCase(wsData.Range("A" & i)) = UCase(cbUserName) Then
activeRow = i
End If
Next i
End If
Call DisplayData
Else
'wrong password was given
lbComment = "Invalid username or password"
lbComment.Visible = True
txPassword = "*"
End If
End If
Next cell
End Sub7. A cancel (kilépés) gombnak egyszerű az eseménye, bezárja a formot.
Private Sub btCancel_Click()
Unload Me
End Sub8. A felhasználóhoz rendelt adatok megjelenítésére csináltam egy külön szubrutint, DisplayData névvel, Ez most nagyon minimalista.
Sub DisplayData()
With wsData
If Len(.Cells(activeRow, 2)) > 0 Then
txRecord1 = .Cells(activeRow, 2)
Else
txRecord1 = ""
End If
If Len(.Cells(activeRow, 3)) > 0 Then
txRecord2 = .Cells(activeRow, 3)
Else
txRecord2 = ""
End If
If Len(.Cells(activeRow, 4)) > 0 Then
txRecord3 = .Cells(activeRow, 4)
Else
txRecord3 = ""
End If
End With
End Sub9. Ha a felhasználó átírja a formon az adatot, akkor a Save gombbal visszaírjuk a munkalapra.
Private Sub btSave_Click()
With wsData
.Cells(activeRow, 2) = txRecord1
.Cells(activeRow, 3) = txRecord2
.Cells(activeRow, 4) = txRecord3
End With
End Sub10. Végül az ADMIN-ra kell egy kicsit rágyúrni, mivel ő látja az összes adatot.
Neki az a megoldás, hogy van egy görgető sáv (én most a spinbutton vezérlőt használom, a spinRecord nevet adtam neki), amin tud lépkedni az egyes mezők között. Ezt a görgető sávot a belépéskor teszem láthatóvá neki és beállítom hogy a 2-es sortól (1-esben a fejléc van), csak az utolsóig mehet. A léptetést a vezérlő change eseménye oldja meg, mert módosítja az activeRow értéket és egyből meghívja a DisplayData-t.Private Sub spinRecord_Change()
activeRow = spinRecord.Value
Call DisplayData
End SubA végeredmény így néz ki Admin-al belépve:
A UserForm-nak nagy előnye, hogy áttekinthető formában tálalni a felhasználó felé azokat az adatokat amiket te választasz ki, illetve ha sok adatot kell bekérni akkor ott is tud segíteni, mert mentés előtt tudsz validálást csinálni.
Ha akarod akkor automatikusan indíthatóvá teheted.
Private Sub Workbook_Open()
MainForm.Show
End Subüdv
-
Mutt
senior tag
Sziasztok,
Érdemes megnézni, pár Excel MVP háziversenyét a Youtube-on.
Mr. Excel inkább a tartalomra (ami nagyon érdekes) ment rá, mint a látványra/megoldásra. A lányok vizuálisan szép megoldásokat adtak, Mike Girvin képletei még mindig ütősek, Oz szerintem gyenge volt. Nálam Jon Acampor volt a legjobb, mert egy interaktív dashboard alapjait mutatta be.
üdv
-
#39083
Mutt
senior tag
szricsi_0917
#39072
Mutt
senior tag
válasz
szricsi_0917
#39072
üzenetére
Szia,
Nézd meg előbb, hogy Power Query-vel meg tudod-e csinálni a kért feladatot.
Javaslom, hogy a Power Query segítségével "fűzd" össze a sok fájlt és arra már lehet FKERES/VLOOKUP.
üdv
-
#39081
Mutt
senior tag
dellfanboy
#39080
Mutt
senior tag
válasz
dellfanboy
#39080
üzenetére
Szia,
Az ikonkészlettel nem fog menni, ezt a kerülő megoldást javaslom.
1. Keress egy olyan betűkészletet, amelyben van a neked tetsző pipa és kereszt. pl. a Wingdings betűtípusokban vannak ilyenek.
Én a "HoloLens MDL2 Assets" betűtípust használom, mert abban egyszerűbbek ezek az ikonok.
Egy cellába rakd be a két ikont (Symbol -> Insert), hogy onnan ki tudd másolni, mert sokszor olyan karakterhez vannak ezek rendelve amelyeket billentyűzetről nehéz bevinni.
2. Az adatsorod mellé kell egy segédoszlop, ebben a képlet ennyi:
=IF(vmiadat=0;pipaikon;keresztikon)
3. Most már csak színformázást kell adnod ehhez a tartományhoz.
A példában zöld illetve piros hátteret és fehér betűszínt választottam.
üdv
-
Mutt
senior tag
válasz
Agostino
#39064
üzenetére
Szia,
Ha a képen látható módon rendezed az adatodat, akkor megkapod a kívánt eredményt.
Lépések:
1. Kijelölöd az adattartományod, majd grafikon beszúrásakor kiválasztod a halmozott oszlop diagrammot.
2. Excel próbál okos lenni és 3 oszlopot tesz egymás mellé, de te kattints a sorok/oszlopok felcserélésére.üdv
-
Mutt
senior tag
válasz
ccs2929
#39059
üzenetére
Szia,
"...sorokat összehasonlítsunk legalább 2 oszlop figyelembe vételével és az azonosokat valamilyen módon kiemeljük, pld. színnel..""
Ilyen esetre, használható a DARABHATÖBB (COUNTIFS) függvény, amely megszámolja hogy a feltételként megadott érték a kiválasztott oszlopban hányszor szerepel.
pl. A-oszlopban név, G-oszlopban adószám, és van fejléced, akkor
=DARABHATÖBB(A:A;A2;G:G;G2) megmondja hogy adott névvel és adószámmal hány találatod van a második sorban lévő partnerre, ezt csak le kell húznod és megkapod az eredményt az összes cégre.Feltételes formázásban is lehet használni.
üdv
ps. A névre való keresés nem olyan biztos megoldás, mert lehet hogy az egyik rendszerben használtak ékezeteket vagy rovidítéseket és a másikban nem. Az adószámnál pedig bonyodalmat az okozhat, ha EU-s változatban vagy magyar formátumban került felvitelre az adószám.
-
#39065
Mutt
senior tag
thomas50.000
#39062
Mutt
senior tag
válasz
thomas50.000
#39062
üzenetére
Szia,
"...whatsmycolor függvény sajnos nem frissíti önmagát...."
A tippem azt lett volna, hogy elég egy Application.Volatile parancsot tenni az UDF-be, de az Excel-ben a cella szinének megváltozásakor nincs esemény indítás, azért ez sem megoldás.
A legjobb megoldás, hogy azon munkalap selectionchange eseményéhez, ahol használod a függvényt rendelsz egy Application.CalculateFull parancsot.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End SubEnnek a hátránya ugyebár, hogy MINDEN értéket újraszámol a munkalapon ami lassabbá teszi a műkődést.
üdv
-
Mutt
senior tag
Szia,
A gondot az okozza, hogy ugyanazok az azonosítók és értékek más nappal is bekerülnek a táblába, viszont a jelenlegi képletet csak a legkorábbi nap érdekli.
pl. a 21-es sorban a 4455-ös azonosítóhoz az elvégezve dátum augusztus 4, miközben a 2-es sorban van már egy elvégezve augusztus 2-vel. A MIN függvény augusztus 2-át fog vissza adni, és mivel a 4-es sorban meg van a párja ugyanarról a napról, így minden további eset amikor a 4455-ös azonosító fel van sorolva már duplikáció lesz.
A feladatodat érzésem szerint bővíteni kell és téged azok az esetek érdekelnek, amikor:
1. ugyanazon a napon van az azonosítónál ELVÉGEZVE és LEHETŐSÉG és
2. csak azokat a sorokat akarod megjelölni, ahol több lehetőség van és azok napja egyezik az elvégezve napjával.A megoldás, hogy nem keressük meg a legkorábbi napot, hanem megszámoljuk hogy az adott napon hányszor van ELVÉGEZVE és LEHETŐSÉG:
=HA(HA(DARABHATÖBB($E:$E;$E2;$F:$F;"elvégezve";$N:$N;$N2)*DARABHATÖBB($E:$E;$E2;$F:$F;"lehetőség*";$N:$N;$N2);DARABHATÖBB($E$2:$E2;$E2;$F$2:$F2;"lehetőség*";$N$2:$N2;$N2)>1;HAMIS);"x";"")Ennek a megoldásnak a számodra még annyi előnye van, hogy a nap oszlopnak nem kell innenől dátumnak lennie, visszarakhatod a korábbi állapotába.
üdv
-
Mutt
senior tag
Szia,
Megis adtad a problémá indokát: a szöveg függvény eredménye szöveg lesz, amin a MINHA függvény szépen "elhasal" (0-t ad vissza, hiszen a betűknek nincs minimuma).
A gyors megoldás, hogy akkor kell egy segédoszlop, amiben a dátumnak látszó szöveget dátumra alakítod vissza és azon már menni fog. =DÁTUMÉRTÉK(N2) kell neked.
A Kimutatásban a Mező csoportosítása opciót nézd meg, mert az jobb megoldás mint a szöveggé alakítás.
üdv
-
Mutt
senior tag
Szia,
Ellenőrízd le a képletet, mert nálam ezen adatsorokra is jónak tűnik.
Q12-es cellában ez a képlet:
=HA(HA(MINHA($N:$N;$E:$E;$E12;$F:$F;"elvégezve")=MINHA($N:$N;$E:$E;$E12;$F:$F;"lehetőség*");DARABHATÖBB($E$2:$E12;$E12;$F$2:$F12;"lehetőség*")>1;HAMIS);"x";"")Az O-oszlopra nincs ezen képlethez szükséged.
üdv
-
Mutt
senior tag
Szia,
1.
Lehet egy lépésközöd, ami alapján szeretnéd szétszedni a két szám közötti tartományt.
Ez esetben a = induló szám + lépésköz * lépésszám függvény -el tudsz számolni egy köztes értéket.pl. a harmadik értéked = 10 + 0,1 *3 = 10,3
A lépészámban a =SOROK(fixtartomány:futótartomány) tud segíteni.
pl. a = SOROK($C$2:$C4) 3-at ad eredményül.
2.
Ha mindig csak adott számú köztes elemet akarsz (pl. 10 egyenlő részre osztani),
akkor a lépésközöd = (végső - induló)/lépésszám
A többi lépés ugyanaz mint előbb.üdv
-
Mutt
senior tag
válasz
csiben1
#39004
üzenetére
Szia,
Részlet a súgóból:
"Akkor használjon százalékértéket, ha minden értéket egymáshoz arányítva kíván megjeleníteni, mert a százalékérték alkalmazásakor az értékek eloszlása egymással arányos."Vagyis nem csak a cella értékét, hanem a többihez képest az elhelyezkedését is figyelmbe veszi.
Az alábbi képletet használja: = minimum + százalék(maximum-minimum)Az alábbi képernyő talán jobban elmagyarázza ezt:
Javasolt kerülni a százalékos beállítást. Tisztán a cella értéke a "szám" beállítással lehet szinezni.
üdv.
-
Mutt
senior tag
Ezzel menni fog segédoszlop nélkül:
=HA(HA(MINHA($N:$N;$E:$E;$E2;$F:$F;"elvégezve")=MINHA($N:$N;$E:$E;$E2;$F:$F;"lehetőség*");DARABHATÖBB($E$2:$E2;$E2;$F$2:$F2;"lehetőség*")>1;HAMIS);"x";"")Kérdés:
A mintádban a P12-es sorban van X, pedig az elvégezve és a lehetőség napja nem azonos.
A napot nem is kell akkor figyelni? -
Mutt
senior tag
Szia,
Arra lenne szükségem, ha egy "azonosítóból" több is van és ... a kritérium oszlopban az „elvégezve” szó, akkor ... csak egynél jelölje meg az utolsó oszlopban a sort mondjuk egy x-el. És csak azok közül, ahol a „lehetőség 1-6” megnevezés van. A „nem érdekes”-t ne figyelje.Illetve még azt is figyelni kellene, hogy azonos napon történjen.
A
=MINHA($N:$N;$E:$E;$E2;$F:$F;"elvégezve")képlet megmondja, hogy egy azonosítónál melyik napon került beírásra az "elvégezve" szó.A
=MINHA($N:$N;$E:$E;$E2;$F:$F;"lehetőség*")képlet pedig megadja a napot az azonosítóhozHa ez a kettő érték azonos, akkor már csak a duplikációt kell megjelőlni. Mivel csak 1x szeretnéd az azonosítónál a jelőlést, a
DARABTELI($E$2:$E2;$E2)=1képlettel megkeressük az első előfordulását az azonosítónak és oda teszünk egy x-et.A fenti három képlet egybe gyúrva és O2-es celléba írva:
=HA(HA(MINHA($N:$N;$E:$E;$E2;$F:$F;"elvégezve")=MINHA($N:$N;$E:$E;$E2;$F:$F;"lehetőség*");DARABTELI($E$2:$E2;$E2)=1;HAMIS);"x";"")
Ps. Ha az azonosítónál nincs se elvégezve, se lehetőség akkor is egyezés lesz. Ha ilyen eseted van, akkor
még egy plusz ellenőrzést kell betennünk ÉS függvénybe csomagolva:MINHA($N:$N;$E:$E;$E2;$F:$F;"elvégezve")>0 -
Mutt
senior tag
Folytatás:
13. Most már csak meg kell szabadulnunk azoktól a soroktól ahol az érték oszlopban az azonosító látható, megint az attributum oszlop segít nekünk. A ".1"-re végződő attributomok nem érdekelnek minket.
Attributom oszlopon Szövegszűrők -> Nem erre végződik opcióval tudjuk elérni ezt.
14. Törölhető ezek után az attributum oszlop, jobb klikk eltávolítás.
15. A szebb végeredményért rendezzük sorba a listát előbb azonosító, majd dátum szerint. Azonosító oszlop kijelöl, majd Kezdőlap -> Rendezés -> Növekvő. Ugyanezt csináljuk meg Dátummal.
16. Egy kimutatás kell már csak készíteni. Azonosító oszlop kijelöl Átalakítás -> Forgatott oszlop
A beállítások ezek legyenek:
17. Az eredmény Power Query-ben
18. Excelbe tölteni a Kezdőlap -> Bezárás és betöltés -> Adott helyre opcióval lehet. Válaszd a táblázatot és az új munkalapot.
Ezek után a kollégák töltik az adatokat és neked csak az Adatok fülön az összes frissítése gombra kell kattintanod a kimutatás frissítéséhez.
Ha egy napon több esemény is van ugyanazon azonosítóhoz akkor ez nem fogja megjeleníteni azokat, Fferi megoldása az első esemény visszaadja.
üdv
-
Mutt
senior tag
válasz
kezdosql
#38964
üzenetére
Szia,
FFeri makrós megoldása mellett itt egy Power Query megoldás.
1. Az adatsorod táblázatban legyen
2. Excel-ben Adatok fülön Adatok beolvasása táblázatból megnyitja az adatsort Power Query szerkesztőben
3. Töröld az automatikusan létrehozott típus módosítás lépést jobb oldalon lévő lépés-szerkesztőben
4. A dátum oszlopot állítsuk át Dátum formátumra
5. Most pedig vágjuk szét a cellákban található azonosítót és szöveget. Jelöld ki az első oszlopot, majd válaszd az Átalakítás -> Oszlop felosztása -> Karakterszám alapján
Csak egyszer kell az első 4 karatker alapján szétvágni a szöveget:
Az eredmény ez lesz:
A.1 oszlopban lesz az azonosító és az A.2-ben a maradék szöveg.6. Az 5-ös lépést el kell végezned az összes oszlopon, vagyis következő oszlop kijelöl és oszlop felosztása karakterszám alapján.
7. Megvan minden oszlopra az átalakítás.
8. Jön egy furcsa lépés, az Unpivot. Jelöld ki csak a Dátum oszlopot, majd Átalakítás -> Többi oszlop elemi értékekre bontása
Az eredmény ez lesz:
9. Azt kell elérnünk, hogy az érték oszlopban az azonosítók egy másik oszlopba kerüljenek, ebben tud segíteni az hogy az attributum oszlop itt mindig ".1"-re végződik.
Egy új oszlopot fogunk beszúrni, amibe az érték oszlop adatát csak akkor írjuk be, ha az attributum ".1"-re végződik. Oszlop hozzáadása -> Egyéni oszlop opció kell nekünk.
10. Az egyéni oszlop képlete a fenti logika alapján ez lesz:
= if Text.End([Attribútum],2) = ".1" then [Érték] else null
11. Az eredmény megint nem tökéletes, mert pont ott nem látható az azonosító ahol az esemény történt.
12. Ezt lehet orvosolni az Átalakítás -> Kitöltés -> Le opcióval.
A végeredmény jól néz ki:
13. Most már csak meg kell szabadulnunk azoktól a soroktól ahol az érték oszlopban az azonosító látható, megint az attributum oszlop segít nekünk. A ".1"-re végződő attributomok nem érdekelnek minket.
Attributom oszlopon Szövegszűrők -> Nem erre végződik opcióval tudjuk elérni ezt. -
Mutt
senior tag
Szia,
2 pontosítást hadd tegyek:
1. Makrómentesen is meg lehet oldani, Excel 4.0 GET.CELL függvénnyel. A névkezelőben létre kell hozni az alábbi képletet:
=GET.CELL(38;Sheet1!A1)
A 38-as kód a függvényben a cella kitöltését nézi, ha automatikus akkor 0-t ad vissza.Egy segédoszlopban így kell használni:
=NemSzines
Nem szabad megadni cella hivatkozást a képletben, mert mindig relatív hivatkozást fog használni.Mivel csak az adatot tartalmazó cellák érdekelik a kérdezőt, ezért a segédoszlopban egy
=HA(A1<>"";NemSzines;"")csak akkor íratjuk ki a cella háttérszínét amikor van adat benne.Ezek után már csak meg kell számolni a 0-kat a segédoszloban.
2. A megadott makrót szintén finomítani kell mert az üres cellákat is megszámolja, amelyre gondolom nincs szükség.
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False, Optional SkipEmptyCells As Boolean = False)
Dim rCell As Range
Dim lCol As Long
Dim blEmptyCell As Boolean
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If SkipEmptyCells Then
blEmptyCell = Not IsEmpty(rCell)
Else
blEmptyCell = True
End If
If rCell.Interior.ColorIndex = lCol And blEmptyCell Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If SkipEmptyCells Then
blEmptyCell = Not IsEmpty(rCell)
Else
blEmptyCell = True
End If
If rCell.Interior.ColorIndex = lCol And blEmptyCell Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End FunctionHasználata: =ColorFunction(cella amelynek a szinét keressük; vizsgálandó tartomány; ÖSSZEG vagy DARAB kell?;Kell az üres cella?)
A kérdező esetében:
=ColorFunction($E$1;A1:A43;FALSE;TRUE), ahol E1 cella nincs formázva, A1:A43 az átnézendó tartomány, FALSE mert darab kell és TRUE mert nem érdekelnek az üres mezők.üdv
-
#38929
Mutt
senior tag
szricsi_0917
#38904
Mutt
senior tag
válasz
szricsi_0917
#38904
üzenetére
Szia,
Excel 2010-től a Power Query tud az ilyen feladatokban segíteni.
Egy nem olyan friss video a szükséges lépésekről.
üdv
-
Mutt
senior tag
válasz
p5quser
#38725
üzenetére
Szia,
Oszlop diagram (stacked bar chart) esetén nem megy az általam javasolt megoldás, de ha pont diagramot használsz akkor jó lesz. Ezen tutorial alapján menni tudsz Gantt-diagramot másik módon létrehozni és ötvözni a korábban már említett trükköt.
Az y-tengely felirata lehet zavaró, mert ott sorszámokat fogsz látni, de adatfeliratok segítségével megoldható hogy az eredeti szöveg látszódjon.
pl.
üdv
8 tartomány hányszor nem üres, Ha 1-nél többször akkor jön a hibaüzenet.
Új hozzászólás Aktív témák
Fferi50- Mobil flották
- sziku69: Szólánc.
- sziku69: Fűzzük össze a szavakat :)
- Luck Dragon: Asszociációs játék. :)
- Vicces képek
- Kerékpárosok, bringások ide!
- Dell notebook topic
- Milyen asztali (teljes vagy fél-) gépet vegyek?
- Steam, GOG, Epic Store, Humble Store, Xbox PC Game Pass, Origin Access, uPlay+, Apple Arcade felhasználók barátságos izgulós topikja
- Gurulunk, WAZE?!
- További aktív témák...
- Bomba ár! HP Elitebook 8570W - i7-QM I 16GB I 750GB I 15,6" HD+/FHD I Nvidia I W10 I Garancia
- Apple iPhone 13 128GB / Kártyafüggetlen 1ÉV GARANCIÁVAL
- Samsung Galaxy Xcover 6 Pro, 6/128 GB, Kártyafüggetlen
- LG 27GR95QE - 27" OLED / QHD 2K / 240Hz & 0.03ms / NVIDIA G-Sync / FreeSync Premium / HDMI 2.1
- ÁRGARANCIA!Épített KomPhone Ryzen 5 5600X 16/32/64GB RAM RTX 4060 8GB GAMER PC termékbeszámítással
Állásajánlatok
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest
Cég: PC Trade Systems Kft.
Város: Szeged