- 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
válasz
karlkani
#45260
üzenetére
Szia,
A hónap napjai egy oszlopban, egymás alatt helyezkednek el (C oszlop). Az ünnepnapok színezése egy másik lap alapján történik, névkezelőben hozzáadva Ünnepnapok néven
=SZORZATÖSSZEG((HÉT.NAPJA(Ünnepnapok;11)<6)*(HÓNAP(Ünnepnapok)=HÓNAP(C$2)))
Hogyan műkődik?
- a HÉT.NAPJA meg mondja hogy az adott ünnepnap melyik napra esik, a <6 a hétköznapokat jelenti.
- a HÓNAP(Ünnepnapok) = HÓNAP(C$2) kiválogatja azokat az ünnepnapokat, amelyek azonos hónapban vannak mint a C2-ben lévő dátum.Ha a két feltétel azonos, akkor 1-et ad vissza a szorzás, különben 0-t.
Végül már csak az 1-et kell összeadni. Lehetn a SZUM-át is használni, de a képlet tömb-képlet és ott előnyösebb a SZORZATÖSSZEG.üdv
-
Mutt
senior tag
válasz
Carasc0
#45230
üzenetére
Szia,
Egy Excel for Ipad és Excel 2016 között kb hány %-os kompatibilitás van?
Nincs aktív tapasztalatom Excel Apps-al IOS-en, de MS súgót olvasva, ezt látom:
- Nézegetésre megfelelő.
- Szerkesztésre csak az alap fájlformátumok mennek.
- A függvények teljes mértékben mennek, tömb-képletek is (csak újat nem fogsz tudni létrehozni).
- Pivot, fejlesztőeszköz (gombok, vezérlők), VBA nincsen.Kezdő szinten szerintem 100%-os a kompatibilitás, haladó szinten 50%-ot mondanék.
Üzleti fájlok létrehozására alkalmatlan, max. prezentálni lehet vmit vele.üdv
-
Mutt
senior tag
válasz
Misi_D
#45222
üzenetére
Szia,
Ezt próbáld ki.
1. Fájl importálása Power Query-be.
2. Majd csoportosítás és a keresd meg id-nként a ciklusszámláló maximumát.
3. Most jön egy furcsa lépés, de így a legyegyszerűbb. Szóval lekérdezések egyesítése és ott válaszd ki a táblát kétszer és add meg hogy az id és a ciklusszámláló alapján keressen belső egyezést.
4. Jön a második furcsa lépés, de ehhez M-kódot kell módosítani. A szerkesztő lécen vmi ilyen kód lesz:
Ahol a #"Sorok csoportosítva" egy hivatkozás egy korábbi lépésre aminek egy tábla az eredménye. Power Query-ben nem kötelező a közvetlen előző lépésre hivatkozni, lehet bármelyik korábbira hivatkozni. Nekünk pedig az a lépés kell ami még az eredeti (nem csoportosított adatsort) tartalmazza. Az én esetmben ezt a lépést "Típus módosítva"-nak hívják. Még egy módosítás kell, "max" oszlop nincs az eredeti táblázatban hanem ott nekem "counter" van ezt is le kell cserélni.
Ha ezeket javítod akkor ez lesz az eredmény:
5. Most már csak ki kell bontanod a Sorok csoportosítva oszlopot és megkapod kívánt eredményt.
üdv.
Ps. Ha a nagy fájl miatt lassú lenne a feldolgozás, akkor még egy trükk. Lehet puffereltetni egy adott táblát.
Az én esetemben a "Típus módosítva"-t érdemes. Ehhez azt kell csinálni, hogy ezen lépés után beszúrsz egy új lépést (pl. új oszlop hozzáadása). Majd a kapott M-kódot felülírod ezzel: = Table.Buffer(#"Típus módosítva") -
Mutt
senior tag
válasz
zsolti_20
#45217
üzenetére
Szia,
Power Query-s megoldás VBA-mentesen.
1. Alakítsd át a listát és a szűrőt 2 táblázattá.
2. Ezek után Adatok -> Táblázatból vagy tartományból opcióval töltsd be Power Query-be az egyik táblázatot.
3. Kezdőlap -> Bezárás és betöltés -> ... adott helyre majd válaszd a csak kapcsolatot.
4. 2-3-as lépést csináld meg a szűrővel
5. Kapcsolatok alatt jobb klikk a lista lekérdezésen és Szerkesztéssel menjünk vissza a Power Query szerkesztőbe.
6. Most számoljuk meg hogy hány eleme van az egyes csoportoknak. Ehhez a Kezdőlapon a Csoportosítási szempontot használd. Itt elég a sorokat megszámolni, de ha nem bánod akkor haladó üzemmódban inkább ezt állítsd be:
7. Az Elemek egy beágyazott tábla, ami tartalmazza az eredeti listádat és ebből ki fogjuk nyerni a tételeket.
(Természetesen lehet ezt sima lekérdezés egyesítéssel helyettesíteni, ha a hagyományos utat választanád.) Adj egy új oszlopot a táblázathoz, aminek ez legyen a képlete: =[Elemek][Tetel]
8. A Tetel oszlop jobb felső sarkában lévő ikonra kattints és válaszd a kibontást új sorokba. Ezzel visszakaptuk az eredeti listát, csak most már tudjuk hogy hány elem van egy csoportban.
9. Jöhet a Kezdőlap -> Lekérdezések egyesítése. Itt kiválasztjük a szűrő lekérdezést és a közös oszlopot kijelöljük. Alul javasolt a Belső (egyező sorok) opciót használni.
10. Most megint meg kell számolnunk, hogy egyes csoportokban hány elem van. Hasonlót csinálunk mint a 6-os lépésben.
11. Megint lett egy beágyazott táblánk, amiből megint kellenek a tételek, de nyerjük ki még az eredeti elemszámot is. A tételeket ugyanúgy mint a 7-es lépésben egy új oszloppal kapjuk meg.
12. Az eredeti elemszámhoz szintén egy új oszlop kell, aminek a képlete ez: =List.Min([Tetelek][Elemszám])
13. Már látszik, hogy nekünk csak azok a sorok kellenek, ahol az eredeti és az új elemszám azonos. Ehhez megint kell egy új oszlop. Képlete: =[Elemszám]=[EredetiElemszám]
14. Az új oszlopban szűrjünk rá a TRUE sorokra.
15. Töröljük a felesleges oszlopokat (csak a csoport és a tétel kell)
16. Tétel oszlop jobb felső sarkában megint kibontás sorokba.
17. Eredmény visszatöltése Excelbe, Kezdőlap -> Bezárás és betöltés.
18. Jobb klikk megint a Lista lekérdezésen és válasszuk a Betöltés helyét, majd Táblázat.Ez lett a végeredmény.
üdv
Ps. VBA-val limitáltan lehet módosítani a Power Query-t.
-
Mutt
senior tag
válasz
TheSaint
#44814
üzenetére
Szia,
Egy főtáblából szeretnék adott oszlopokat lekérdezni olyan módon hogy a sorok mellé szabadon lehessen megjegyzéseket irogatni és a főtábla változásakor a megjegyzés maradjon...
Megoldás lehet, hogy
1. a főtáblához is hozzáadjuk a gépelt megjegyzést vagy
2. egy segédtáblában tároljuk a megjegyzéséket a főtábla megfelelő sorára mutató azonosítóval együtt.MIndkét megoldás makrót igényel valószínű Worksheet Change eseményt használva.
Az első esetben a makrónak meg kell találnia a főtábla megfelelő sorát és oda átmásolni a megjegyzést.
A második esetben a makrónak egy új táblához kell hozzáfűznie a megjegyzést egy azonosítóval és ezek után a Power Query-vel lehet hozzáadni a riporthoz a megjegyzést.üdv
-
Mutt
senior tag
válasz
zoombiee
#44797
üzenetére
Szia,
Látom Bobrooney már küldött jó megoldást.
Google Sheets App Script-et használ, a megadott VBA kódod ennek felel ott meg:
function CommandButton1_Click() {
var sheet = SpreadsheetApp;
var teszt1 = sheet.getActiveSpreadsheet().getSheetByName("Teszt1");
var teszt2 = sheet.getActiveSpreadsheet().getSheetByName("Teszt2");
var start_x = teszt1.getRange("C17").getValue();
var start_y = teszt1.getRange("D17").getValue();
teszt2.getRange(start_x, 3).setValue(start_y);
};Gombot pedig rajzolással tudsz létrehozni.
üdv
-
Mutt
senior tag
válasz
modflow
#44792
üzenetére
Szia,
Excel 2010-től van Power Query, amivel ha azonos a lapokon a struktúra akkor lehet összesítést könnyedén létrehozni. Power Query-nek az előnye, hogy mint egy makró a rögzített lépések újra felhasználhatók, így ha változik a fájlodban az adat akkor egy frissítés után megkapod a legújabb eredményt.
A minta fájlomban van 3 munkalap, amelyek így néznek ki (a tartalom természetesen laponként más és más)-
Ezek után ezek a lépések (angol Excelben tudom most mutatni):
1. Fájlban hozz létre egy munkalapot ahova kerül majd az összesítés, én Összesít nevet adtam neki.
2. Data -> Get data from file opcióval nyisd meg az Excel fájlt.
3. A navigátor ablakban ne kattints a munkalapra, hanem válaszd a fájlt ki majd alul a második opció Transform data kell. Ezzel megnyílik a Power Query szerkesztő része.
4. Itt a munkalapok nevénél válasszuk ki azokat, amelyek nem kellenek (nálam az Összesít).
5. Jelöld ki a Data oszlopot és jobb klikk a többi oszlop eltávolítása, hogy csak a Data oszlop maradjon meg.
6. Data jobb felső sarkában kattints a kibontás ikonra és okézd le.
7. Innentől a fájlod tartalmától függenek a lépések. Nekem pár lépés volt, hogy ezt a formát
erre át tudjam alakítani.
8. Ha készen vagyunk, akkor már csak Excel-be kell visszatölteni a Close & Load opcióval.
9. ha mentesz és utána frissíted a riportot akkor lesz egy mellékhatás (a fájlnak része lett az új report és azt is be fogja építeni a következő frissítéskor). Ezt úgy lehet kiküszöbölni, hogy megnyitod a lekérdezést Power Query-ben (Data -> Queries & Connections -> jobb klikk a lekérdezésen és Edit). Elmész a második lépéshez és ott KInd oszlopon szűrsz, hogy csak a munkafüzetekről dolgozzon.
üdv
-
Mutt
senior tag
válasz
fausto
#44768
üzenetére
Szia,
Excel 2010-től van Power Query, amiben az Unpivot (oszlopok elemi értékre bontása) opciót kell használnod.
Az első formátumnál csak az ID-oszlopot kell kijelölnöd majd a Többi oszlop értékre bontását.
A második formátumnál az összes oszlopot jelöld ki majd Oszlopok értékre bontása.
üdv
-
Mutt
senior tag
Szia,
Nincs CS01 hozzáférésem, így nem tudtam tesztelni de ezt próbáld meg:
Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.Workbooks("dj feltolt").Sheets("Munka1")
For i = 2 To objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
'CS01 fõképernyõn anyagszám / plant / BOM usage megadása
session.findById("wnd[0]/usr/ctxtRC29N-MATNR").Text = COL1
'session.findById("wnd[0]/usr/ctxtRC29N-WERKS").text = "0610"
session.findById("wnd[0]/usr/ctxtRC29N-STLAN").Text = "1"
session.findById("wnd[0]/usr/txtRC29N-WTEXT").SetFocus
session.findById("wnd[0]/usr/txtRC29N-WTEXT").caretPosition = 0
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
'component screen amiről csak akkor kell elmenni, ha másik anyagot kell felépíteni
j = -1
Do
j = j + 1
COL2 = Trim(CStr(objSheet.Cells(i + j, 2).Value)) 'Column2
COL3 = Trim(CStr(objSheet.Cells(i + j, 3).Value)) 'Column3
session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-POSTP[1," & CStr(j) & "]").Text = "L"
session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-IDNRK[2," & CStr(j) & "]").Text = COL2
session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-MENGE[5," & CStr(j) & "]").Text = COL3
session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-MENGE[5," & CStr(j) & "]").SetFocus
session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/txtRC29P-MENGE[5," & CStr(j) & "]").caretPosition = 5
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").sendVKey 0
Loop Until COL1 <> Trim(CStr(objSheet.Cells(i + j + 1, 1).Value))
'mentés
session.findById("wnd[0]/tbar[0]/btn[11]").press
'léptetni kell az i értékét
i = i + j
Next i
MsgBox "Keszen vagyunk"Neked egy fájlban vannak a létrehozandó anyagtörzsek (az aktuális mindig a COL1 változóban van), így addig nem kell kilépned CS01-ből amíg új anyaghoz nem érsz.
A fenti megoldásban van egy Do - Loop ciklus ami addig adogatja a komponenseket a listához amíg a COL1 értéke meg nem változik.A másik fontos dolog, hogy a rácson hivatkozni SAP-ban sor és oszlop azonosítóval lehet.
pl. .. CMAT/ctxtRC29P-IDNRK[2,0] mindig a második mezőt az első soron jelenti, vagyis a komponenst.
A sor változót kell léptetni, hogy a komponensek egymás alá kerüljenek és ne mindig az első sor legyen felülírva. Ezért van mindegyik sorban a j belső változó meghívva.Ami fontos, hogy a script el fog hasalni ha olyan sorra hivatkozol ami a nem látható képernyő része. A képernyőmentéseden látszik 19 sor, de ha kisebbre teszed a GUI-t akkor lehet hogy csak 5 sort fogsz látni. Ezt kivédeni úgy lehet, hogy mielőtt írnál megnézed hogy létezik-e az adott sor. Tippre vhogy így:
On Error Resume Next
If session.findById("wnd[0]/usr/tabsTS_ITOV/tabpTCMA/ssubSUBPAGE:SAPLCSDI:0152/tblSAPLCSDITCMAT/ctxtRC29P-POSNR[0," & CStr(j) & "]").Text <> Right("000" & j, 4) Then
MsgBox "hiba"
End If
On Error GoTo 0Megkértem a jogosultságot a CS01-hez így ha majd megkapom akkor tudom tesztelni, hogy az elmélet párosul-e a valósággal.
üdv
-
#44656
Mutt
senior tag
Csokishurka
#44643
Mutt
senior tag
válasz
Csokishurka
#44643
üzenetére
Szia,
A táblázatból nem derül ki, hogy miből látszik hogy melyik hiba mennyi ideig tartott.
Kérlek add meg, hogy milyen Excel verzióhoz kellene a megoldás.üdv
-
Mutt
senior tag
válasz
Slowman
#44460
üzenetére
Szia,
Kimutatáson jobb klikk és Kimutatás beállításai (Pivot Table Options) -ban az első lap alján be van pipálva, hogy megtartsa a formátumokat? A felette lévővel is érdemes kísérletezni, szerintem érdemes kipipálni azt, hogy ne méretezze át az oszlopokat minden alkalommal.
Ha ezek után sem tartja meg, akkor szerintem az a gond, hogy a cellákat közvetlenül formázod, de a helyes megoldás, hogy a Kimutatásban az értékekre kattintva kell a formázást állítani.
üdv
-
Mutt
senior tag
válasz
RedHarlow
#44459
üzenetére
Szia,
Ahol D2-ben a képlet ha Excel 2016-od van legalább:
=(HOSSZ(SZÖVEGÖSSZEFŰZÉS("";IGAZ;$A$2:$A$10))-HOSSZ(HELYETTE(SZÖVEGÖSSZEFŰZÉS("";IGAZ;$A$2:$A$10);C2;"")))/HOSSZ(C2)Vagy ez:
=SZORZATÖSSZEG(HOSSZ($A$2:$A$6)-HOSSZ(HELYETTE($A$2:$A$6;C2;"")))/HOSSZ(C2)Az E-oszlopban pedig ez a képlet:
=HAHIBA(INDEX(A:A;ÖSSZESÍT(15;6;SOR($A$2:$A$10)/ELŐJEL(SZÖVEG.TALÁL(C2;$A$2:$A$10));1));"")Power Query-vel is meg lehet oldani (ha igaz, hogy szavak mindig nagybetűvel kezdődnek) pár lépésben, ahol a legbonyolultabb függvény az első találat megkeresése.
.
üdv -
-
Mutt
senior tag
Sziasztok,
A másik fórumon felmerült egy probléma, amelynél egy "kódolt" szövegsorból kell számtartományokat előállítani.
A példa szerint a "30000000+34000000+341-34100099+348-341003+3401" karaktersort az alábbiak szerint kell értelmezni:
1. A számok 8 értékes jegy hosszúságú tartományokat jelölnek. Ahol mind a 8 karakter megvan ott a tartomány alsó és felső értéke azonos, a többi esetben pedig az tartomány kezdőértékét a megfelelő számú nullák hozzáadásával lehet elérni. A felső értéket pedig a megfelelő számú kilencesekekkel.
Így lesz a 341-ből 34100000 - 34199999 tartomány.
2. Ha a szám előtt minusz van, akkor az abból készült tartományt ki kell majd a végeredményből zárni. A többi esetben kell a tartomány.A feladat, hogy az így megadott kódsorból állítsuk elő a használható számtartományokat.
Ebben a feladatban lehet használni pár képességét a Power Query-nek:
1. A kódolt számsort fel lehet darabolni a +/- jelek alapján, hogy külön lehessen őket kezelni.
2. A nem 8 hosszúságú számokkból lehet alsó és felsőértéket előállítani a Text.PadEnd függvénnyel.
3. Lehet előállítani egy számsort adott két szám között a list operátorral (kapcsos zárójel).
4. Lekérdezések segítségével ki lehet zárni a nem kívánt számokat a végerdményből.
5. Lehet csoportosítgatni, hogy megkapjuk hogy mely számtartományokról van szó.
A nem tökéletes változat letölthető innen
Az eredmény nem hibás, csak lehetne jobban is összevonni a tartományokat illetve brute forceként minden számot létrehoz majd szűrőget, így lassú tud lenni nagy számok esetén.
üdv
-
Mutt
senior tag
válasz
maliszka
#44258
üzenetére
Szia,
...Ebbe lehet importálni CSV file-ból pl banki adatokat, amiket nem akarok egyenként felvenni....
A fenti képen bal oldalt van egy elméleti adatsor letöltve a bankodból, jobb oldalt pedig a programnak megfelelő sorrend van.
H2-ben ez a képlet:
=HA($A2<>"";B2;"")J2-ben pedig ha magyar területi beállítású Excel-t használsz akkor ez (a helyette a tizedesvesszőt pontra cseréli a proginak, importálásnál ez az alapértelmezett):
=HA($A2<>"";HELYETTE(SZÖVEG(C2;"#0,00");",";".");"")K2-ben ez a képlet (feltételezve hogy csak a tényleges kiadásokat/bevételeket vezeted):
=HA(A2<>"";"C";"")I2 képlete:
=HA($A2<>"";SZÖVEG(A2;"éééé-hh-nn");"")
M2 képlete:=HA(ÉS($A2<>"";D2<>"");D2;"")I-oszlopba neked kell bevinned a programban használd kategóriáidat (javasolt egy adatérvényestés ehhez).
O-oszlopban ezen adatokat már csak össze kell fűzni és a végére még egy vesszőt tenni.
O2-ben a képlet:=HA(A2<>"";SZÖVEGÖSSZEFŰZÉS(",";HAMIS;""""&H2:M2&"""")&",";"")Ezeket a képleteket jó sokszor másod le és kész is a sablonod.
A következő lépés már csak az O-oszlopban lévő adatok kijelölése és másolása egy szövegszerkesztöbe ami képes UTF-8-as kódolással menteni. pl. az ingyenes Notepad++
Ezek után a fájlt már csak fel kell másolni a telóra.üdv
Ps. A https://github.com/viszi/codes/blob/master/Excel/Useful/Export2CSV/Export2CSV.xlsm oldalon van egy Excel fmakró, ami képes kimenteni adott elválsztókkal a kijelölt tartományt. Ez majdnem jó lenne, de az ékezetes karaktereket nem jó kódolással menti ki a programnak így esetedben felejtős.
-
Mutt
senior tag
Sziasztok,
Másik fórumon az alábbi probléma merült fel: felhasználó szeretné ha megad számokat és azok ismétlésének számait (ami eltérő is lehet), akkor abból a legkönnyebben legyen egy kész lista.
A képen bal oldali listából kellene a jobb oldali.
Power Query-vel ez nagyon könnyen elérhető. A lényeg, hogy felsorolást kapcsos zárójellel az alső és felső érték megadásával lehet létrehozni. pl. {1..99} vagy {'a'..''z'}
A listát sorokba kell bontatni és kész is az eredmény.
üdv
-
Mutt
senior tag
válasz
alfa20
#44251
üzenetére
Szia,
PowerPivot-tal megy, az kinézettel csak annyi a gond, hogy a lenti képen az I-J oszlopot el kell rejteni ha zavaró.
Lépések:
1. Pivot létrehozásakor az adatmodelt is be kell pipálnod ahhoz hogy elérd a DAX függvényeket.
2. Megtervezed a Pivot-odat.
3. A mezőválasztónál a fejlécen jobb klikk, majd új mező létrehozása, aminek a képlete esetemben:=CALCULATE(SUM(Range[Value]);FILTER(Range;Range[Date (Year)]=MAX(Range[Date (Year)])))-CALCULATE(SUM(Range[Value]);FILTER(Range;Range[Date (Year)]=MIN(Range[Date (Year)])))A mezőneveket a sajátod szerint add meg. A képlet feltételezi hogy csak 2 éved van, MIN/MAX.
4. Behúzód a helyére az új mezőt.
5. Csinosítgatsz.Alternatív megoldás:
Pivot melletti oszlopban képletekkel kiszámolod a különbséget. Itt figyelned kell arra, hogy a Pivot tud bővülni vagyis a fix hivatkozások miatt érdemes INDEX/MATCH és IFERROR-okat használni.Egy másik lapra kiteszed a Slicer-t és ott megint INDEX/MATCH párossal megjeleníted a végeredményt. Az eredeti lapot pedig elrejted.
Ezt hívják dashboard-nak. A lényeg a vizualizáció, a háttérszámítások egy "rejtett" helyen vannak, a fő lapon mindig csak a kívánt tartalom jelenik meg dinamikusan.
üdv
-
Mutt
senior tag
Annyi pontosítás, hogy ahogy nézem a sort könnyebben el lehet érni, vagyis a a kód rövidebb tud lenni.
illetve annyit finomítottam, hogy csak akkor frissít ha a linkedcell értéke nem jó.
Sub UpdateLinkedCells()
Dim sp As Shape
Dim rng As Range
Const sorEltol As Long = 0
Const oszlopEltol As Long = 7
For Each sp In ActiveSheet.Shapes
'az aktív lapon talált objektumok közül csak a jelölőnégyzeteket keressük meg
'hiba esetén menjünk tovább
On Error Resume Next
If sp.DrawingObject.progID Like "*CheckBox*" Then
'a jelőlőnégyzet a TopLeftCell.Column oszlopban található, a sort pedig TopLeftCell.Row adja meg
'a sor és oszlop azonosítókat csal akkor toljuk el a megadott értékkel ha ráférnek még a lapra
If sp.TopLeftCell.Row + sorEltol <= Rows.Count And sp.TopLeftCell.Column + oszlopEltol <= Columns.Count Then
Set rng = Cells(sp.TopLeftCell.Row + sorEltol, sp.TopLeftCell.Column + oszlopEltol)
'frissítsünk ha az új hely máshol van
If Intersect(rng, ActiveSheet.Range(sp.DrawingObject.LinkedCell)) Is Nothing Then
'mentsük át az új helyre az eddigi értéket
rng = ActiveSheet.Range(sp.DrawingObject.LinkedCell)
'töröljük a korábbi hely tartalmát
ActiveSheet.Range(sp.DrawingObject.LinkedCell).ClearContents
'linkeljük be az újat
sp.DrawingObject.LinkedCell = rng.Address
End If
End If
End If
On Error GoTo 0
Next sp
End Sub -
Mutt
senior tag
válasz
darvak
#44231
üzenetére
Szia,
...a Linkedcell cellákat beállítja az aktív munkafüzeten lévő összes beillesztett jelölőnégyzetre egységesen pl. 7 db cellával jobbra...
Próbáld ki a lenti kódot. A sorEltol és oszlopEltol állandókat változtatva tudod megadni, hogy mennyivel legyenek arrébb a kapcsolt cellák.
Sub UpdateLinkedCells()
Dim sp As Shape
Dim sor As Long
Dim rng As Range
Const sorEltol As Long = 0
Const oszlopEltol As Long = 7
For Each sp In ActiveSheet.Shapes
'az aktív lapon talált objektumok közül csak a jelölőnégyzeteket keressük meg
If sp.DrawingObject.progID Like "*CheckBox*" Then
'a jelőlőnégyzet a TopLeftCell.Column oszlopban található, de hogy melyik sorban azt
'csak a magassága alapján tudjuk megmondani
sor = getRow(sp.top + sp.Height / 2)
'ha megvannak sor és oszlop azonosítók, akkor toljuk el a megadott értékkel őket ha ráférnek még a lapra
If sor + sorEltol <= Rows.Count And sp.TopLeftCell.Column + oszlopEltol <= Columns.Count Then
Set rng = Cells(sor + sorEltol, sp.TopLeftCell.Column + oszlopEltol)
'mentsük át az új helyre az eddigi értéket
rng = Range(sp.DrawingObject.LinkedCell)
'töröljük a korrábi hely tartalmát
Range(sp.DrawingObject.LinkedCell).ClearContents
'linkeljük be az újat
sp.DrawingObject.LinkedCell = rng.Address
End If
End If
Next sp
End Sub
Function getRow(pos As Double) As Long
Dim c As Long
Dim h As Long
c = 0
h = 0
Do While pos > h
c = c + 1
h = h + ActiveSheet.Cells(c, 1).Height
Loop
getRow = c
End Functionüdv
-
Mutt
senior tag
válasz
csferke
#44210
üzenetére
Szia,
A Beviteli formon csak AC listából választasz, vagy van ott még több dolog is?
1. Ha nem, akkor jobb megoldás az Adatérvényesítés opciót használni.
2. Ha igen, akkor kell egy szövegdoboz a formra, ahova tudja a felhasználó a szöveget gépelni és ezek után lehet visszamenteni az összefúzött szöveget a lapra.üdv
-
Mutt
senior tag
válasz
bitpork
#44208
üzenetére
Szia,
Próbáld ki ezt az UDF-et:
Function RemainingDays(keydate As Date, Optional period As Long = 180, Optional maxdays As Long = 42) As Long
Dim wsRoutes As Worksheet
Dim c As Long
Dim ElapsedDays As Long
Dim LastRow As Long
Dim travelDays As Long
Set wsRoutes = ThisWorkbook.Sheets("Ark1")
With wsRoutes
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
Dim splitDate
Dim travelStart As Date
Dim travelEnd As Date
'loop thorugh travel dates to find matching records
For c = LastRow To 2 Step -2
'separate merged travel start & end dates
splitDate = Split(.Cells(c, "C"), "-")
'check only arrays with 2 elements
'split generates 0-based arrays
If UBound(splitDate) = 1 Then
'try to convert strings to date
travelStart = DateSerial(Right(Trim(splitDate(0)), 4), Mid(Trim(splitDate(0)), 4, 2), Left(Trim(splitDate(0)), 2))
travelEnd = DateSerial(Right(Trim(splitDate(1)), 4), Mid(Trim(splitDate(1)), 4, 2), Left(Trim(splitDate(1)), 2))
'find the row which has travel start earlier then given keydate
If travelStart <= keydate Then
'Debug.Print "KeyStart: " & keydate - period & " KeyEnd: " & keydate
If travelEnd >= keydate - period Then
travelDays = travelDays + .Cells(c, "L")
'Debug.Print vbTab & "Row: " & c & " TravelStart: " & travelStart & " TravelEnd: " & travelEnd & " Days: " & .Cells(c, "L")
End If
End If
End If
Next c
RemainingDays = maxdays - travelDays
End With
End FunctionHasználata pedig annyi, hogy megadsz neki egy dátumot és ő az Ark1 füzeten alulról felfelé haladva megkeresi, hogy az utazások alapján melyik sorok L-oszlopbában lévő számokat kell összegezni.
Ha nem egy mezőben lennének szövegként az utazási dátumok felsorolva, hanem különálló rekordonként akkor egy SZUMHATÖBB is elég lehet. Feltételezve, hogy a problémát jól értettem.
üdv
-
Mutt
senior tag
válasz
sutyesz96
#44204
üzenetére
Szia,
get.cell függvényt hogy tudom elérni magyar nyelvű excelben?
A GET.CELL egy Excel 4 makró függvény, magyar neve CELLÁT.VESZ
2 módon tudod elérni:1. Hozz létre egy Excel 4.0 makrólapot, majd ott normál módon tudod haználni.
2. Névkezelőben hozz létre egy új formulát,a képlete:
=CELLÁT.VESZ(20;!A1)
Ahol 20-as a képlet paramétere esemben, amely igazat ad vissza ha félkövér betűtípus van a cellában. A !A1 pedig egy relatív hivatkozást fog takarni, ha B1-en állva hozod létre a névkezelőben a képletet, akkor mindig a cellától balra lévő cellát fogja kiértékelni.B1-ben a képlet csak ennyi:
=getcell
üdv
-
Mutt
senior tag
válasz
benjoe1
#44180
üzenetére
Szia,
Makrómentesen 2 megoldás:
1. A Projektneveket egy Kimutatással soroltam fel alul.
2. B10-ben ez a csúnya tömbképlet van.=HA(A10<>"";SZÖVEGÖSSZEFŰZÉS(";";IGAZ;HA(ELTOLÁS($B$1;HOL.VAN($A10;$A:$A;0)-1;;;DARAB2($1:$1)-1)>0;ELTOLÁS($B$1;0;;;DARAB2($1:$1)-1);""));"")Kell hozzá Excel 2016-tól elérhető SZÖVEGÖSSZEFŰZÉS, a végén CSE-t kell nyomni. Másold le sokszor, hogyha új projekt jön akkor automatikusan megjelenjen. Ha az oszlopok száma nem bővül (vagyis nem hétről-hétre adjátok hozzá őket, akkor a második ELTOLÁST le lehet cserélni fix tartományra, pl. $B$1:$BA$1-re)
2. Excel 2010-től van Power Query, amiben kb. 10 lépés. A lényeg az ún. UNPIVOT (magyarul elemi értékre bontás).
üdv
-
Mutt
senior tag
válasz
csferke
#44196
üzenetére
Szia,
Plusz információt az InputBox, vagy jobb az Application.InbutBox metódussal tudsz bekérni. Az utóbbinál megadhatod, hogy milyen információt fogadjon el, pl. csak számokat, csak tartományt stb. A Type:=2 azt jelenti, hogy szöveget vár.
Ezek után tudod használni a munkalap Change eseménykezelőjét:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngValidation As Range
Dim szoveg
'ezen a területen van az adatérvényesítés
Set rngValidation = Range("A2:A5")
'a kód csak akkor fusson le, ha az adatérvényesítés területén módosítunk
If Not Intersect(Target, rngValidation) Is Nothing Then
'kikapcsoljuk az eseménykezelõt, így amikor módosítunk a területen
'nem fogja elindítani magát a kód újból
Application.EnableEvents = False
'kérjünk be vmilyen szöveget
szoveg = Application.InputBox("Szöveg", Title:="Infomáció", Type:=2)
'ha NEM nyomtak Mégsem-et, akkor füzzük a cella tartalmához a szöveget
If szoveg <> vbFalse Then
Target = Target & " " & szoveg
End If
'eseményeket mostantól újra nézzük
Application.EnableEvents = True
End If
End Subüdv
-
Mutt
senior tag
Szia,
Röviden: ...C$2
$100=igen
Ezt hogyan lenne elegáns módosítani, ha nekem a valóságban több ezer sorom van? Írjak be egy nagy számot, hogy mindig elég legyen?Ahogy írtad adj meg egy nagyobb számot, de óvatosan mert a túl sok soron történő számítás le fogja lassítani a munkafüzetedet! Tömbképletek lassabbak mint a hagyományos képletek, ezért csakis módjával velük.
Magyarázat a megadott képlethez, hogy könnyebben lehessen módosítani:
=HA(DARABTELI($C:$C;"igen")>=SOROK(E$1:E1);INDEX($A:$A;ÖSSZESÍT(15;6;SOR(A$2:A$100)/(C$2:C$100="igen");SOROK(E$1:E1)));"")1. A magja a SOR(A$2:A$100)/(C$2:C$100="igen") rész, ami megnézi hogy az adott sorban a C-oszlopban IGEN van-e vagy sem. Ha az akkor visszakapjuk a sor számát, ha nem akkor pedig a 0-val osztás eredményeként hibát. Ez tömbfüggvény, vagyis egyszerre több eredményt fog visszadni. pl. {2;#ZÉRÓOSZTÓ;#ZÉRÓOSZTÓ;5;6}
2. Ebből a tömbből kellenek egymás után a számok, amit a KICSI függvény add meg, de a gond hogy az a függvény nem tud mit kezdeni azokkal a tömbökkel, amelyekben van hibás érték.
Itt fog besegíteni az ÖSSZESÍT függvény, ami több függvényt tud meghívni, miközben képes a hibákat (amiket szándékosan vétünk az első lépésben) kiszűrni. A 15-ös kód a KICSI függvényt jelenti, a 6-os pedig hogy rejtse el a hibákat.3. A KICSI-nek meg kell mondani, hogy hányadik elemet kérjük vissza a tömbből. A SOROK(E$1:E1) ezt fogja elérni, mert ahogy másoljuk lefelé a második része növekedni fog (mivel ott nem lett rögzítve a sor).
4. A végén az egész egy HA-ba van téve, hogy ne legyen hibaüzenet ha nincs mit megjeleníteni.
Ezt lehetne HAHIBA függvénnyel is elérni, de az mindig előbb kiértékeli a függvényt és az alapján dönti el hogy melyik ágon menjen tovább. Mivel a tömbképletek elég erőforrás igényesek tudnak lenni, ezért azoknál inkább kerüljük. Itt is azt tettem; lecseréltem egy gyorsabban kiszámolható függvényre.Vége.
+1 A képletnek annyi a hibája, hogy az ismétlődéseket nem tudja kiszűrni. Ahhoz kellene egy segédoszlop a régebbi Excelhez.
üdv
-
Mutt
senior tag
Szia,
Első feladatra (legalább 1 igen) a megoldások:
1. A C-oszlopot tedd be a Kimutatás szűrőjébe.
2. Ha Office 365-öd van, akkor ez a képlet:
=EGYEDI(SZŰRŐ(A:A;C:C="igen"))
3. Excel 2010-től pedig tömbképlet, amit E1-be írj be majd húzd le a képletet:=HA(DARABTELI($C:$C;"igen")>=SOROK(E$1:E1);INDEX($A:$A;ÖSSZESÍT(15;6;SOR(A$2:A$100)/(C$2:C$100="igen");SOROK(E$1:E1)));"")Második feladatra, hogy csak akkor ha mindegyik igen.
Kell egz segédszlop. Én D-be tettem a képlete pedig ez:=DARABHATÖBB(A:A;A2;C:C;"nem")>0
Ezek után jöhet a Kimutatás, de mostmár 2 szűrőd van: az igen és segédoszlop (ami HAMIS, mert azt keressük hogy volt-e NEM a betűnél bárhol említve).
üdv
-
Mutt
senior tag
-
-
Mutt
senior tag
-
Mutt
senior tag
válasz
zsolti_20
#44128
üzenetére
Szia,
A körlevél készítő újabb lapot fog létrehozni a többi címnek a sablon szerint. Vagyis ha 14-nél több címed van, akkor egy újabb lapon folytatja újabb 14 címmel és így tovább.
A kész eredményt nyomtatás előtt le tudod ellenőrízni, hogy legyen lehetőség javításra. Ne a nyomtatást válaszd, hanem az új dokumentum késztését. Ha az jó akkor küldheted azt nyomtatásra.
üdv
-
#44127
Mutt
senior tag
szricsi_0917
#44126
Mutt
senior tag
válasz
szricsi_0917
#44126
üzenetére
Szia,
A tömböknek a szorzatösszegben azonos méretúnek kell lennie, a képleted szerint is azonos magasságúak (2-es sortól a lastrow2-ig).
Próbáld ki így a képlet létrehozását:
Dim rng As Range
Dim keplet as String
For i = 2 To lastrow
For a = 10 To 21
Set rng = szerviz.Cells(2, a).Resize(lastrow2 - 1)
keplet = "=SUMPRODUCT(MAX((Szerviznyilvántartó!$B$2:$B$" & lastrow2 & "=$B" & i & ")*(Szerviznyilvántartó!" & rng.Address & "=""Motorolajcsere"")*Szerviznyilvántartó!$G$2:$G$" & lastrow2 & "))"
auto.Range("V" & i) = Evaluate(keplet)A kódrészlet alapján ami furcsa, hogy 11 oszlop 11 eredményét kiszámolod és mindig a V oszlopba kiírod, így valójában mindig csak az utolsó oszlop eredménye marad meg.
Ha gond van, akkor érdemes feldobnod egy minta fájlt vhova a pontos feladattal és itt többen is tudnak jobb kódot majd javasolni.
üdv
-
#44122
Mutt
senior tag
szricsi_0917
#44114
Mutt
senior tag
válasz
szricsi_0917
#44114
üzenetére
Szia,
Ez nem fog menni, mert a VBA-s változat máshogy műkődik.
Alternatív megoldások:
1. Előbb képlettel kiszámoltatod az eredményt, majd értékként beilleszted
With Range("U2:U" & lastrow)
.FormulaR1C1 = "=SUMPRODUCT(MAX((RC[-19]:R[998]C[-19]=RC[-19])*RC[-14]:R[998]C[-14]))"
.Value = .Value
End With2. Evaluate függvénybe teszed a képletet és az eredményt kiíratod a cellába
For i = 2 To lastrow
keplet = "=SUMPRODUCT(MAX((B2:B1000=B" & i & ")*G2:G1000))"
Range("U" & i) = Evaluate(keplet)
Next i3. Ahogy nézem csak a maximumot keresed a G-oszlopban minden B-ben lévő értékhez. Ezt Kimutatással, AB.MAX vagy MAXHA függvényekkel is el lehet érni.
üdv
-
Mutt
senior tag
válasz
p5quser
#44115
üzenetére
Szia,
A Split egy eredménytömböt próbál létrehozni, aminek az elemszáma a megtalált elválasztó karakterek száma alapján változik. Ha nem találja meg a karaktert, akkor 1 elemű lesz a tömb, a bemeneti értékkel az első elemben.
A tömb elemszámát az UBOUND() függvény adja meg.
Mielőtt a 3-ik elemet keresnéd meg kell nézned, hogy van-e egyáltalán?If UBound(spl) > 2 then
... 3-ik elemes keresésed
End ifHa jól értelmezem az eredeti felvetésedet, akkor ez a kód jobb eredményt fog adni mint a mostani.
Sub Kereses()
Dim rngSearch As Range 'ez a B oszlop
Dim txSearch As Range 'ez a B oszlop éppen vizsgált cellája lesz
Dim arrWhat() 'ez a G oszlop
Dim txWhat As Variant 'a splittel ide szedjük szét fenti cella tartalmát
Dim match As Long 'találatok számolása
Dim bestmatch As Long 'legtöbb találat
Dim bestWhat As Long 'legtöbb találatot adó keresés pozíciója
Dim i As Long
Dim j As Long
With ActiveSheet
'memóriában tárolt tömbe töltjük a keresendõ kifejezések listáját
'Transpose 1-es index-szel induló tömböt hoz létre
arrWhat = Application.Transpose(.Range("G2:G180"))
Set rngSearch = .Range("B1:B" & .Range("B1").End(xlDown).Row)
For Each txSearch In rngSearch
bestmatch = 0
bestWhat = 0
For i = 1 To UBound(arrWhat)
'keresendõ szavak létrehozása
txWhat = Split(arrWhat(i), " ")
If IsArray(txWhat) Then
match = 0
'Split mindig 0-ás index-szel hozza létre a tömböt
For j = 0 To UBound(txWhat)
match = match - (InStr(1, UCase(txSearch), UCase(txWhat(j))) > 0)
Next j
'ha találtunk több egyezést a korábbiaknál, akkor jegyezzük meg
If match > bestmatch Then
bestmatch = match
bestWhat = i
End If
End If
Next i
'mielõtt új cellára mennénk a C-D oszlopban írjuk ki hogy mi a legjobb egyezésünk
If bestWhat > 0 Then
txSearch.Offset(, 1) = bestmatch
txSearch.Offset(, 2) = arrWhat(bestWhat)
End If
Next txSearch
End With
End Sub
üdv
-
Mutt
senior tag
Szia,
Excel 2010-től van Power Query, ahol 5+1 lépés az egész:
0. Alakítsd át a tartomány táblázattá (nem kötelező ha másik fájlból akarsz dolgozni)
1. Olvasd be az adatsort
2. Add Column -> Index column
3. Kijelölöd az újonnan létrejött oszlopot, majd Transform -> Unpivot -> Unpivot by other columns
4. Majd sorba rendezed előbb a fejléc alapján, majd az index alapján.
5. Törlöd a felesleges 2 oszlopot.
6. Visszatöltöd az eredményt Excelbe. Home -> Close and load -> .. to..üdv
-
Mutt
senior tag
válasz
mulli86
#44069
üzenetére
Szia,
A feltett fájlt megnézve makró mentesen Power Query-vel simán megoldható.
Importálni kell a hibakódokat és a CSV fájlt, majd annyiszor kell lekérdezések összefűzését használni ahány oszlopban akarod a hibakódot keresni. Kibontás és utána egy egyéni oszlopban megnézni, hogy hány esetben lett az eredmény üres (null). Ahol csak null volt azok hibamentes sorok vagyis dobhatók és a maradékot lehet betölteni egy új munkalapra.
Kb. 30 perc alatt megvan a Power Query aki jártas már benne és utána újrahasznosítható más fájlokkal.
Természetesen a Power Query-t tanulni kell, ami idő, de Youtube-on van jó sok segítség.4 oszlop csekkolása kb. így néz ki.
üdv
-
Mutt
senior tag
Szia,
Delila válasza mellett itt egy UDF, amit feltételes formázásban használhatsz.
Function CellaSzinek(rng As Range, Optional szinkod As String, Optional mutasd As Boolean)
'cella színének változása nem eredményezi a képletek újra kiértekélését
Application.Volatile
Dim cella As Range
Dim szin As Long
If mutasd Then
CellaSzinek = CStr(rng.Range("A1").Interior.Color)
Else
CellaSzinek = True 'megelőlegezzük hogy a tartomány színe azonos
'ha nincs meg adva a viszonyításhoz színkód, akkor használjuk a tartomány elsõ cellájának színét
If szinkod = "" Then
szin = rng.Range("A1").Interior.Color
Else
szin = CLng(szinkod)
End If
For Each cella In rng
If cella.Interior.Color <> szin Then 'ha eltérés van akkor kilépünk a ciklusból
CellaSzinek = False
Exit For
End If
Next cella
End If
End FunctionTúlzásba vittem, mert 3 módon lehet használni.
- Ha csak 1 paramétert (a vizsgálandó tartományt) adsz meg, akkor a tartomány első cellájánák színéhez hasonlítja a többit. Ha azonosak, akkor IGAZ eredményt ad vissza.
- Ha 2 paramétert adsz meg, ahol a második az adott színkód macskakörmök között amelyet keresel, akkor pedig IGAZ lesz az eredmény, ha a tartományban a cellák színe azonos a megadottal.
- Ha a harmadik paraméter IGAZ, akkor pedig a kijelölt tartomány első cellájának színkódját kapod meg.üdv
-
Mutt
senior tag
válasz
mulli86
#44058
üzenetére
Szia,
1. lastsor típusa Long legyen, mert az integer csak 32 ezer sorral fog bírni.
2. A hibaname és oszlopnumber a két for cikluson belül van inicilaziálva ami nem jó, hozd ki őket a for-ok elé.
3. Variant a hibaname típusa, de közben a cella értékét ellenőrzöd. Jobb lenne egy specifikus típust használnod pl. Double ha számok érdekelnek, vagy String ha szöveg.
4. Ha sokat dolgozol egy lapon akkor érdemes With ... End With-et használnod.
pl.With Worksheets(1)
lastsor = .Range("A5").End(xlDown).Row
For x ...
For y ...
hibaname = .Cells(5, y)
For p ...
If hibaname = Sheets(3).Cells(1,p) then
....
End If
Next p
Next y
Next x
End With5. A GoTo rész biztos hogy kell? Miért nem teszed az IF-be az ottani dolgokat?
6. Sokat gyorsít a "villódzás" kikapcsolása.
Application.ScreenUpdating = False a for ciklusok elé, majd = True a legvégén.üdv
-
Mutt
senior tag
válasz
mulli86
#43960
üzenetére
Szia,
.. több csv (excel alapú) adatbázist. 59 excel, excelenként változó mennyiségű sor, az oszlopok száma megegyezik és az oszlopokon belül található attribútumok is.
Ha Excel 2010 vagy frissebbed van, akkor Power Query fog tudni neked segíteni.
Youtube-on van pár video róla, pl. több fájl fedolgozása.Ha kell segítség a feladathoz dobd fel a fórumra.
üdv
-
Mutt
senior tag
válasz
Fferi50
#43920
üzenetére
FFeri-vel értek egyet.
Én is vmi olyat javasolnék, hogy az egyik csapatban a legerősebb + leggyengébb játékos, majd a másik csapatba a második legerősebb + második leggyengébb kerülni. Ezt változtatva kerülnének az emberek szétosztásra, így a pontszámok egymást kompenzálni próbálják csapaton belül. Viszont a szórás innentől pocsék lesz, hiszen a legjobb és a legrosszabb egy csapatban lesz.
Képletnek a NAGY függvényt tudom javasolni, ha nincs ismétlődés a pontszámokban.
Csináltam egy ilyen táblázatot, ahol legkívül a kiválasztási körök vannak. Beljebb hogy hanyadik ember kell nekünk a listából, belül pedig a NAGY függvény van, ami a hanyadik emberre mutat.
üdv
-
Mutt
senior tag
válasz
Zenebohoc
#43892
üzenetére
Szia,
Ez alapján csak tippelni lehet mi a gondja annak az 1 db fájlnak.
1. Más makrót tartalmaz-e az adott fájl, különös tekintettel Worksheet eventeket futtattót?
2. Melyi az utolsó használt cella a fájlban? F5-öt nyomj és Irányított -> Utolsó cellát válaszd ki
3. Mentés másként egy új fájlba után az új fájl is csinálja a problémát?üdv
-
Mutt
senior tag
Szia,
lappy által linkelt oldalon ott a megoldás, Power Query használata (Excel 2010-hez még külön kell telepíteni).
Lépések
1. Adatok -> Adatok beolvasása -> Más forrásból -> Weblapról
2. https://www.mnb.hu/arfolyamok URL megadása
3. Első táblázat kijelölése, majd alul Adatok átalakítása gombra kattintasz
4. Pénznem oszlopban szűrés USD-re.
5. Forint érték fejlécén jobb klikk és a többi oszlop tőrlése
6. Bezárás betöltés adott helyre, majd válaszd a táblázatot és a helyet ahol szeretnéd
7. Opcionális: Adatok fülön Tulajdonságok majd a lekérdezés tulajdonságainál az Adatfrissítés fájl megnyitásakor.
üdv
-
Mutt
senior tag
Szia,
SZUMHA és SZORAZTÖSSZEG vagy 3x SZORZATÖSSZEG függvény tud segíteni:
=SZUMHA(A:A;1;C:C)+SZUMHA(A:A;2;D:D)+SZORZATÖSSZEG((A2:A20=3)*(C2:C20)*(D2:D20))
=SZORZATÖSSZEG((A2:A20=1)*(C2:C20))+SZORZATÖSSZEG((A2:A20=2)*(D2:D20))+SZORZATÖSSZEG((A2:A20=3)*(C2:C20)*(D2:D20))Az eredeti felvetésedhez is vmi hasonló lesz a megoldás.
üdv
-
#43878
Mutt
senior tag
pero19910606
#43868
Mutt
senior tag
válasz
pero19910606
#43868
üzenetére
Szia,
Az elmúlt 30 napot kell megjelenítenem értékben...
Grafikonokon lehet dinamikus tartományokat használni, amelyet az ELTOLÁS függvénnyel lehet előállítani.
Elnézést az angolnyelvű képernyőmentésekért.
A lépések:
1. Hogy könnyebben átlátható legyen az összes lépés van pár segédcellám.
A G1-es cellában egy=MA()függvény van.
A G2-ben 30 van, aminek adtam egy speciális számformátumot (ami simán elhagyható). Ha ezt a számot változtatom, akkor minden dinamikusan fog a grafikonon változni.
A G3-ban megkeressük a sort, amely a 30 nappal ezelőtti dátumhoz legközelebbi dátumot tartalmazza. Képlete:=HOL.VAN(G1-G2;A:A;1)
A G4-ben megkeressük, hol van az utolsó sorunk. Képlete:=DARAB2(A:A)2. A névkezelőben három dinamikus tartományt hozzál létre, a dátumhoz, a mennyiséghez és az értékhez. A példában én xAxis, yAxis1, yAxis2 neveket adtam nekik és a munkalaphoz rendeltem őket.
A képletek pedig:=ELTOLÁS(Sheet1!$A$1;Sheet1!$G$3;;Sheet1!$G$4-Sheet1!$G$3)
=ELTOLÁS(Sheet1!$B$1;Sheet1!$G$3;;Sheet1!$G$4-Sheet1!$G$3)
=ELTOLÁS(Sheet1!$C$1;Sheet1!$G$3;;Sheet1!$G$4-Sheet1!$G$3)3. Rakd össze a grafikonodat, majd a Nézet -> Adatok megadása alatt válaszd ki az adatsorodat és kattints a Szerkesztésre.
Forrásnak a 2-es lépésben használt neveket kell megadnod munkalap névvel együtt! Esetemben pl. Sheet1!yAxis1
üdv -
Mutt
senior tag
válasz
RedHarlow
#43801
üzenetére
Szia,
Ha tömbfüggvény is játszik, akkor SZORZATÖSSZEG is tud segíteni.
O3-ban a képlet:
=HAHIBA((SZORZATÖSSZEG(($B$2:$M$2=O$2)*($B3:$M3>0)*($B3:$M3))/SZORZATÖSSZEG(($B$2:$M$2=O$2)*($B3:$M3>0))&O$2);0)B-től M-ig terjedő oszlopokat nézi csak, ezt majd bővítened kell.
A végét pedig egy Excel 2016-ban lévő függvény, a SZÖVEGÖSSZEFŰZÉS adja meg.
=SZÖVEGÖSSZEFŰZÉS (KARAKTER(10);IGAZ;HA(O3:Q3>0;O3:Q3;""))Ezt Ctrl+Shift+Enter-el kell majd bevinni.
üdv
-
Mutt
senior tag
válasz
Fferi50
#43769
üzenetére
A lapnevekhez nem kell VBA.
Alternatív megoldások:
1. Excel4 makró használata
Névkezelőben felvenni egy új változót, aminek a képlete:=MUNKAFÜZETET.VESZ(1)Ahol az 1-es paraméter azt mondja meg, hogy a munkafüzetben található lapok nevét függőleges tömbként adja vissza. Ha vízszintesen kell akkor 3 a paraméter. Ha csak a lapok száma kell akkor pedig 4.
Angol Excel esetén GET.WORKBOOK a függvény neve.Ezek után INDEX függvénnyel megkaphatók a lapok nevei.
Ha nem kell a munkafüzet neve, csak a lapnevek, akkor SZÖVEG.KERES és KÖZÉP függvényekkel szépen kivágható a lényeg.
2. Power Query-ben Adatok beolvasása -> Fájlból -> Munkafüzetből ahol megadjuk az aktuális munkafüzet helyét. A többi már csak pár kattintás.
üdv
-
Mutt
senior tag
válasz
Clark_1
#43739
üzenetére
Szia,
Power Query-t (Excel 2010-től) tudom javasolni, pár tutorial videot a Youtubon megnézel és te is el tudsz vele indulni azonban...
1. Én egy nagy táblában (adatbázisban) gondolkodnék, ahol lenne egy plusz oszlop (mező) ami azonosítót (pl. a hét számát + év) tartalmazná. Innentől könnyedén lehetne kikeresni a legutolsó hét adatait összehasonlításhoz (nem kell kézzel kijelölni tud magától menni).
2. a kiemelést feltételes formázással lehet megoldani majd, de ehhez kell majd egy mezőt létrehozni ami tartalmazza hogy mi a változás (pl. ha "törölt" akkor legyen más a betűtípus stb).
üdv
-
Mutt
senior tag
Szia,
Mivel új Exceled van én is bedobom a megoldásomat, ami az új makrónyelvet (OfficeScript) használja.
Telepítened kell az ingyenes Script Lab bővítményt az Office-bővítmények áruházból. Majd azt elindítva importáld be a kódot ezen linkről:
https://gist.github.com/viszi/0a28b84dbece74b23219923a3e963b64Utána pedig mehet a futtatás.
Előnye, hogy a munkafüzet továbbra is makrómentes maradhat, mivel a makró nem kerül oda be.
Hátránya, hogy még nekem is tanulnom kell az új nyelvezetet.üdv
-
Mutt
senior tag
válasz
zsolti_20
#43699
üzenetére
Szia,
Ha a lent látható eredmény tetszik, akkor Power Query-ben ezek a lépések kellenek.
1. Adatsort alakítsuk táblázattá, én az "orders" nevet adtam neki.
2. Adatsoron állva Adatok -> Adatok beolvasása -> Táblázatból/tartományból megnyitja a Power Query szerkesztőt.
3. Location oszlopon állva használjuk a növekvő sorbarendezést.
4. Kezdőlap -> Csoportosítási szempontokkal csináljunk egy ilyet.
5. Egy új egyéni oszlopot adjunk hozzá, hogy lássuk melyik rendelés mely helyeken található.
6. Egy másik egyéni oszlopban megszámoljuk, hogy rendelések hány helyre oszlanak el.
7. Ezen elemszám alapján csökkenő sorrendbe rendezzük a táblázatot, ezzel a végső eredményünkben előrébb lesznek azok a rendelések amelyek több helyen vannak.
8. A location-ben lévő listát ki kell nyernünk (én hülye módon elemek nevet adtam ennek az oszlopnak de a lényeg, hogy itt a helyek vannak).
Az eredmény vesszővel elválasztva ílyen lesz:
9. Most megint csoportosítunk, hogy az azonos helyen lévő rendeléseket tudjuk kezelni.
10. Megkapjuk, hogy hány csoportra oszthatók a rendelések. A következő két lépésben nevet adunk a csoportoknak. Előbb az Oszlop hozzáadása -> Indexoszlop -> 1-től indul opciót használjuk.
11. Majd egy új egyéni oszloppal eléírjuk, hogy "Group ". Itt annyi kavar van, hogy számot szöveggé kell alakítani.
12. Jobb klikk ezen az oszlopon és mozgassuk legelőre.
13. Van két felesleges oszlopunk (az index és a location-t tartalmazó), ezeket töröld.
14. Kell egy újabb egyéni oszlop a rendelési számok duplikáció mentes megjelenítéséhez.
15. Itt megint vesszővel elválasztva írassuk ki az értékeket.
16. Opcionális lépés: ahhoz hogy a rendelési számok sorba legyenek a szerkesztőlécen a képletbe List.Sort() függvényt kell beírni (a sárga rész).
17. Az elemszám oszlop jobb sarkába kibontés az Adatok-at válaszd.
18. Az adatok oszlop jobb sarkában megint kibontás és most SKU, qty, location oszlopok kellenek.
19. Most már csak összegezni kell a mennyiségeket. Jelöld ki a qty kivételével az oszlopokat és használd a csoportosítást.
20. Rendezd az oszlopok sorrendjét tetszés szerint.
21. Lehet a sorokat is rendezni tetszés szerint.
22. Kezdőlapon betöltés adott helyre az utolsó lépés.üdv
-
Mutt
senior tag
válasz
zeeland66
#43681
üzenetére
Szia,
Röviden: inkább a C-oszlopot kellene összesíteni a D-oszlopban. A minta alapján a D3-ba a
=SZUM(C$2:C3)tűník logikusnak.Hosszabban: Kimutatást (Pivot) lenne célszerú használnod.
Még hosszabban: adatbázist akarsz egy táblázatkezelővel menedzselni, ami hosszútávon nem lesz jó. Adatvesztés mellett a bővíthetőség és riportálás fog gondot okozni, cserébe mondjuk tudsz szép grafikonokat és felületet csinálni.
üdv
-
-
Mutt
senior tag
válasz
dave0825
#43624
üzenetére
Szia,
A B:C segédoszlopokból hiányoznak a képletek a 101-es sortól.
Ha nem akarsz segédoszlopokkal jatszánai, akkor ezek a lehetőségek vannak:
1. Office365-ben van UNIQUE(EGYEDI) függvény az egyedi nevek előállításához.
2. Pivot (Kimutatás) is tud segíteni. Ez nem frissül automatikusan, de be lehet állítani, hogy a fájl megnyitásakor frissüljön.
3. Tömbfüggvénnyt is lehet használni.
üdv
-
-
Mutt
senior tag
válasz
zsolti_20
#43541
üzenetére
Szia,
Ahhoz hogy kizárjuk azokat a csomagokat, amelyek olyan tételeket is tartalmaznak amelyek minket nem érdekelnek a korábban megadott lépéseknél a 9-estől kezdődően kell változtatnunk.
1-8. lépések a 43537-es komment alapján
9. A kapcsolat típusa Left Outer legyen.
10. Ezek után kell egy Add Column -> Custom column, aminek ez a képlete:
=List.Count([parts][Column1])
Az új "Matchings" column azt fogja majd egy összegzés után megadni, hogy a csomagban hány olyan tétel van ami érdekel minket.
11. Jöhet az összegzés a Home->Group by-al
Ahogy láthatod a második összesítő függvénnyel megnézzük hogy hány tétel van a csomagban amire szükséged van. A harmadik függvény pedig megmondja, hogy összesen hány fajta tétel volt a csomagban.
Ahol a kettő nem lesz azonos azok a csomagok nem kellenek. Ez lesz a következő lépés.12. Add column->Custom -ot használjuk megintm a képlet ennyi:
=[Matches]=[TotalLines]
13. Ahol az új oszlopban TRUE van azok kellenek. Oszlop jobb felső sarkában lévő szűrővel állítsuk be ezt.
14. Ha nem kellenek az alkatrészek nevei a csomagból felsorolva, akkor ugord át ezt a lépést.
Add column -> Custom -ot használjuk megint, a képlet pedig:=[Count][SKU]
A kapott oszlop jobb sarkába kattintva Extract values és válassz egy elválasztó jelet.
15. Ahhoz hogy megkapjuk a mennyiségeket pedig megint egy új oszlop kell, aminek ez lesz a képlete:
=List.Sum([Count][QTY])
16. Törlöd a felesleges oszlopokat a riportból és Home->Close&Load->Close&Load to.. -val betöltöd Excelbe.
üdv
-
Mutt
senior tag
válasz
zsolti_20
#43529
üzenetére
Szia,
Ha Excel 2010 vagy újabbad van akkor van Power Query, amivel megoldható a dolog.
(Excel 2010-hez külön kell telepíteni az MS oldaláról.)Lépések:
1. Az első munkalapon a rendelés listát táblázattá alakítottam (előnye, hogy a mérete automatikusan változik amikor bemásolod a csomag adatokat). Én ezt a táblát "orders"-nek neveztem el, de nincs jelentősége.
2. A második munkalapon az A1 celléba beírtam a vesszővel felsorolt alkatrész listá és a cellának adtam egy nevet ("parts").
3. Maradj ezen a cellán és Data fülön "From table/range" (magyarul Adatok->Táblázatból/tartományból) opcióval megfog nyilni a Power Query szerkesztő.
4. Jobb oldalt az Applied Steps (Alkalamazott lépések) alatt lesznek felesleges lépések, töröld ki őket, csak a Source maradjon.
5. Transform -> Split column -> By delimiter-t (Átalakítás->Oszlop felosztása->Elválasztó alapján) indítsd el
6.Állítsd be, hogy vessző alapján szedje szét az adatokat és hogy sorokba tegye őket.
7. Home -> Close & Load -> Close & Load to .. (Kezdőlap->Bezárás&betöltés->..adott helyre) parancsot használd és a megjelenő ablakbak az Only create connection-t (Csak kapcsolat létrehozása).
8. Menj át az első lapra, ahol a csomag adatok vannak és állj rá az adatsorra, majd Data -> From table/range-t (Adatok->Táblázatból/tartományból) indítsd.
9. Home -> Merge queries -> Merge queries (Kezdőlap->Lekérdezések egyesítése) parancsot indítsd. Ez fel fog dobni egy ablakot, ahol látni fogod a csomagok listáját, alatt lesz egy legördülő listából válaszd ki a korábbi lépésekben létrehozott listát. A két listában a közös elem az alkatrészek, ezeket az oszlopokat jelöld ki. Majd alul a kapcsolat típusánál Inner Join (Belső) legyen. Ezt kellene látnod.
OK-t nyomva megkapod az első lapon lévő listából azokoat a sorokat, amelyek a második lapon szerepelnek.Már csak ki kell venni a duplikációkat.
10. Jelöld ki az "order number" oszlopot és Home -> Group by-t (Kezdőlap->Csoportosítási szempont) indítsd el. Majd állítsd be így.
A Count - All Rows páros csak akkor kell, ha érdekel a következő lépés amiben kiíratjuk hogy az adott csomagból téged mely alkatrészek érdekelnek.
11. Add column -> Custom column-t (Oszlop hozzáadása->Egyéni oszlop) indítsd el és töltsd ki így:
A képlet
=[Count][SKU]azt mondja meg a Power Querynek, hogy a Count nevezetű táblából (ezt az előző lépéssel hoztuk létre az SKU fejlécű oszlop adatati írja be az új oszlopba).12. Az újonnan létrehozott oszlopban jobb felső sarkában lévő gombra kattints és használd az Extract Values-t (Értékek kinyerése), majd válaszd ki hogy mi legyen az elválasztó elem a felsorolások között.
13. Töröld a Count oszlopot.
14. Home -> Close & Load -> Close & Load to .. jön megint, de most válaszd a Table opciót és add meg hogy hova töltödjön a végeredmény.
A sok lépés azért volt, mert ha módosítod bármelyik adatsort, akkor elég csak a Data -> Refresh all (Adatok->Összes frissítése) gombra kattintanod és előáll az új listád makrómentesen.
üdv
-
Mutt
senior tag
válasz
Fferi50
#43425
üzenetére
Szia,
A pontosvesszők mint elválasztók.... azok úgy tudom, csak magyarban működnek...
Nem csak a magyarban, hanem többnyire az európai változatokban. Egész pontosan a Windows Területi beállításai alatt található lista elválasztót használja mindig az Excel. A képletek nyelve pedig a telepített nyelvicsomagok függvényében változtatható.
üdv
-
Mutt
senior tag
válasz
bozsozso
#43377
üzenetére
Szia,
Ha használhatunk egy segédoszlopot, akkor FKERES és közelítő találatot javaslom.
Ha nem akkor BAL és JOBB függvények egymásbaágyazása.
A fenti példán az F:G oszlopban van a segítség. F1-ben 0-t írtam és átállítottam idő formátumra a cellát, F2-ben pedig a képlet
=F1+1/48. G1-ben ez a képlet van:=SZÖVEG(F1;"[ó]:pp")&"-"&SZÖVEG(F2;"[ó]:pp")Ezek után az eredeti értéket (pl. 534) kell idővé alakítanunk, az utolsó két karakter lesz mindig a perc, ami előtte marad az pedig az óra. Ennek a képlete:
=IDŐ(BAL(JOBB("0"&A2;4);2);JOBB(A2;2);0)Ezt berakva egy FKERES-be meglesz az eredmény:
=FKERES(IDŐ(BAL(JOBB("0"&A2;4);2);JOBB(A2;2);0);$F:$G;2)Ha nem játszik a segédoszlop, akkor pedig:
=--BAL(JOBB("0"&A2;4);2)&":"&HA(--JOBB(A2;2)<30;"00";"30")&"-"&--BAL(JOBB("0"&A2;4);2)+1&":"&HA(--JOBB(A2;2)<30;"30";"00")üdv
-
#43373
Mutt
senior tag
dellfanboy
#43371
Mutt
senior tag
válasz
dellfanboy
#43371
üzenetére
Szia,
Kell egy segédoszlop, ahova véletlenszámokat generálsz. Majd pedig a RANK.EGY és az INDEX függvény.
A fenti példán E-oszlopban a képlet
=VÉL()
A C-oszlopban pedig=INDEX($A$2:$A$4;RANG.EGY(E2;$E$2:$E$4))üdv
-
Mutt
senior tag
válasz
Jarod1
#43355
üzenetére
Szia,
Excel 2010-től van Power Query, ami sokkal könnyebbé teszi az adatbeolvasást makró nélkül.
Adatok fülön a beolvasás weblapról opció tud segíteni.Ha makróval akarod frissíteni, akkor pedig ezt kell csak a kódodba írnod.
ActiveWorkbook.Connections(<lekérdezés neve>).Refreshüdv
-
Mutt
senior tag
Szia,
...fkres fv már balra is tud keresni?
Alapból nem tud balra keresni, de tömb függvénnyel el lehet érni, hogy bal oldalról is adjon találatot.
A fenti példához a CSE képlet:
=FKERES(E2;VÁLASZT({1\2};B2:B6;A2:A6);2;0)Office 365-ben már van XKERES, ami tud balra is keresni.
üdv
-
Mutt
senior tag
válasz
ROBOTER
#43335
üzenetére
Szia,
Több infó kellene nekünk, mert nehéz kitalálni hogy miért nem fut le a kód.
Watch ablakba be kellene húzni a változókat és megnézni, hogy melyik nem kap értéket, lehet hogy nem is a tartományokkal van gond.
Két ötlet:
1. Spill funkció használata (itt feltételezem hogy az első sor fejléc. Ha kibontás hiba van, akkor foglalt a céltartományRange("KÖLTSÉG").Cells(2, 1).Formula2 = "=EGYSÉGÁR*MENNYISÉG"2. INDEX függvénnyel próbáld meg a megfelelő elemet kinyerni:
Range("KÖLTSÉG").Cells(rowID, 1).Formula = "=INDEX(EGYSÉGÁR*MENNYISÉG,)"üdv
-
Mutt
senior tag
válasz
ROBOTER
#43293
üzenetére
Szia,
Azt még nem értem, hogy honnan veszi, hogy az adott sorban hajtsa végre a műveletet, amikor sor-koordináta nincs megadva.
Implicit intersectionnak hívják, ha nem adod meg az elemet, akkor az Excel feltételezi hogy az aktuális sorra/oszloppal megegyező elemet akarod visszakapni.
@ jelet tesz az új név elé!
Office365-öt használsz, ahol vannak már spill (kitöltő) függvények, vagyis ha a képlet végeredménye nem fér be 1 db cellába, akkor több cellába fogja kiírni az eredményeket. A @ jel azt mondja meg az új Excelben, hogy ne legyen több cellás kitöltés, az implicit intersection szerinti eredményt adja vissza. Ha azt akarod, hogy műkődjön a spill, akkor a Formula2 tulajdonságba kell a képletet írnod.
üdv
-
Mutt
senior tag
Másik fórumon a felhasználó kapott egy Excel VBA kódot, ami asztali környezetben jól működik, de online felületen már nem. A probléma azon túl, hogy a VBA nem fog online futni, hogy a felhasználó MS Excel helyett Google Spreadsheet-et használ, aminek más a makró nyelve.
A feladat az, hogy automatikusan kell azokat az oszlopokat eltüntetni, ahol az oszlop második sorában található dátum már elmúlt. A feladat annyival bonyolultabb, hogy egyes cellák össze vannak vonva, így nem csak egy oszlopot, hanem egyszerre kettőt kell elrejteni.
A problémához ez a makró használható:
//onOpen esemény gondoskodik arról, hogy a munkafüzet megnyitásakor adott parancs lefusson
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
//a makró csak az adott nevű munkalap megnyitásakor fog lefutni
if (spreadsheet.getSheetName() == "Munkalap1") {
autohide();
}
};
//ez a makró fogja az adott oszlopokat elrejteni
function autohide() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
//aktuális idő változóba mentése
var currentdate = new Date();
//az aktív munkalap második sorából az adatok változóba mentése
var ranges = spreadsheet.getRange("2:2");
var values = ranges.getValues();
//a második sorban lévő adatokon egyesével végigmegyünk
//ha a dátum régebbi mint az aktuális és az oszlop még nincs elrejtve, akkor elrejtjük az oszlopot és a mellete lévőt is
for (var i = 0; i < ranges.getNumColumns(); i++) {
if (values[0][i] < currentdate & values[0][i].toString().length > 0 & !spreadsheet.isColumnHiddenByUser(i+1)) {
spreadsheet.hideColumns(i+1, 2);
}
}
};üdv
-
Mutt
senior tag
Szia,
...nem feltétlenül a megoldás kell, hanem szeretném megtanulni, hogy bármilyen felmerülő problémát meg tudjak oldani...
Sok feladat megoldható összetett képletekkel vagy az újabb Excelekben elérhető funkciókkal.
1. Ha esetleg még nem dolgoztál tömbfüggvényekkel, akkor érdemes velük kezdeni. Segít az Excel magabiztos használatában és olyan helyeken/esetekben hasznos ahol a makró nem járható út.
Youtubeon Mike Girvin szokott régen szép megoldásokat bemutatni.2. Hasznos bővítmény a Power Pivot, ami a DAX formulákat adta az Excelhez és a Power Query ami az M-nyelvet. Az előbbi kezd háttérbe szorulni az utóbbi miatt, de érdemes rájuk nézni. Ha nagyobb mennyiségű adattal kell dolgozni, elemzéseket kell készíteni akkor ezek a legjobb eszközök. A Power Query számos olyan funkciót hozott amelyek korábban csak VBA-val ment, szerintem manapság haladó Exceles nem kerülgetheti.
Youtubeon megint csak Mikeot tudom javasolni.Az Office365-el új írányt vett a Microsoft, az online/megosztásos munka új megoldásokat kívánt és ezeket próbálja meg orvosolni a cég. A makró nyelv régóta megvan az Excel-ben; a 4.0-ás makrókat - ha jól emlékszem -1995-ben VBA-ra cserélte a Microsoft, de még a mai napig támogatott a 4.0 makró is. Excel 2013-al pedig jött az újabb nyelv, ami JavaScript alapú. Az új nyelv előnye lesz, hogy az online változatban is használható lesz, de még mindig fejlesztési álapotban van és csak a bátraknak javasolt. A VBA még jó sok évig az asztali változatokban valószínűleg támogatott lesz, de az online-t már csak az új nyelvvel lehet feltuningolni.
Jelenleg is érdemes VBA-t tanulni (angol nyelven John Walkenbach szerintem jó könyveket adott ki, illetve Youtubeon is számos videot találsz vmint a makrórgzítő eredményét is lehet nézegetni). Azonban a JavaScript univerzálisabb nyelv, így ha van energiád érdemes azzal is foglalkozni. A gond hogy az Office JavaScript nyelvről még nincs jó könyv, marad a hivatalos és száraz dokumentáció vmint sok-sok próbálkozás illetve mások kódjának nézegetése. A másik előny, hogy a Google Apps Script is JavaScript alapon nyugszik, így az itt megtanultak ott is tudnak segíteni.
üdv
-
Mutt
senior tag
válasz
ny.erno
#43238
üzenetére
Szia,
Azt szeretném elérni, hogy ha megadom a Termékeket, akkor automatikusan jelenjenek meg az adatok a termék nevével azonos munkalapokon.
Feltöltöttem egy új fájlba 3 különböző makrómentes megoldást.
Mindegyik esetben a lapon a H1-es cellába a lap nevét kézzel be kell írni.
1. Tömbfüggvény
Hátránya, hogy sok adat esetén be fogja lassítani a gépet.
2. Új csak Office365-ben elérhető FILTER függvény
Hátránya, hogy csak a legújabb Excellel használható.
3. Power Query
Excel 2010-től működik, de nem realtime.üdv
-
Mutt
senior tag
Szia,
VBA-s megoldást kaptál, de mivel Office365-öd van egy másik megoldás is, ami az új makró nyelvet használja.
1. Telepítsd az Office áruházból a Script Lab bővítményt.
2. Kattints a Scrip Lab menűben a Code gombra.
3. A hamburger menüben válaszd az Import opciót és add meg ezt a linket: https://gist.github.com/viszi/e2bd0fe97f1cd3794ea2402c90d40914
4. Betöltés után kattints a Run -> Run in this pane-re (feltéve ha már megnyitottad az átalakítandó fájlt).
Majd pedig az indítás gombra.Ebben a változatban én meghagytam a díjak mértékegységét, ha nincs rá szükséged akkor a Code gomb alatt a 23-as sort (
cel.getCell(i, 2).values = result[0][2];) töröld ki.Remélem nálad is műkődik.
üdv
-
Mutt
senior tag
válasz
gepesz13
#43211
üzenetére
Szia,
Miként lehetne Excel 2016 ban egy cellában egy zöld kitöltésű kört csináln?
Beszúrás -> Szimbólumok opciónál válaszd a Wingdings betűtípust, mert az fog tartalmazni különböző alakzatokat (köztük teli és üres kört is). Szúrd be a cellába és formázd-színezd kívánság szerint.
üdv
-
Mutt
senior tag
Szia,
Amit én javasolnék, hogy az eredeti adatsorodban hozzál létre egy új oszlopot, ami csak a számokat tartalmazó házszámokat tartalmazná.
Két megoldást tudok erre javasolni ezen a mintán bemutatva.
1. Ha Excel 2016-ot vagy újabbad van, akkor egy tömbfüggvény tud nagyjából segíteni.
=--SZÖVEGÖSSZEFŰZÉS("";IGAZ;HAHIBA(--KÖZÉP(B3;SOR(INDIREKT("1:"&HOSSZ(B3)));1);""))2. Jobb megoldás egy saját függvény, amelynek a kódja pedig:
Function num(rng As Range, Optional stopat As String) As Long
Dim n As Integer, j As Integer
For n = 1 To Len(rng)
If Len(stopat) Then
For j = 1 To Len(stopat)
If Mid(rng, n, 1) = Mid(stopat, j, 1) Then
Exit Function
End If
Next j
End If
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End FunctionEzt két módon használhatod:
=num(B2) vagy =num(B2;karakter(ek))A második esetben meg tudod adni, hogy csak azokat a számokat listázza amelyeket a megadott karakterig talált meg a függvény. pl. a 11-13 esetén csak a 11-et adja vissza, a =num("11-13","-") képlet.
Ezek után ha a teljes adatsorodat (nemcsak a tisztított házszám oszlopot) jelölöd ki és választod a sorba rendezést akkor minden adat együtt fog mozogni.
üdv
-
Mutt
senior tag
Szia,
Excel van írva, de ez egyértelműen adatbázis feladatsor, amelyhez az Officeban található Access (ha ingyenes kell akkor a LibreOfficeban található Base program a segítség). Access fórum itt található.
A másik probléma, hogy 4 táblát mond a leírás de csak 3 van megadva.
Excelben az alábbiakat tudod végrehajtani, de ahogy haladunk előre egyre inkább nem Excelről kellene beszélni.
1. Hozza létre a táblázatokat
Excelben mondjuk egy munkalap tartalmazza az összetartozó adatokat, a táblákat. A munkalap első sora legyen a fejléc a feladatban megadott nevekkel. Maradj a fejlécen és válaszd a Beszúrás -> Táblázat opcíót, hogy táblázattá tud alakítani a listádat (ezzel pár plusz lehetőséget kapsz).Accessben ez Létrehozás -> Tábla (vagy táblatervező)
2. Hozzon létre CSV fájlokat és töltse be a táblázatokba.
Hát ez elég tág feladat, de CSV fájl egy szöveges fájl amilt Jegyzettömbbel is létre lehet hozni, a lényeg hogy az adatok benne vesszővel (nálunk ez pontosvessző) vannak elválasztva.
pl. 1;1;T1800;5Az adatok feltöltése pedig a fájl importálást jelenti a Fájl -> Megnyitást használva, majd a beolvasott adatok másolásával az adott helyre.
Accessben ez Külső adatforrás -> Új adatforrás
3. Számolja ki a termék átlagárát és a középen elhelyezkedő termékek árát.
TERMÉKEK munkalapon kell a darabszámot és egységárat összesorozni (kezdőként a táblázat melletti F-oszlopba és az oszlop alján az ÁTLAG függvényt használni).A középen található termékekhez pedig a DOBOZOK és TERMÉKEK táblákat kell összekpacsolni (kulcsmező a termékkód) és a SZINT mezővel szűrve lehet megkapni a teljes árat (darabszám * egységár).
Accessben ezt lekérdezésnek hívják és aggregátor (összegző függvények kellenek).
Excelben a második részhez FKERES kell.4. Számolja az átlagárnál drágább termékek darabszámát.
Excelben DARABTELI függvény tud ebben segíteni.Access egy újabb lekérdezés.
5. Egységesítse a terméknevek formátumát.
TERMÉKEK táblán lehet a KISBETŰS/NAGYBETŰS/TNÉV függvények vmelyikét használni.Accessben a lekérdesében lehet LCase/UCase függvényeket ehhez haználni.
6. Listázza ki vevő alapján a rendelt termékek adatait.
Ez már nem alap Excel függvény, de korábban már volt itt ilyen kérdés. Vagy Kimutatás/Pivot vagy egy tömbfüggvényt javasoltunk, esetleg a vadúj SZŰRŐ függvényt.Access sima lekérdezés összekapcsolt táblákkal.
A többi feladathoz nincs elég adat, vagy nem Excelben kellene gondolkodni.
üdv
-
Mutt
senior tag
Elég sok VBA kód lett a fórumon mostanában megosztva, de közben már elérhetó a legújabb teszt verzióban egyeseknek az új makrózási nyelv, amelyet Office Scripts-nek hívnak és nagyon hasonlít a Google Scripts-hez vagyis egy JavaScript alapú programozási nyelv.
A https://docs.microsoft.com/en-us/office/dev/scripts/ oldalon lehet olvasni róla és a már létező függvényeket nézegetni.
Bill Jelen demózza is ebben a videójában.
Másik fontos változás, hogy aki Office 365-öt használ annak már az új tömbfüggvények is elérhetőek, vmint hogy már nem kell Ctrl+Shift+Enter-t használni tömbképletekhez. Ezekről itt lehet olvasni..
-
Mutt
senior tag
Szia,
Nem tökéletes, de múkődnek az alábbi képletek ha a szövegben szám nem található betű után. Vagyis a példában mutatott 12/b/2-t nem szedi szét. Ennél különben mi lenne a helyes megoldás?
Ha A2-ben van a házszám, akkor B2-ben ez a képlet a szám részt adja vissza.
=BAL(A2;KERES(HOSSZ(A2)+1;SOR(INDIREKT("1:"&HOSSZ(A2)))/SZÁM(--KÖZÉP(A2;SOR(INDIREKT("1:"&HOSSZ(A2)));1))))Ezek után a maradék szöveg képlete:
=HELYETTE(A2;B2;"")Lehet egy kicsit tisztítani (megszabadulni az esetleges ponttól, perjeltől, szóköztől).
=KIMETSZ(HELYETTE(HELYETTE(HELYETTE(A2;B2;"");"/";"");".";""))Másik megoldás pedig, hogy Excel 2010-tól van Power Query amiben van egy okos szövegdaraboló.
Ez sajnos el fogja rontani az "1-3b" példát, mert "1;-3;b" eredményt fog adni, de az M-code megfelelő módosításával a kötójel is felvehető a számok közé.
Előtte:Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Házszám.1", "Házszám.2", "Házszám.3"}
Utánna:Splitter.SplitTextByCharacterTransition({"0".."9","-"}, (c) => not List.Contains({"0".."9","-"}, c)), {"Házszám.1", "Házszám.2"}üdv
-
Mutt
senior tag
Sziaszok,
Másik fórumon a felhasználó egy összesített listából akar egy kibontott listát (unpivot) készíteni. Vagyis a tételt annyiszor kell ismételni, amennyi a mennyiség oszlopban van.
A csatolt képen a bal oldali a kiinduló állapot. A cél a jobb oldali előállítása.
Excel 2010-től van Power Query ahol ez könnyedén elérhető.
Lépések.1. Adatok betöltése Power Query-be: Adatok - > Táblázatból...
2. Adjunk két egyéni oszlopot a listához. Az egyik oszlop tartalma csupa 1-es lesz, a másik pedig az Ár / Mennyiség. Oszlop hozzáadása kell kétszer, a második eset pl.
3. Kell egy harmadik oszlop, aminek ez a képlete:
={1..[Mennyiség]}
Ezzel egy számsort tudunk előállítani 1 és a mennyiségben megadott érték között.4. Ennek az oszlopnak a jobb sarkába kattintva bontsuk ki sorokba a számokat.
5. Végül töröljük a felesleges oszlopokat és töltsük vissza Excelbe az eredményt.
üdv
-
Mutt
senior tag
válasz
halfnote
#43066
üzenetére
Szia,
Mi történik akkor ha Adatok -> Lekérdezések & kapcsolatok alatt egyesével, egymás után frissíted a lekérdezéseket?
Az első lekérdezések a munkalapra adatokat írnak vissza? Ha igen, akkor érdemes lenne inkább kapcsolatként létrehozni a lekérdezést.
A végét nem értem, de a forrás fájlban lévő összes tartományra/táblázatra az Excel.CurrentWorkbook() függvénnyel lehet hivatkozni.
üdv
-
Mutt
senior tag
Sziasztok,
Excel 2016-tól van SZÖVEGÖSSZEFŰZÉS (TEXT.JOIN), ami jobb mint a FŰZ/ÖSSZEFŰZ.
Szintén tömbfüggvény, vagyis CSE kell hozzá:=SZÖVEGÖSSZEFŰZÉS("";;KÖZÉP(A1;SOR(INDIREKT("$A1:$A"&HOSSZ(A1)));1))Excel 2010-től pedig van Power Query is, amelyben a Text.Reverse függvény is használható.
Itt csak az a probléma, hogy a fenti függény előtt/után mindig a megfelelő típus konverziót kell elvégezni különben hibaüzenet lesz az eredmény. A lenti példában annyi trükk van, hogy bináris (IGAZ/HAMIS) értéknél a másik bináris értéket adja vissza és nem a fordított szöveget.
Korábbi Excelben marad a korábban mutatott VBA UDF.
üdv
-
Mutt
senior tag
válasz
karlkani
#42810
üzenetére
Szia,
Excel 2010-től van a Power Query ami az alábbi lépésekkel használható.
Én csináltam egy minta txt fájlt, ami így néz ki:
1. Excelben az adatok fülön található beolvasással megnyitom ezt a minta fájlt.
2. Adatok beolvasására kattintva megjelenik a fájl tartalma a Power Query szerkesztőben
3. Az első jobb sarkában található szövegszűrővel rákeresünk az id és username kezdetű sorokra.
4. Szedjük szét az oszlop tartalmát két oszlopra kettőspont elválasztó szerint.
5. A sorokhoz adjunk egy azonosító oszlopot. Oszlop hozzáadása -> Indexoszlop
6. Egy újabb azonosító oszlopot vegyünk fel, de ez ne 1-ről hanem 2-ről induljon. Oszlop hozzáadása -> Indexoszlop -> EgyéniEzzel a két utolsó lépéssel azt értük el, hogy az egymás utáni sorokban lévő felhasználónév és azonosító azonos azonosítószámot kapott.
7. Kezdőlapon a Lekérdezések egyesítése opcióval megkerestetjük az azonos azonosítószámhoz tartozó adatokat. A képernyőt igy kell ehhez kitölteni:
8. Az új oszlop jobb sarkában lévő ikonra kattintva válasszuk ki hogy a második oszlop jelenjen meg.
9. Az első oszlopban szúrjünk a username-re és végül a felesleges oszlopokat töröljük ki.
10. Töltsük vissza Excelbe a készeredményt.
üdv
-
Mutt
senior tag
válasz
zsolti_20
#42820
üzenetére
Szia,
A1 cellában található szöveget/számot ezzel a tömbképlettel tudsz megfordítani:
=FŰZ(KÖZÉP(A1;HOSSZ(A1)-SOR(INDIREKT("$A$1:$A"&HOSSZ(A1)))+1;1))Dátumot szöveggé kell alakítani a SZÖVEG függvénnyel.
=FŰZ(KÖZÉP(SZÖVEG(A1;"ééééhhnn");HOSSZ(SZÖVEG(A1;"ééééhhnn"))-SOR(INDIREKT("$A$1:$A"&HOSSZ(SZÖVEG(A1;"ééééhhnn"))))+1;1))üdv
-
#42720
Mutt
senior tag
torrentwarez
#42711
Mutt
senior tag
válasz
torrentwarez
#42711
üzenetére
Szia,
FILTER függvény hiányában ezt az összetett képletet tudod használni a D-oszlopban (ha máshol kell, akkor érdemes a D$2:D2 hivatkozást javítanod:
=HAELSŐIGAZ(ÉS(DARABTELI($A$2:$A$32;"*"&Report!$B$5&"*")=0;SOROK(D$2:D2)=1);"not found";SOROK(D$2:D2)>DARABTELI($A$2:$A$32;"*"&Report!$B$5&"*");"";SOROK(D$2:D2)<=DARABTELI($A$2:$A$32;"*"&Report!$B$5&"*");INDEX(A:A;ÖSSZESÍT(15;6;SOR($A$2:$A$32)/SZÁM(SZÖVEG.KERES(Report!$B$5;$A$2:$A$32));SOROK(D$2:D2))))üdv
-
#42712
Mutt
senior tag
torrentwarez
#42711
Mutt
senior tag
válasz
torrentwarez
#42711
üzenetére
Szia,
FIlTER (SZŰRŐ) jelenleg csak Office 365 insider változatban érhető el, Excel 2019ben nem fog menni. Helyette tömbfüggvény/pivot használható. Írd le hogy mit szeretnél elérni (szerintem hibás az eredeti képlet), milyen adatokon.
Üdv -
#42436
Mutt
senior tag
Nagyzoli27
#42432
Mutt
senior tag
válasz
Nagyzoli27
#42432
üzenetére
Szia,
Makró nélkül az Excel 2010-től elérhető Power Query-t tudom javasolni.
Ebben a hozzászólásban látod a lépéseket (a 7-10-es lépések neked nem kellenek).üdv
$100=igen
Új hozzászólás Aktív témák
- 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...
- ROBUX ÁRON ALUL - VÁSÁROLJ ROBLOX ROBUXOT MÉG MA, ELKÉPESZTŐ KEDVEZMÉNNYEL (Bármilyen platformra)
- Eladó Steam kulcsok kedvező áron!
- Gyermek PC játékok
- Kaspersky, McAfee, Norton, Avast és egyéb vírusírtó licencek a legolcsóbban, egyenesen a gyártóktól!
- Játékkulcsok olcsón: Steam, Uplay, GoG, Origin, Xbox, PS stb.
- Azonnali készpénzes AMD Radeon RX 5000 sorozat videokártya felvásárlás személyesen / csomagküldéssel
- BESZÁMÍTÁS! Asus A520 R5 3600 16GB DDR4 500GB SSD RTX 2060 8GB Rampage SHIVA CoolerMaster 700W
- Epson Expression 12000 XL Nagyformátumú A3 szkenner
- 134 - Lenovo Legion Pro 7 (16IRX8H) - Intel Core i9-13900HX, RTX 4090
- AKCIÓ! ASUS PRO WS W790E-SAGE SE alaplap garanciával hibátlan működéssel
Állásajánlatok
Cég: Promenade Publishing House Kft.
Város: Budapest
Cég: PC Trade Systems Kft.
Város: Szeged