Hozzászólások
-
Szia!
Hát ez egy klasszikus esete annak a helyzetnek, amikor adatbázis-kezelés feladatra akarunk Excel-t használni. Különben érthető, ha nincs adatbázis-kezelés ismeretünk, e van egy kevés józan eszünk, akkor egész jó adatmodelleket lehet Excel-ben is felállítani.
Az már egy jó indítás, hogy külön táblákban gondolkodsz, bár hogy miket tárolsz külön táblákban, azt átgondolnám, de talán így is lehet.
Ahogy Én csinálnám:
1. Van egy törzs tábla a termékeknek, ahol az oszlopok:
Termék_kód (cikkszám), Terméknév, Termékcsoport, Listaár2. van egy vevő törzs, ahol az oszlopok
Vevő_kód, vevőnév, amit még akarsz3. Engedmény tábla, ahol az oszlopok
Vevőkód, termékcsoport, kedvezmény mértékeEz a harmadik tábla kapcsolja össze a vevőket és a termékeket.
Ha jól értem, akkor az lenne a lényeg, hogy vevőnként tudj árlistát generálni, a nekik érvényes árengedményekkel számolva. A kedvezményt pedig nem termékenként, hanem termékcsoportonként adnád nekik.A listát egy db SQL lekérdezéssel meg lehet kapni a fenti modellből.
De szerintem Függvényekkel meg lehet csinálni Excel-ben is.
Azt csinálnám, hogy
– elsőként kitölteném a fenti 3 táblát
– Kezdenék egy negyedik táblát, ez lesz az eredmény tábla
– Ennek első oszlopai a cikkszámok (esetleg a terméknevek, ha kell), termékcsoportok és a listaár. Tulajdoképpen a terméktörzs tábla másolata. Csinálhatod copy-paste-el vagy hivatkozásos másolással.
– A negyedik oszlopban végig annak a vevőnek a kódja, akinek az árlistát csinálod
– A következő oszlopba a vevőkód és a termékcsoport alapján áthozod az engedmény táblából az adott vevőhöz és termékcsoporthoz tartozó engedményt. Ezt lehet két feltételes FKERES-el, de ebben az esetben lehetne SZUMHATÖBB függvénnyel is.
-Az ötödik oszlopban a listaár és a kedvezmény alapján számolod a kedvezményes árat.Ebből az 5 oszlopból neked csak a cikkszám és a kedvezményes ár kell, de ezt az utolsó lépést a fantáziádra bíznám.
Érthető nagyjából? És egyáltalán erre gondoltál?
Imre
Letöltöttem, kipróbáltam.
Nekem megcsinálja.
Office 365, Excel 2013NHÉ formátumot választottál?
Most látom, hogy a fenti képen hibás formátum van kiválasztva. A te dátumaid a fájlban Nap/Hónap/Év formátumúak, a képen HNÉ látszik, mert elbénáztam.Ja, és a villám kitöltés is működik nálam.
Szia!
Ha Office 365-öt használsz, akkor az 2013 vagy 2016 lesz.
Ebben az esetben delila megoldása mellett létezik egy nagyon egyszerű és gyors megoldás.
1. A rossz dátumok után beszúrsz egy új oszlopot
2. Az első sorba beírod a helyes, magyar dátumot
3. Adatok szalagon a Szövegből oszlopok gomb mellett találsz egy „Villám kitöltés” gombot.
4. Nyomd megÉs van még egy nagyon egyszerű, és minden Excel verzióban működő megoldás, a „Szövegből oszlopk” funkció. Ezzel kapásból, segédoszlop nélkül átalakíthatók a dátumok.
1. Angol dátumok oszlop kijelölése
2. Adatok->Szövegből oszlopok
3. Kétszer Tovább
4. A harmadik lépésben beállítod, hogy Dátum típusú, és kiválasztod a helyes sorrendet. A Te esetedben a NHÉ (Nap/Hónap/Év), mert az angol dátumaid így vannak.
5. A rendeltetési hely alapból az első kijelölt cellára mutat, tehát rá fogja írni az eredeti dátumokra az átalakított verziót
Működik?
Imre
Szia!
Ez egy klasszikus hiba, szinte mindenki elköveti, amikor egy képletben változót akar használni.
Az a gond, hogy a képlet valójában egy szöveg. Dupla idézőjellel kezdődik és záródik. ha ebben csak így simán beírod a változót, akkor annak a neve, és nem az értéke lesz a képletbe, mint szöveg.
Ezt próbáld:Activecell.FormulaR1C1 = _ "=INDEX('" & fc & ".xls'!C5,MATCH(RC1," & fc & ".xls'!C1,0))"Ugye külön figyelni kell a szimpla idézőjelekre is.
A módszer lényege, hogy meg kell szakítanod a szöveget azon a helyen, ahová a változó megy. Jelen esetben az INDEX kezdete és a szimpla idézőjel után.
Befűzöd a változót, és tovább folytatod az eredeti stringet.
A változó következő előfordulásakor újból ugyanez a teendő.Imre
Szia!
Itt bizony az INDIREKT függvényt kell segítségül hívni.
Ezzel lehet szövegesen összerakni egy hivatkozást.Az oszlop indexét használva a CÍM (ADDRESS) függvénnyel meg lehet tudni a keresett oszlop első cellájának címét. Ebből lehet teljes oszlophivatkozást készíteni.
Visszatöltöttem a megoldást.
Elég brutális lett, de megvan lépésenként is.
A tetejére változóként felvettem az adatokat tartalmazó munkalap nevét, és a fejkléc sorszámát.Ha nem túl nagy a táblázat, akkor fog működni.
Sok adattal lassú lesz.Majd írj vissza, hogy jó-e így!?
Imre
Szia Tamás!
Majdnem biztos voltam benne, hogy dátum mezővel szívsz.
Ezekkel autofilternél és advanced filternél is csak a baj van.
Makrófuttatáskor konkrétan nem eszi meg a dátumot dátum formátumban.
Tehát nem írhatod ezt:>=2016.03.24Az már eggyel jobb próbálkozás volt, (mert gondolom sejtetted a problémát), hogy megpróbáltad összefűzni a dátumot, de az eredmény továbbra is dátum forma maradt:
Range("C2").Select ActiveCell.FormulaR1C1 = "=""<=""&YEAR(RC[10])&"".""&MONTH(RC[10])&"".""&DAY(RC[10])"Az egyik lehetséges megoldás az, hogy megnézed, hogy annak a dátumnak, ami akarsz,
mi a dátumszáma, és azt írod a feltételbe. Pl.: a 2016.03.29 dátumszáma 42458.Tehát ez jó lehet:
<=42458De ez elég béna, ezért számított formában érdemes beírni a cellába a makró futtatás előtt:
="<="&DÁTUM(2016;3;27)Beírás után különben ez dátumszámkét fog látszódni
<=42457És végül, ha makróval akarod beírni:
Selection.FormulaR1C1 = "=""<=""&DATE(2016,3,27)"Így a dátum egyes részei lehetnek befűzött változók is.
Kipróbáltam a küldött fájlon, működik, úgyhogy menni fog Nálad is, most nem xcsatolom vissza.
Minden jót, majd jelezd, hogy rendben van-e!
Imre
Szia!
Elsőként indítsd el SAFE módban az Excel-t.
Tartsd lenyomva ctrl billentyűt, mikor indítod
Ez letiltja az esetleges beépülő modulokat.
Nézd meg, hogy így elérhető-e a menüben a cucc!
Ha igen, akkor valami lakó van az Excelben.Telepítettél mostanában rá bármilyen add-on-t?
Powerquery, PowerPivot, vagy bármi, ami az excelbe beépülő lenne?
Esetleg ez a problémás fájl tartalmaz makrókat, amik futás közben lefagytak, vagy bármi miatt nem fejezték be a dolgukat?Kerestél már a neten a probléma után?
Ha tudsz makrókat futtatni, akkor próbáld ki ezt:
Sub ResetCellMenus() Dim cbr As CommandBar For Each cbr In Application.CommandBars If cbr.Name = "Cell" Then cbr.Reset Next cbr End SubHa ez nem, akkor az Immediate ablakban futtatsd ezt:
Commandbars("Cell").ResetJelezz vissza, hogy mi lett!
Imre
Szia!
Ha azt akarod, hogy ne is tudja megnyitni a munkalapot, akkor miért nem rejted el?
Ha elrejted, és utána jelszóval véded a munkafüzet szerkezetét, akkor nem tudja előszedni.
(Korrektúra->Füzetvédelem)Átlag user-ről beszélek, nem arról, aki mindenképpen fel akarja törni.
Az elrejtést kétféleképpen lehet:
– Az egyik a hagyományos jobb klikk->Elrejtés
– A másik a jobb klikk-> Kód megjelenítése -> Bal oldalon kiválasztod a munkalapot -> F4-el előveszed a tulajdonságok ablakot -> Utolsó tétel a „Visible” tulajdonság -> VeryHiddenUtóbbi úgy rejti el, hogy nem látszik a felfedés menüben, hogy van elrejtett munkalap.
Amit eredetileg akarsz, hogy ne is lehessen kiválasztani a munkalapot, de különben látszódjon, csak makróval lehet megcsinálni.
A makrók pedig letilthatók, és ott van vége.Ráadásul akkor mindenkinek, még neked is tiltva lesz az átkattintás. Hacsak nem még bonyolultabb makróval megnézzük a megnyitáskor, hogy jogosult személy nyitotta-e meg a fájlt.
imre
Szia!
Nagyon érdekes problémákat hozol. Először azt hittem, passzolnom kell, mert nem nagyon volt ötletem, és nem is nagyon találtam megoldást. Végül meglett, sé kiderült, hogy elsőre elég közel jártam.
Szóval tegyük fel, hogy a tartományod az A1:D4-ben van.
1. Tömbképlettel megnézheted, hogy hol van benne a legnagyobb érték:
=--(A1:D4=MAX(A1:D4))
Ez egy tömböt ad eredményül. Ott lesz benne egy egyes, ahol a legnagyobb érték van.2. Ha ezt szorzod a tartomány celláinak az oszlopaival, akkor az eredmény mátrixban az adott helyen a keresett max érték oszlopának száma lesz, a többi helyn meg nulla.
3. Ezt a szorzatösszeggel összeadva, megkapható eredményként a max érték oszlopának száma száma
=SZORZATÖSSZEG(--(A1:D4=MAX(A1:D4))*OSZLOP(A1:D4))4. A fejlécből kiveszed az ennyiedik elemet
5. Ugyanezt megcsinálod a sorokra is.
6. A két képletet egybe is vonhatod, hogy a sor és oszlop számítás, meg az INDEX függvények ne legyenek külön cellákban.
FONTOS!
Ez nyilván csak akkor műxik, ha nincs két egyforma legnagyobb.
Különben PASSZ!Visszatöltöttem a fájlt a megoldással.
Imre
Szia!
Ott keresd a problémát, hogy nézd meg, hogy működik a keresés, amikor kézzel keresel.
Amikor minden előforduláson átmentél a „Következő” gombbal, akkor elkezdi a keresést elölről, és újra az első találatra ugrik. Ezért a .find – .findnext ciklusoknál azt csinálják, hogy megjegyzik az első találat (.find) celláját, és a ciklus addig fut, amíg a .findnext újra az elsőnek megtalált cellára ugrik.Itt van két link, tanulmányozd:
http://www.ozgrid.com/forum/showthread.php?t=168157
http://www.ozgrid.com/forum/showthread.php?t=37604Még okozhat esetleg további problémát az, hoyg a találatok A oszlop végére írásánál SELECT-et használsz. meg kéne próbálni select nélkül, egyből beleírni.
Cells(ActiveCell.CurrentRegion.Cells(ActiveCell.CurrentRegion.Cells.Count).Offset(1, 0).Row, ActiveCell.Column).Value = keresett.AddressImre
Szia!
Hát, így elsőre nem túl egyszerű a kérdés. Illetve az igényektől függően bonyolult.
Mert ha csak az a kérdés, hogy a kódok az egyes oszlopokban hányszor vannak benne, az még nem olyan problémás.
Ha viszont az oszlopkombinációkhoz akarod a kódokat rendelni, nos az már kicsit izgalmasabb.Nyilván adja magát az, hogy makróprogramot lehet írni, és akkor bármit meg lehet csinálni.
Kicsit eljátszogattam vele, készítettem egy modellt 3 oszlopra (A,B,C)
A-B-C oszlopokba vannak a kódok, ismétlődésekkel, ahogy írtad.
F oszlopba képeztem a kódok egyedi listáját, és mögé kiszámoltam, hogy melyik kód melyik oszlopban hányszor szerepel. ha több, mint nullaszor, akkor az oszlop kódját kapja, különben üres sztringet.=HA(DARABTELI(A$1:A$10;$F2)>0;G$1;"")Tehát a piros fejlécű táblázat már megoldás első szinten. Az utolsó oszlopban (J oszlop) benne van, hogy egy kód mely oszlopokban van benne.
A második féle megoldás a kék fejlécű részben van.
Itt a fejlécben a lehetséges oszlop-kombinációk vannak, és minden kombinációhoz kilistázza a kódokat az F oszlopból. Ez egy elég bonyolult tömbképlet, amit oszloponként addig kell lehúzni, amíg üres nem lesz az eredmény. Ha módosítanád, akkor fontos, hogy Ctrl-Shift-ENTER-rel kell lezárni.Próbáld meg alkalmazni a 6 oszlopos verzióra.
Ha az első szintű megoldás elég, akkor készen vagyunk.
csatoltam a modellt.
Imre
P.S
Nem vettem észre, hogy csatoltál egy mintafájlt, bocs, hogy nem azzal dolgoztam.Szia!
A változók (kezdes és vege) string típusok.
A vege változót lehet „off”-ra vizsgálni. Tehát elég, ha csak ennyit teszel, és ha a 4. oszlopba „off” kerül, akkor mellé egy nullát tesz, különben lefut a munkaidő számítás.Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim kezdes As String Dim vege As String Dim perce As Integer kezdes = Cells(Target.Row, 3).Value vege = Cells(Target.Row, 4).Value If Target.Column = 4 Then If vege = "off" Then Cells(Target.Row, 5).Value = 0 Else perce = CInt(Minute(CDate(vege))) Select Case perce Case 0, 30 Cells(Target.Row, 5).Value = (vege - kezdes) * 24 Case 1 To 59 Cells(Target.Row, 5).Value = WorksheetFunction.RoundUp((vege - kezdes) * 24 / 0.5, 0) * 0.5 End Select End If End If End SubA bolondállóság azt jelenti, hogy mennyire vizsgáljuk, hogy a felhasználó valóban helyes formátumú, intervallumú adatot írt be. Ha mások is használják, akkor ezt jobban szoktuk ellenőrizni, hogy ne adjon az első tévedésnél runtime hibát, vagy egyéb hibás működést.
Ezt a szintet neked kell eldönteni. Ez a kis progi most nem túl bonyolult, simán el lehet magyarázni, hogy hogy kell használni, és mi lesz ha nem jót ír be.
Lehetne vizsgálni mindkét oszlopban, hogy egyáltalán időt írt-e be, ezek hogy viszonyulnak egymáshoz (pl. a vége később van-e, mint az eleje), stb…
Imre
Na, egy kicsit körbejártam a problémát, mert valóban volt itt egy olyan gond, amivel még nem foglalkoztam Én sem. Ahogy a tanfolyamon is említettem, eddig élesben nem nagyon foglalkoztam userform-os alkalmazásokkal.
Volt két alap hiba. Az egyik, hogy miután bemásoltad a kódot a fórum weboldaláról, nem vetted észre, hogy a range(„adatok”) dupla idézőjelei nem egyenesek, hanem ferdék, azaz nem a VBA szintaxisának felelnek meg. Ezeket kézzel vagy cserével cserélni kell. Ezt valószínűleg a WP editor rontotta el.
A másik, amit Én is elmulasztottam kezelni, hogy ha választasz valamit a Combi panelben, akkor az szöveges lesz, de a Kód oszlopban számok vannak,ezért még egy Val függvényt be kell iktatni. Enélkül a Vlookup nem fog találni semmit, és hibával elszáll.A lényegi problémát viszont nem ezek, hanem a következő okozta:
Miközben az Initialize rutin fut, és eléri a BoundColumn=1 sort, az kiváltja a Combobox1 Change eseményét. A Change-ben pedig van egy Vlookup, ami keresi a Combobox1.value értéket a tartomány első oszlopában. de mi van éppen a Combipanelben? Egy nagy semmi, mert még el sem értünk arész betöltéséig. ha meg eljutottunk volna, akkor pedig a "<válassz>"stringet keresné. Nem találja, ezért hibát dob.Megoldás
Van Combobox-nak egy ListIndex tulajdonsága, ami megmondja, hogy hányadik elemet választották. Nullától kezdődik a sorszámozása. Ha nem a listáról való a benne lévő aktuális tartalom, akkor pedig -1-et tartalmaz. na ezt kell figyelni a Change-ben.Private Sub ComboBox1_Change() If ComboBox1.ListIndex > -1 Then TextBox1.Value = WorksheetFunction.VLookup(Val(ComboBox1.Value), Sheets(1).Range("adatok"), 2, 0) TextBox2.Value = WorksheetFunction.VLookup(Val(ComboBox1.Value), Sheets(1).Range("adatok"), 3, 0) End If End SubNézd meg, nálam működött.
Volt még kérdésed.
A Show metódus mielőtt megjeleníti az űrlapot, kiváltja az Initialize eseményt. ez gyárilag van így, pont azért, hogy a form egyes elemeinek tulajdonságait még a megjelenítés előtt, dinamikusan meg tudjuk határozni. Tehát a Show hívja meg az Initialize-t.Imre
Szia!
A munkaidő kiszámolását a 4. oszlopban történt cellaérték változás eseményéhez kötöd.
De a munkalap beszúrásakor, amikor a fejlécet hozza létre, ott is ír a 4. oszlopba, de az nem időérték, hanem egy szöveg, amit megpróbál átalakítani úgy, ahogy a change eseménykezelőben megadtad, de nem sikerül neki.Az ilyesmit úgy lehet megoldani, hogy amíg a fejlécet töltöd, addig kikapcsolod az eseménykezelést.
Ezt így lehet:Application.EnableEvents=FalseMajd miután már kell figyelni az eseményekre, visszakapcsolod.
csatoltam a működő verziót.
Tegyük fel, hogy a számaid az A oszlopban, A2-nél kezdődnek.
A szomszéd, B oszlopba írd ezt a képletet, majd szűrd a táblát a B oszlopban az IGAZ értékekre.=JOBB(KEREK.LE(A2;-2);3)="500"Lefelé kerekít 100-asokra, majd megnézi, hogy az utolsó 3 karakter 500 lesz-e.
Remélem megfelel. 🙂
Imre
Szia!
Az INDIRECT függvény nem tudja ezt.
Kicsit utánanéztem, de csak makrós, UDF-es megoldást találtam, meg ezt a kijelentést:„INDIRECT does not evaluate formulas that use worksheet functions, and if the defined name represents a formula and not a simple reference, it’ll error out.”
Tettem egy ilyen próbát:
G2-t választottam (üresen)
Megnyitottam a Visual Basic Immediate ablakot (ALT-F11, majd Ctrl-G)
Beírtam ezt:
Selection="="&"keresztnévből"Működik.
Ez azt jelenti, hogy ha feltennél egy Combo box ActiveX vezérlőt, beállítanád, hogy ezt a két nevet lehessen kiválasztani, akkor a vezérlő Change eseményéhez rendelhetnéd a következő kódot:Private Sub ComboBox1_Change() Range("G2") = "=" & ComboBox1.Text End SubÉs persze ezután makróbarát munkafüzetként kell elmentened a fájlt.
Szóval Macro Free megoldás nem létezik.
Szerintem.
Imre
Szia!
Na, egy kicsit eljátszogattam vele.
Hogy jobban megértsd, nézd meg ezt a korábbi anyagot az intervallum tömbökről!Megcsináltam hagyományos módon segédcellákkal is, mint ahogy Te, majd tömbképlettel is.
Extra, hogy az adott évet, amikor a legtöbben voltak, azt is meg lehet kerestetni.Elméletileg az a lényeg, hogy képezni kell két tömböt:
- Az egyik a legkisebb és a legnagyobb év közötti évszámok egyesével növekvő tömbje
- A másik pedig az egyes évekhez tartozó létszámok
- Végül venni a létszámok maximumát
Ezt a két tömböt képeztük le a segéd cellákkal, de lehetséges tömbképletben a memóriában is leképezni.
Hogy kicsit olvashatóbb legyen a képlet, elneveztem az éveket tartalmazó két oszlopot, és külön-külön a be és kilépési évek oszlopait, és egy külön cellában nevekkel is ott a képlet.
Az I17-ben van, amit keresel, alatta a hozzá kötődő évszám.Ha több olyan év is lenne, amikor azonos létszám dolgozott, és ezek a legnagyobbak (pl. több évben is dolgoznának 10-en), akkor csak az első évet tudná megkeresni.
A H oszlopban van a legrövidebb verzió, ami már használ egy elnevezett képletet is, ami az évek tömbjét állítja elő.
Nézz körül a névkezelőben!
Érdekes feladat, érdekes volt vele dolgozni, köszönöm.
Imre
Szia!
Nem teljesen értem a kérdésedet.
Azt könnyű megmondani, hogy hányan tanítottak egy adott évben. Például DARABHATÖBB függvénnyel.
De ezen belüli szélső értéknek nem látom értelmét.Ha esetleg az lenne a kérdés, hogy melyik évben tanítottak a legtöbben, na, annak már van értelme.
Minden vizsgált évhez kiszámolod az előző függvénnyel a létszámot, és annak veszed a maximumát.
Ezt lehet két lépésben, segédoszloppal, vagy egyben tömbképlettel.Szóval mi is a kérdésed?
Az is jó, ha leírod, hogy a fenti táblázatból mit szeretnél eredményként látni, és miért.Másik, hogy ha lehet, ne képet tölts fel, mert azzal nem tudok tesztelni, így be kellene gépelnem a minta adatokat.
köszi,
Imre
Ok, szóval hogy mindenki értse, van egy ilyen adat:
D3,1333,16GB,C9,Kingston,Hyp,K2,2x8GB,HyperX,Fury,White,Series,HX313C9FWK2/16és ebből vissza szeretnéd kapni az utolsó vessző utáni elemet.
Pont erről szólt egy korábbi kérdés, csak ott az utolsó per jel utánit kellett kivenni.
Ott van a képlet, csak a per jelet vesszőre kell cserélned.Kipróbáltam, működik.
Imre
