- Samsung Galaxy S25 Ultra - titán keret, acélos teljesítmény
- Friss koncepciót hoz a Nothing Phone (3)
- Amazfit Active 2 NFC - jó kör
- Csak semmi szimmetria: flegma dizájnnal készül a Nothing Phone (3)
- Magisk
- Samsung Galaxy S24 Ultra - ha működik, ne változtass!
- Xiaomi 15 - kicsi telefon nagy energiával
- iPhone topik
- Fotók, videók mobillal
- Vivo X200 Pro - a kétszázát!
-
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
-
#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
-
#42413
Mutt
senior tag
dellfanboy
#42411
Mutt
senior tag
válasz
dellfanboy
#42411
üzenetére
Szia,
Mivel neked vízszintesen vannak az adataid ez a képlet kell:
=SUM(OFFSET(AH5,0,0,1,MATCH(AZ2,$AH$4:$AT$4,0)))Volatile függvény nélkül pedig a SUMIF-et tudom javasolni:
=SUMIF($AH$4:$AT$4,"<="&$AZ$2,AH5:AT5)üdv
-
Mutt
senior tag
válasz
Redneck
#42373
üzenetére
Szia,
Angol LibreOffice-ban Format -> Conditional alatt találod meg a feltételes formázást.
Kerekítésnél tudni kell, hogy számot akarsz kerekíteni, vagy csak a megjelenítését (ekkor minden további számolás amely ezt a számot használja az eredeti értéket fogja használni, míg az elsőnél a kerekített értékkel számol tovább).
Első esetében a ROUND (KEREKÍT) függvényt kell használni.pl
=ROUND(A1,1)
Másodiknál pedig a cellaformázást (Format -> Cells -> Decimal places).
üdv
-
#42354
Mutt
senior tag
dellfanboy
#42353
Mutt
senior tag
válasz
dellfanboy
#42353
üzenetére
Szia,
Talán élhetünk azzal a feltételezéssel, hogy az első kötőjel a szövegben a rendszám része, akkor ez a képlet tud segíteni:
=KÖZÉP(A1;SZÖVEG.TALÁL("-";A1)-3;7)üdv
-
Mutt
senior tag
válasz
B.Sec.
#42302
üzenetére
Szia,
Én nem találkoztam még ilyen hibaüzenettel, de a leírás szerint vagy vmelyik munkalap, vagy elnevezett tartomány neve nem megfelelő.
Nem kezdődhet aláhúzással (ez lesz a gond nálad), nem lehet cella azonosítóval megegyező elnevezés (pl. A1, B12), nem tartalmazhat szóközt vagy \, /, *, [, ], :, ? karakterek vmelyikét.
Megoldás: nyisd meg a fájlt majd Képletek -> Névkezelő (Ctrl+F3) és a szűrőben keress rá a hibás nevekre.
Esetleg használd ezt: http://www.decisionmodels.com/downloads.htm#namemanager
üdv
-
Mutt
senior tag
válasz
dave0825
#42301
üzenetére
Szia,
A legegyszerűbb megoldás egy Kimutatás (Pivot) az A-oszlopon. Egyetlen hátránya, hogy nem realtime frissül.
Ha Office 365 Insider-t használsz, akkor van EGYEDI (UNIQUE) vagy SZŰRŐ (FILTER) függvény, amely a duplikációkat veszi ki.
Ha régebbi Excel-ed van, akkor egy tömbfüggvény tudja a fentit helyettesíteni.
üdv
-
Mutt
senior tag
válasz
Pakliman
#42300
üzenetére
Szia,
Írhatsz egy saját függvényt, amely ellenőrzi a lehetséges kombinációkat.
pl.
Function DateTest(datum As String) As Boolean
Dim helyes_a_datum As Boolean
helyes_a_datum = IsDate(datum)
If helyes_a_datum Then
DateTest = True
Else
DateTest = IsDate(Replace(datum, ".", ". "))
End If
End Functionüdv
-
Mutt
senior tag
válasz
Pakliman
#42298
üzenetére
Szia,
Nézd meg újra a Windows Területi beállításait, lehet hogy lesz ott szóköz az évek - hónapok - napok között az egyiknél, míg a másiknál nem.
Nekem van szóköz, de a MA() függvény eredményében ez nem látszik. Az IsDate/CVDate pedig csak szóközzel megadott dátumokat fogad el.
üdv
-
-
Mutt
senior tag
válasz
PeLa87
#42228
üzenetére
Szia,
... a főlapom, meg lehetne valahogy jeleníteni, hogy azon a napon van-e valakinek születésnapja?
Makró és segédoszlop nélkül az Excel 2010-től használható Power Query tud megoldás lenni.
Magyar olimpikonok listájából csináltam egy ilyen fájlt:
Van benne egy Fő lap és 12 hónap adatát tartalmazó lap, amelyen vannak az adatok (azonos fejléccel és azonos sorrenddel). Ezek után jöhet a Power Query.
1. Adatok fülön -> Adatok beolvasása -> Fájlból
2. Kiválasztod a fájlt ami tartalmazza az adatokat.
3. Jobb klikk a fájlnéven (esetemben Olimpikonok.xlsx) és Szerkesztés
4. Első lépésként szűréssel zárjuk ki az első lapot, ahova az eredményt akarjuk megjeleníteni. Esetemben a Fő nevű munkalapot dobjuk.
5. Csak a Data oszlopra lesz szükségünk, ezt jelöld ki és a fejlécen jobb klikk után a többi oszlop tőrlése kell.
6. A megmaradt oszlop jobb felső sarkába kattints és válaszd a kibontást.
7. Hozzunk létre egy normális fejlécet, erre a legegyszerűbb megoldás az Átalakítás -> Első sor használata fejlcéként.
8. Mivel minden lapon volt fejlécünk ezért az adatok között nem csak a neveket/dátumokat, hanem a többi lapon ismétlődő fejléceket is látjuk ezt szűréssel vegyük ki.
9. Érdemes a megmaradt oszlopok formátumát beállítani. A születési dátum legyen tényleg dátum, a kor pedig szám. Jelöld ki az oszlopot és az Átalakítás fülön válaszd ki a megfelelő Adattípust. pl. születési dátumhoz a dátum legyen.
10. Kell egy új oszlop, amelybe megnézzük hogy melyik dátum napja azonos az aktuális nappal.
Oszlop hozzáadása -> Egyéni oszlopot használd. A képlet pedig ez legyen:=Date.Day([Születési Idő])=Date.Day(DateTime.LocalNow())
11. Ebben az oszlopban a TRUE értékekre szürjél rá.
12. Utánna törölhet jobb klikkel ez az oszlop.
13. Kezdőlap -> Bezárás és betöltés -> .. adott helyre opció kell.
14. Válaszd a Kimutatás opciót és a fő lapot. Tervezd meg a Kimutatásodat ahogy neked tetszik.
15. A kimutatás beállításainál válaszd az automatikus frissítést megnyitáskort.
üdv
-
Mutt
senior tag
válasz
bara17
#42246
üzenetére
Szia,
Excel 2016-tól felfelé van MINHA függvény, amelyet használj így:
=MINHA(B:B;B:B;">"&F2;C:C;1)ahol B oszlop a dátumos oszlop, C a 0/1-et taratalmazó oszlop és F2 a cella ahol van a keresési érték.
Régebbi Excelben pedig egy tömbfüggvény tud segíteni, ezt Ctrl+Shift+Enter-rel kell lezárni.
=MIN(HA((B1:B22>F2)*(C1:C22=1)>0;B1:B22;2^99))üdv
-
Mutt
senior tag
válasz
TigerCat
#42021
üzenetére
Szia,
Mac-en nagyon apró, így ha elmentem 125%-on, az túl nagy Windows alatt és fordítva.
Láttam választ, de nem láttam OS ellenőrzést, amire az Application.OperatingSystem függvény használható VBA-ban.
Private Sub Workbook_Open()
If Application.OperatingSystem Like "Windows*" Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 150
End If
End Subüdv
-
Mutt
senior tag
Szia,
... Hogyan tudnék excelben létrehozni egy olyan munkafüzetet, ahol ha megadom, hogy pl. 100.000 Ft az alapára a kerékpárnak, majd pedig megadom a 3*10db opciót árakkal, akkor az excel kiszámolja nekem az összes létező variációt, tehát mind a 1.000db-ot és mondjuk egymás alá megadja nekem egy táblázatban.
Makrómentesen az Excel 2010-től elérhető Power Query tud segíteni.
1. A különböző opciókat táblázattá alakítsd át, FFeri mintáját felhasználva van 4 táblázatom egymás mellett. Az elsőben csak a kerékpár alapára van, a többiben pedig a választható elemek (eltérő színekkel jelöltem ezeket). Mindegyik táblázatnak adjál egy beszédes nevet (én Alap, Vaz, Gumi, Attetel, Szin-t használtam).
2. Ezeket az adatokat Power Query-be kell tölteni, rajta állsz az adott adatsoron és Adatok -> Beolvasás táblázat/tartományból.
3. Nem kell semmit Power Query-ben még csinálni, csak Adatok betöltése adott helyre opciót választani és Csak kapcsolatot megadni.
A 2-3-as lépéseket meg kell csinálni mindegyik adatsorral, vagyis lesz 5 db Power Query kapcsolatunk a fájlunkban. Adatok -> Lekérdezések és Kapcsolatok alatt lehet őket látni.
4. Jobb klikk az Alap lekérdezésen a kapcsolatok lapon és Referencia/Hivatkozást választva visszajutunk a Power Query-be, ahol a egy új Egyéni oszlopot kell felvenni.
5. Az egyéni oszlop képlete csak a másik táblának a neve (pl. Vaz).
6. Az új oszlop jobb felső sarkában lévő ikonra kattintva ki lehet bontani az adatokat.
Az eredmény egy keresztszorzat lesz:
7. Az 5-6-os lépéseket végezd el a többi választható opciókat tartamazó adatsorokkal.
A végén vmi hasonlót kapsz:
8. Egy újabb egyéni oszlopban már csak a különböző változatok teljes árát kell kiszámolni, ami egy sima összeadás.
9. Az eredményt vissza kell tölteni Excelbe, a Bezárás és betöltés opcióval.
üdv
-
Mutt
senior tag
válasz
bozsozso
#42050
üzenetére
Szia,
... ki lehetne úgy egészíteni, hogy a gyümölcsökhöz tartozó számokat az alján összesítse, hogy hány db?...
A 4-es lépésnél a csoportosításnál kell egy plusz beállítás: összegezni is kell az értékeket.
A 6-os lépés elé érdemes beszúrni egyet, amikor az összegzett értéket a legutolsó oszlopba húzzod, így amikor transzponálod az adatokat, akkor az utolsó sorba fog kerülni.
A többi lépés teljesen azonos.
üdv
-
Mutt
senior tag
Másik fórumon az alábbi probléma merült fel:
Azt a függvényt, keresem, ami sorban megszámolja a pozitív számokat, de csak az első nem pozitív számig.
Vagyis a kimeneti értéke
0, ha a bemeneti változók -5, X, X, X, stb.
1, ha 8, -3, X, X, X stb.
2, ha 13, 9, -10, X, X, X stb.
3, ha 49, 15, 6.5, X, X, X stb. (ahol X tetszőleges szám).Tömb-függvénnyel megoldható. A lényeg, hogy megkeressük az első negatív szám pozícióját és 1-et levonva belőle megkapjuk, hogy előtte hány pozitív szám volt:
=SZORZATÖSSZEG(MIN(HA(A1:F1<0;OSZLOP(A1:F1);9^99)))-1Az eredményt korrigálni kell, ha nem lenne egyetlen negatív szám sem a listában.
=MIN(SZORZATÖSSZEG(MIN(HA(A1:F1<0;OSZLOP(A1:F1);9^99)))-1;DARAB(A1:F1))
üdv
-
#41972
Mutt
senior tag
marchello1
#41971
Mutt
senior tag
válasz
marchello1
#41971
üzenetére
Szia,
onEdit-et ezzel egészítsd ki:
//ez fog megjelenni a cellában
const link = 'Kattins ide'
//ha a cellában már van hivatkozás vagy a cella tartalma nem http-vel kezdődik akkor kihagyjuk
if (cell.getFormula().substring(0,10) != '=HYPERLINK' && cell.getValue().substring(0,4).toLowerCase() == 'http')
{
//a cella tartalmát linkesítjük
cell.setFormula('=HYPERLINK("' + cell.getValue() + '";"' + link + '")');
}üdv
-
#41970
Mutt
senior tag
marchello1
#41956
Mutt
senior tag
válasz
marchello1
#41956
üzenetére
Szia,
Ez esetben az onEdit makrót kell kibővítened vmi ilyennel:
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getActiveRangeList().setShowHyperlink(false);Ha azt akarod, hogy csak adott munkalapon induljon el, akkor pedig plusz ellenőrzéssel nézd meg ezt:
var spreadsheet = SpreadsheetApp.getActive();
if (spreadsheet.getSheetName() == 'munkalapodneve')
{
spreadsheet.getActiveRangeList().setShowHyperlink(false);
}üdv
-
#41966
Mutt
senior tag
customer114
#41964
Mutt
senior tag
válasz
customer114
#41964
üzenetére
Szia,
Állj rá az A1-es cellára, majd Feltételes formázás -> Új szabály -> Képlet
=$A1=MA()Beállítod a kívánt kiemelést, majd ezt a szabályt alkalmazod a sokadig sorig.
Angol Excelben ez így néz ki:
üdv
-
#41965
Mutt
senior tag
Gravity1234
#41963
Mutt
senior tag
válasz
Gravity1234
#41963
üzenetére
Szia,
A leírásod alapján inkább ez lenne a képlet az 5-ös sorban:
=(Q5+I5+J5+K5+L5+M5+P5)-((C5-D5)+(E5-F5)+(G5-H5)+N5)Ahogy nézem a D-F-H oszlopban lévő értékeket hozzá is adod, majd le is vonod, szóval nullázod az ott lévő értékeket.
Csinálhatod azt is hogy elnevezed az oszlopokat és akkor a képletben az elnevezéséket használhatod:
pl. A fault adatokat tartalmazó oszlopot kijelölöd és bal oldalt nevet adsz neki.
Ezek után már tudsz a képletben hivatkozni rá így: =Pont
üdv
-
Mutt
senior tag
Hali,
Adatok fülön Lekérdezések és kapcsolatokra kattintva jobb oldalt feljön a lekérdezés, ott jobb klikk a lekérdezésen és válaszd a Szerkesztést.
Megnyilik a Power Query szerkesztő és jobb oldalt a lépéseknél a legelső a Source. Jelöld ki és a szerkesztőlécen írd át az újra.
üdv
-
Mutt
senior tag
Szia,
Excel 2010-től felfelé Power Query-t is használhatsz.
A lépések (ezen KSH adatokon bemutatva):
1. Az adatsor import.csv néven mentettem el.
2. Excel-ben Adatok fülön Beolvasás -> Fájlból -> Szöveg/CSV fájlból
3. Adatok átalakítása gombra kattintva bejön a Power Query szerkesztő.
4. Termékkód, Terméknév, Ár oszlopok kiválsztása és jobb klikk a fejlécen és "További oszlopok eltávolítása" opcióval törölhető az összes többi felesleges oszlop.
5. Az oszlopok típusának megadása, egyedül az Ár-on érdemes a Tizedes tört-et változtatni.
6. Új oszlop hozzáadása, amelynek a képlete={1,0,95,200}
7. Az új oszlop fejlécében jobb felső sarokba kattintani és "Kibontás új sorokba".
8. Ennek az oszlopnak is a típusát megváltoztatni, Tizedes tört-re.
9. Az ár és ismétlés oszlopot kijelölve az Oszlop hozzáadása alatt a Szorzás kiválasztása (ha ez nem elérhető akkor nem szám vmelyik oszlop, kimaradt egy fenti lépés).
10. Az előbbi lépés 200-al al megszorozta az árat, de azt hozzá kell adni. Ezért kell egy kis módosítás.
Ezért a szerkesztőlécen ahol látjuk az "each [Ár] * [Ismétlés]" részt kell módosítani. Kézzel írjuk át erre:each if [Ismetles] = 200 then [Ár] + [Ismetles] else [Ár] * [Ismetles]Illetve az oszlop nevét is átírhatjuk valami jobbra, zöld részben "Szorzás" lett lecserélve "Új ár"-ra
11. A további két oszlopot az Egyéni oszlop hozzáadásával tedd meg.
pl.
18. Jobb klikk Tőrlés-el vedd ki azokat az oszlopokat amik nem kellenek.
19. Az eredmény Excelbe töltése az utolsó lépés.
Ezeknek a lépéseknek az volt a célja, hogy ha frissíted a kiinduló fájlodat (a példámban import.csv-t), akkor az Adatok fülön a "Az összes frissítése" gombra kattintva az eredmény le fog frissülni.
Már csak egy dolog maradt az pedig a CSV exportálás. Ezt lehet mentés másként-el, vagy egy általad rögzített makróval vagy ezzel ki tudod exportálni az eredményt.
üdv
-
#41954
Mutt
senior tag
marchello1
#41944
Mutt
senior tag
válasz
marchello1
#41944
üzenetére
Szia,
Ezt bővítsd a kívánt formátumokkal.
function onEdit(e){
var cell = e.range;
if(cell.getColumn() == 2) {
var currency = cell.getValue();
var format;
switch (currency) {
case "HUF": format = "#,##0.00\ [$Ft-40E]"; break;
case "USD": format = "$#,##0.00;$(#,##0.00)"; break;
case "EUR": format = "€#,##0.00;€(#,##0.00)"; break;
default: format = "@"; break;
}
cell.offset(0,-1).setNumberFormat(format);
}
}üdv
-
Mutt
senior tag
válasz
looser
#41949
üzenetére
Szia,
A gond a MAREDÉK részben van, ott ahol =0 van ott =4 legyen (mivel te a 19-es sortól kezdve indulsz, mi a példánkban máshonnan összegeztünk).
A SZUM/HA kombóban a =1 helyett szintén =4 legyen, de ott még a hiba hogy ez olyan függvény amit Ctrl+Shift+Enter-el kell leokézni (tömb-függvény).
üdv
-
Mutt
senior tag
válasz
looser
#41936
üzenetére
Szia,
Már jött helyes válasz a kérdésre, szöveggel hadd egészítsem ki.
A SOR és MARADÉK függvényekkel ki tudod választani minden 5-ik sort és azokat egy tömb-függvényben tudod összegezni. Excel verziótól függetlenül a SZORZATÖSSZEG (SUMPRODUCT) tud segíteni.
Office 365 Insiderben lehet a SZŰRŐ-t is használni. Illetve SZUM és HA is tud működni régebbi Excelekben.
üdv
-
Mutt
senior tag
Szia,
Excel verziótól függően meg lehet oldani makrómentesen is.
1. Office 365 Insider esetén a SZŰRŐ (FILTER) függvény tud segíteni.
A képlet.
=SZŰRŐ(A:D;SZÁM(SZÖVEG.KERES($G$1;E:E)))2. Excel 2010-től van PowerQuery. 2 táblám van az egyik a szűrendő adatokat tartalmazza, a másik a szűrő paramétert (ez nem kötelező). Lépések:
a. A táblák PowerQuerybe töltése.
b. Az adatok lekérdezéshez egy új oszlop felvétele, aminek a képlete ez:[I]=Text.Contains([Kód],Filter)[/I], ahol a Filter a szűrő paraméter neve.
c. Szűrés az új oszlopban az igaz értékekre.
d. Felesleges oszlopok eltávolítása.
e. Eredmény betöltése az új helyre.Kék a két eredeti, zöld az eredmény.
üdv
-
#41899
Mutt
senior tag
m.zmrzlina
#41897
Mutt
senior tag
válasz
m.zmrzlina
#41897
üzenetére
Szia,
Ezt nézd meg: Fájl -> Beállítások -> Speciális -> E füzet megjelenítési beállításai -> Objektumoknál megjelenítendő -> Az összes
Szerintem inkább az adatérvényesítésnél van vmi elcsúszva (pl. rossz hivatkozás, hibás képlet).
üdv
-
Mutt
senior tag
válasz
Richard
#41849
üzenetére
Szia,
Ezt már dashboardnak hívják, aminek a lényege hogy a háttérben - pl. egy rejtett munkalapon - történnek a számítások és onnan hivatkozásokkal kerül át a fő lapra a kívánt eredmény.
Chandoo oldalán régebben voltak hasznos ötletek/minták.
üdv
-
Mutt
senior tag
Szia,
Vmi ilyen kell mint ami a képen látható az N-oszlop végén, ami mindig az L-oszlopban látható értékek átlagát veszi?
Ha igen, akkor a Kimutatáson kívül tudod megoldani. Az N2-es cellában az alábbi képlet van:
=HA(K2="Grand Total";HA(SOROK(L1:L$2)>1;ÁTLAG(L1:L$2);L1);"")Ez a képlet, amikor a "Grand Total" (angol Kimutatás esetén ez az utolsó sor neve) szöveg megjelenik átlagolja az L-oszlopban a felette található értékeket. A képlet le van húzva jó sokáig, hogy ha bővülne a kimutatás akkor is számoljon. Egy kis feltételes formázással még a külalakját is meg lehet változtatni, hogy a kimutatás részének tűnjön.
üdv
-
Mutt
senior tag
válasz
dgabor80
#41838
üzenetére
Szia,
IFS mellett kellenek még AND ér OR függvények is a párhuzamos feltételek figyelésére.
Itt egy általam gondolt megoldás (lehet hogy pár dolgot rosszul értelmeztem), az utolsó feltétel nélkül.
A használt képlet:
=IFS(AND(OR(A2="A";A2="B";A2="C");B2>=12;C2<3);"Max stock > 12 MOS";AND(OR(A2="A";A2="B";A2="C");AND(C2>=0.01;C2<0.5);OR(D2<0.01;D2>=1));"Min stock < 0.5 MOS";AND(OR(A2="A";A2="B";A2="C");AND(C2>=0.01;C2<0.5);AND(D2>=0.01;D2<1));"Min stock < 0.5 MOS // Max Stock > 1 MOS";AND(OR(A2="A";A2="B";A2="C");B2>=12;C2>=3);"Min stock > 3 MOS // Max stock > 12 MOS";AND(A2="E";E2>0);"No Min/Max stock";AND(A2="D";D2>=0.01;D2<1);"Max stock < 1 MOS")üdv
-
Mutt
senior tag
Szia,
Melyik képlettel tudom azt megcsinálni, hogy egy adott oszlopban található értékek közül, ha talál ismétlődést, akkor dobjon egy "NOK" üzenetet, vagy bármi mást.
Az alábbi képlet IGAZ-at ad vissza ha nincs ismétlődés, HAMIS-t ha van.
=DARAB2(A1:A9)=SZORZATÖSSZEG(1/DARABTELI(A1:A9;A1:A9))A képlet csak akkor működik ha nincs üres cella a vizsgált tartományban.
üdv
-
Mutt
senior tag
válasz
jackal79
#41731
üzenetére
Szia,
Rögzíts egy makrót az egyéni makró-munkafüzetben billentyparanccsal.
Fejlesztőeszközök -> Makró rögzítése:
Ezek után Alt-F11-el nyisd meg a VBA szerkesztőt és bal oldalt a VBA Project (Presonal.XLSB) alatt a Modules -> Module1 -et nyisd meg (ha több van akkor a legnagyobb sorszámmal rendelkezőt).
A jobb oldalt lévő nem kommentes sorokat írd felül ezzel:
Selection = DateMentés és innentől amit kijelölsz vagy éppen amin állsz oda az aktuális dátum lesz beszúrva (felülírva az ottani tartalmat, ez a művelet nem lesz visszavonható).
Második megoldás, hogy az adott fájl valamelyik eseménykezelőjéhez (WB.Open, WS.Change) készül a kód, de ehhez többet kell tudni.
üdv
-
Mutt
senior tag
válasz
bexinho23
#41728
üzenetére
Szia,
1. Kiemeled a kívánt szövegrészt, majd nyomtatás előtt a Beállítások -> Megjelenítés menüben a "Kiemelési jelek megjelenítése" opciót kipipálod. Nyomtatás után pedig vissza.
2. Bekapcsolod a korrektúrát, Véleményezés -> Változások követése. Ekkor a szövegben és jobb oldalt a margón is lehet látni a javításokat. Nyomtatásnál vedd ki a pipát a változásoknál.
üdv
-
Mutt
senior tag
válasz
mariogg
#41702
üzenetére
Szia,
Első tippre az automatikus javítást javasolnám, Beállítások -> Nyelvi ellenőrzés -> Automatikus javítások.
Fel tudod venni a saját helytelen-helyes szöveg párosodat akár egy saját szótárba, amelyet tudsz másik gépre is vinni.
Második tip, hogy első lépésben a beviteli cella mellé egy FKERES (angolul VLOOKUP) függvénnyel keresteted meg a teljes nevet. Második lépsben pedig átmásolod a kapott eredményt a beviteli cellába.
üdv
-
Mutt
senior tag
válasz
#06658560
#41696
üzenetére
Szia,
Az adataid nincsenek alkalmas sorrendben, ezért a Pivot/Kimutatás nem fog menni.
Excel 2013-hoz van az ingyenesen letölthető Power Query.
Ezzel az alábbi lépésekkel tudod megkapni a végeredményt. (Excel 2016-on tudom megmutatni a dolgokat, de emlékeim szerint 2013-ban is mennie kell minden lépésnek.)
1. A tartományodat nevezd el, én Adatok nevet adtam neki.
2. Lesz egy Power Query füled, azon belül From Table opció.
(Ha ez nem megy, akkor pedig használd a Blank query-t és ezt a képletet add meg:= Excel.CurrentWorkbook(){[Name="Adatok"]}[Content]
Ha jobb oldalt lesz több lépésed (pl. Promote headers, Change type), akkor töröld ki őket és csak a Source maradjon.
3. Javítsuk ki a fejlécet, mindenhol legyen Hét 1, Hét 2 stb.
A Transform fülön kattints a Transpose opcióra, majd jelöld ki az első oszlopot és Fill -> Fill down-t válaszd.
4. Ezek után a Transform fülön a Use First Row Headers-t használd.
5. Jelöld ki az első két oszlopot majd használd az Unpivot -> Unpivot other columns beállítást.
6. Tegyük sorrendbe az oszlopokat, húzzuk előre a termékeket, a végére pedig a heteket.
7. Az adatokat is rendezni kell, előbb jelöld ki a heteket tartalmazó oszlopt és tedd növekvő sorrendbe.
Majd jelöld ki a termékeket és azt rendezzük, végül pedig a tulajdonságokat jelöld ki és azt is rendezd.Az eredmény ez lesz:
8. Munkalapra a Home fülön a Close & Load to -> load to... -val töltsük be.
A fenti lépések arra voltak jók, hogy ha bővíted az Adatok tartományt, akkor 2 kattintással megkapod a frissített listát.
pl. Bővül az Adatok tartomány
A Power Query fülön Workbook Queries-t vélasztva jobb klikk az előbb összerakott lekérdezésen a Refresh felkapja az új adatokat.
Nem ideális, de ha nem lehet a kiindulási listán változtatni akkor érdemes megnézni hogy ez járható út-e.
üdv
-
-
Mutt
senior tag
Szia,
Ha Excel 2016 vagy jobbad van, akkor a MAXHA függvény tud segíteni, ha régebbi akkor egy tömb-függvény.
Lépések:
1. A J-oszlop nálam egy Kimutatás a bal oldali adatsorból. Csak a vezetők neveit húztam be a kimutatásba, ami automatikus frissítésre van téve. A másik megoldás, hogy egyszerűen felsorolod a vezetőket (ekkor ha van változás azt kézzel kell megadni).
2. A telephelyeket megadhatod kézzel, mert ez talán statikus lesz.
3. K3-ban az alábbi képlet van Excel 2016-esetén:=MAXHA($C:$C;$B:$B;$J3;D:D;1)
Régebbi Excel-nél pedig ez a tömb képlet:=MAX(HA(($B$3:$B$10000=$J3)*NEM(ÜRES(D3:D10000));$C$3:$C$10000;0))Írtad, hogy az asszisztens vagy 1-et vagy x-et ír a rubrikába, a fenti képlet csak akkor ad helyes eredményt, ha 1-est ír be. Ha az x-re is figyelni kell, akkor ez a képlet:
=MAX(MAXHA($C:$C;$B:$B;$J3;D:D;">0");MAXHA($C:$C;$B:$B;$J3;D:D;"x"))
4. A fenti képlet vagy egy dátumot, vagy 0-át (amit 1900. jan 1-nek fog mutatni) fog kiírni. Ha a 0 helyére a "nem volt" szöveg kell, akkor azt egy saját számformátummal tudod elérni. Ctrl-1 -et nyomj a cellán és egyéni formátumnak ezt add meg: éééé.hh.nn;;"nem volt"
5. A kiemelt telephely látogatások számait a DARABHATÖBB függvény tudja megadni. Az én példámban a telephely választható (P1 cellában) és mindig az utolsó 30 napot nézi. A képlet P3-ban így:=DARABHATÖBB(C:C;">="&MA()-30;C:C;"<"&MA();B:B;J3;INDEX(D:H;0;HOL.VAN($P$1;$D$2:$H$2;0));">0")Ez a képlet az x-el jelölt dátumokat kihagyja, ha x-re is kell figyelni akkor jó hosszú lesz a képlet:
=DARABHATÖBB(C:C;">="&MA()-30;C:C;"<"&MA();B:B;J3;INDEX(D:H;0;HOL.VAN($P$1;$D$2:$H$2;0));">0")+DARABHATÖBB(C:C;">="&MA()-30;C:C;"<"&MA();B:B;J3;INDEX(D:H;0;HOL.VAN($P$1;$D$2:$H$2;0));"x")üdv
-
Mutt
senior tag
válasz
bozsozso
#41577
üzenetére
Hali,
Ha 100 terméked van a termék lapodon, akkor a rendelt munkalapodon mindegyik terméknek kell egy oszlop (vagyis ott 100 oszlop kellene hogy legyen).
A képernyőmentés szerint van 38 terméked (3-40 sorig), de a rendelt munkalapon csak 28 oszlopot jelöltél ki a B3:AC3 tartománnyal. Ez hibához vezet. 38 sorhoz, 38 oszlop kell (azonos sorrendben kell lenniük itt és ott is a termékeknek hogy helyes eredmény legyen).
üdv
-
Mutt
senior tag
válasz
Aladaar
#41574
üzenetére
Szia,
Ha A2-ben van az eredeti szöveged, akkor ez visszaadja az első számig a karaktereket.
=KIMETSZ(BAL(A2;HAHIBA(HOL.VAN(IGAZ;SZÁM(KÖZÉP(A2;SOR(INDIREKT("$A$1:$A"&HOSSZ(A2)));1)*1);0)-1;HOSSZ(A2))))Ctrl+Shift+Enter-t kell majd nyomod bemásolás után, mert tömb-függvény.
üdv
-
Mutt
senior tag
válasz
bozsozso
#41573
üzenetére
Szia,
SZORZATÖSSZEG függvény fog segíteni a szorzásban és az összeadásban.
Csak 2 plusz feladat van:
1. a rendelések lapon a rendelt mennyiségek vízszintesen vannak megadva termékenként, miközben a termékek lapon függőlegesen. Itt a TRANSZPONÁLÁS függvényt kell segítségül hívni.
2. a képletet nem simán Enter-el, hanem Ctrl+Shift+Enter-el kell bevinni.A példádra a képlet csak ennyi lenne (feltételezve hogy 22 terméked van, ha több akkor mindkét hivatkozást egységesen bővíteni kell - fontos hogy az sorok száma és az oszlopok száma azonos legyen).
=SZORZATÖSSZEG(Termekek!B2:B23;TRANSZPONÁLÁS(Rendelesek!$B2:$W2))Ez a képlet másolás során el fog csúszni, ezért kell az iNDEX - HOL.VAN-al javítani rajta.
=SZORZATÖSSZEG(INDEX(Termekek!$B$2:$G$23;0;HOL.VAN(Felhasznalas!E$1;Termekek!$B$1:$G$1;0));TRANSZPONÁLÁS(Rendelesek!$B9:$W9))üdv
-
Mutt
senior tag
válasz
dm1970
#41543
üzenetére
Szia,
Jöttek már tömbfüggvényes változatok, de itt van pár változat sima függvénnyel:
=SZORZATÖSSZEG((HOSSZ(A1:G1)>0)*($A$8:$G$8))=HAHIBA(HOL.VAN(SOKSZOR("Z";255);A1:G1);HOL.VAN(2^99;A1:G1))=KERES(2;1/(A1:G1<>"");$A$8:$G$8)Az elsőnél hibás lesz az eredmény, ha egy soron belül több találat is lehetséges.
A másik kettőnél pedig nem a legelső találatot adja vissza, hanem a legutolsót így megint hibás lehet az eredmény ha egy soron belül több találat van.üdv
-
Mutt
senior tag
Szia,
Excel 2010-től felfelé Power Query-vel a lépések:
1. Szám - szöveg - ismétlődések egy táblázatban egymás mellett.
2. Táblázat betöltése Power Query-b: Adatok fül -> Táblázatból
3. Oszlop hozzáadása -> Egyéni oszlop
A képlet pedig ez:=List.Numbers(1,[Ismétlődés])
4. Az új oszlop felső jobb sarkára kattintva "Kibontás új sorokba" opció.
5. Kijelölöd azokat az oszlopokat, amiket össze akarsz fűzni. Átalakítás -> Oszlopok egyesítése
6. A felesleges ismétlődés oszlopon jobb klikk -> Eltávolítás.
7. Kezdőlap -> Betöltés adott helyre... és kiválasztod hogy melyik lapra kerüljön az eredmény.
Ezzel a technikával tetszőlegesen állíthatod az ismétlések számát.
üdv
-
Mutt
senior tag
Szia,
Első lépésben újra kell gondolni a struktúrát, mert az eredeti felállás nem könnyedén feldolgozható.
Én egy ilyen struktúrát tudok elképzelni: soronként egymás alatt, hogy ki-mikor-milyen ebédet rendelt.
Ezek után indíts egy Word-öt. A körlevél funkció régóta megvan, Office XP is jó erre. Tervezd meg, hogyan nézzen ki az ebédjegy (gondolom 1 lapon több is lesz).
A Körlevél varázsló tud neked segíteni, de az alábbi lépéseken kell végig menni.
1. A dokumentum sablont megcsinálni, ez kész van.
2. Az adatbázist (Excel fájl esetünkben) összekötni a körlevéllel.
3. Az adatbázis elemeit beszúrni a dokumentum megfelelő helyeire. Ehhez kell majd a megfelelő menűben beszúrni az adatmezőket. például az Excelben a dátum mezőt
4. Megnézni egy élőképen, hogy jó-e az eredmény.
5. Végül pedig összefésülni a sablont az adatokkal, létrehozni a körlevelet, amelyet lehet nyomtatni.Ha Office 2010-ed vagy frissebbed lenne, akkor pedig Power Pivot-al lehetne egy hasonló táblázatot készíteni, mint amit eredetileg mutattál. (Lenti képen bal oldalon az adatsor, jobbon pedig egy kimutatás belőle).
üdv
-
Mutt
senior tag
Szia,
Ha évente változik, akkor nem küzdenék vele hanem közvetlenül hivatkoznék a másik fájl cellájára.
Esetleg egy változóhoz rendelném azt a másik cellát és akkor évente 1x csak a névkezelőben kellene a hivatkozást javítani.Makróval az tudod megcsinálni, hogy a Workbook Open eseményével megnyitod a másik fájlt is, majd a Close eseményével zárod.
üdv
-
#41430
Mutt
senior tag
huliganboy
#41418
Mutt
senior tag
válasz
huliganboy
#41418
üzenetére
Szia,
KIMETSZ kihagyható.
Közterület neve:
=BAL(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))-1))-1)Közterület jellege:
=HELYETTE(KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))-1))+1;100);" "&D2;"")Házszám:
=KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))))+1;100)A képlet magja, hogy mindig megtaláljuk hátulról azt a szóközt, ameddig vagy amitől kell a szöveget csonkolni.
1. Először azt kell megtudnunk hogy hány szóköz van a szövegben.
HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))2. A HELYETTE függvénynek megadható, hogy melyik előfordulását a szövegnek helyettesítse mással (én a kukac jelet használtam).
A házszám az utolsó szököz után indul a feltételezésünk szerint; aHELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";"")))rész a házszám elé fogja tenni a kukacot.
ASUBSTITUTE(A2;" ";"@";LEN(A2)-LEN(SUBSTITUTE(A2;" ";""))-1)
résznek a végén az -1 azt jelenti, hogy az utolsó előtti szököznél lesz a kukac, ahonnan a közterület jellege indul.3. Innen már csak szöveg függvények (BAL, JOBB, KÖZÉP) megfelelő használata kell az eredményhez.
üdv
-
#41415
Mutt
senior tag
huliganboy
#41410
Mutt
senior tag
válasz
huliganboy
#41410
üzenetére
Szia,
Próbáld meg hogy hátulról indulsz el, vagyis az utolsó szóköz után a házszám van. Az előtte levő szóköztől pedig a közterület jellege található, és minden ami ez előtt van az pedig a közterület neve.
Házszám képlete:
=KIMETSZ(KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))))+1;100))Közterület típusa:
=KIMETSZ(HELYETTE(KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))-1))+1;100);KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))))+1;100);""))Közterület neve:
=KIMETSZ(HELYETTE(A2;KÖZÉP(A2;SZÖVEG.TALÁL("@";HELYETTE(A2;" ";"@";HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))-1))+1;100);""))
üdv
-
Mutt
senior tag
Szia,
Bemásolod a képletet a cellába, majd egy sima Enter helyett egyszerre Control+Shift+Entert nyomsz. Ekkor az Excel a képletet kapcsos {} zárójelek közé teszi. Az ilyen képletek egyszerre több cellán alapulnak, ezért lassabbak mind az egyszerű képletek. Ha látványossan lassú lesz az Exceled, akkor más megoldás kell.
üdv
-
Mutt
senior tag
Szia,
Itt az én megoldásom függvényekkel. Remélem Excel 2016-od vagy jobbad van, mivel használtam a SZÖVEGÖSSZEFŰZÉS függvényt.
F2-nek a képlete:
=HA(D2+E2<>HA(A2=A1;D1;C2);"x";HA(ÉS(D2+E2=HA(A2=A1;D1;C2);D2>0);"KÉSZ";""))
G2-ben egy tömb-függvény van, amelyet Ctrl+Shift+Enter-el kell majd bevinni és jelenleg az első 1000 sort nézi:=SZÖVEGÖSSZEFŰZÉS("; ";IGAZ;HA(($A$2:$A1000=$A2)*($F$2:$F1000="x");$B$2:$B1000;""))
Nézd meg, hogy nálad helyesen műkődik? pl. a 30-31-es sorok szerintem készen vannak, mivel az előző műveletből átadodd összes alkatrész megvan, de a mintádban ezekre te nem írtál készt.
üdv
-
#41002
Mutt
senior tag
bartucz911
#41001
Mutt
senior tag
válasz
bartucz911
#41001
üzenetére
Szia,
Köszi a pontosítást. Beépített függvényekkel nem sikerült értelmes eredményt kihoznom, de ha UDF (makrós függvény) is játszik, akkor ezt próbáld ki. Alt-F11-el VBE szerkesztő megnyilik, majd ott Insert -> Module és a megjelenő üres ablakba másold be a kódot.
Function NextMatch(adat As Range)
Dim szam As Range
Dim szamok As New Collection
On Error GoTo ismetlodes
For Each szam In adat
If Len(szam.Value) > 0 Then
szamok.Add szam.Value, CStr(szam.Value)
End If
Next szam
Exit Function
ismetlodes:
NextMatch = szamok.Count
End FunctionEzek után lesz egy NextMatch függvényed, amely egy tartományt vár ahol a vizsgálandó számok vannak (a tartomány elejét ne fixáld!).
üdv
-
Mutt
senior tag
Szia,
Ha van ÖSSZESÍT függvény, akkor van Power Query is.
Lépések:
1. Legyen két táblázat a fájlban, az első tartalmazza a játék eredményeket, a második pedig hogy melyik csapat melyik eredménye érdekel fordított sorrendben. Lehet akár több csapat is eltérő adatokkal. A táblázatok neve Table1 és Table2 nálam.
2. Töltsük be Power Query elöbb a második táblát. Álljunk bele a táblázatba és Adatok -> Adatok beolvasása -> Táblázatból.
3. Jelöld ki a második oszlopot és Kezdőlap -> Oszlop felosztása -> Elválasztó alapján.
4. Menjünk visssza Excel-be, Kezdőlap -> Bezárás és betöltés -> .. adott helyre. A képernyőn "Csak kapcsolat létrehozása" legyen kiválasztva.
5. Menjünk át a másik táblázatba és azt is töltsük be Power Query-be.
6. Első lépésként adjunk a listához egy index oszlopot, Oszlop hozzáadása -> Indexoszlop.
7. Az indexoszlop csökkenő sorrendbe rendezésével megfordítjuk a listát, vagyis előre kerülnek az utolsó eredmények. Oszlopot jelöld ki, majd Kezdőlap -> Rendezés -> Csökkenő sorrend.
8. Válasszuk ki a minket érdeklő adatokat, Kezdőlap -> Összevonás -> Lekérdezések egyesítése.
Table2 -t alulra kiválasztod, majd a kulcsmezőket megadod és végül az egyező sorokat beállítjuk.
9. Ezek után csapatonként kell létrehozni egy újabb sorrendet. Ehhez előbb a Kezdőlap -> Csoportosítást használjuk.
A képletben ha az
each _részt lecseréljükeach Table.AddIndexColumn(_, "Index1", 1)-re, akkor minden csoportban lesz relatív sorrendünk.
A képlet javítása után ez lesz az eredmény:
10. Az utolsó oszlop jobb felső sarkába kattintva jelenítsük meg a rúgott és az index1 értékeket.
11. A 8-as lépést újra játszuk el, annyi különbséggel hogy most már a melyik oszlopot és az index1-et is kiválasztjuk.
12. Jön az utolsó csoportosítás a rúgótt gólokra.
13. A végeredményt visszatöltjük Excelbe, Kezdőlap -> Bezárás és betöltéssel.
üdv
-
Mutt
senior tag
Szia,
Az
ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);1)megmondja az utolsó sor számát, ezt a sort kell kizárni a SZORZATÖSSZEG-ből.=SZORZATÖSSZEG(($S$2:$S$1000=$X$2)*(SOR($S$2:$S$1000)>=MIN(HAHIBA(ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);{1;2;3;4;5;6});2^99)))*(SOR($S$2:$S$1000)<ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);1))*($T$2:$T$1000))Vagy itt van egy általánosabb képlet, ahhoz hogy az N-edik előforduláshoz tartozó gólok számád megkapd:
=HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);N);0));0)Ha szükséged van hátulról a második és negyedik eredményre, akkor csak össze kell adnod őket:
=HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);2);0));0)+HAHIBA(SZUM(INDEX($T$1:$T$500;ÖSSZESÍT(14;6;SOR($S$2:$S$500)/($S$2:$S$500=$X$2);4);0));0)üdv
-
Mutt
senior tag
Szia,
Erre pedig hadd javasoljam a Power Query-t, Excel 2010-től felfelé (2010-hez külön kell telepíteni, utána az Adatok/Data csoport alatt érhető el).
Lépések (angol nyelvű Excelben):
1. Az adatosort alakítsd táblázattá.
2. Adatokat töltsd be Power Query-be. Data -> From Table opció.
3. Vegyük le a duplikációkat. Home -> Remove rows -> Remove duplicates.
4. Home -> Close&Load -> Close & load to opcióval térjünk vissza Excelbe és válasszuk az Only connection opciót.
Most kell csinálnunk egy másik lekérdezést, amivel előállítjuk a teljes listát, ami minden sorszámot tartalmaz.
5. Egy üres lekérdezéssel indítunk. Data -> Get Data -> From other sources -> Blank query.
6. Fent a szerkesztő lécbe az alábbi képletet kellene beírnod:= List.Numbers(Table1[Adatsor]{0}, List.Last(Table1[Adatsor])-Table1[Adatsor]{0}+1)Ahol Table1 a korábbi lekérdezés neve, bal oldalat látható hogy mik lettek eddig létrehozva. Adatsor pedig a mező neve ahol a sorszámok vannak.
7. Ezt a listát táblázattá kell alakítani. Transform ->To Table opcióval.
8. Jöhet az összehasonlítás a csonka listával. Home -> Merge queries.
Ott ki kell választanod a másik táblát, amire hivatkoztunk a 6-os lépésben, továbbá ki kell jelölnöd hogy a két listában mely oszlopokat hasonlítsa össze (itt most csak 1-1 oszlopunk van, de jelöld ki őket). Majd a join type-ot Left Anti-ra állítani, mert ez fog helyes eredményt a mostani kérdésre adni.
9. Meg is van az eredmény. Töröljük a felesleges oszlopot jobb klikk Remove-al.
10. Már csak vissza kell tölteni Excelbe a Close & Load -> Close & Load to -t választva és az ablakban a Table opciót meghagyva.Az előnye, hogy ha egy újabb számokat adsz hozzá az adatsorhoz és Data -> Refresh all-ra kattintasz, akkor azonnal lefrissíti neked az eredményt.
üdv
-
Mutt
senior tag
Szia,
Tömbfüggvénnyel ezt tudod használni. Feltétel, hogy Excel 2016 vagy frissebbed legyen.
Csak számokat tartalmazó esetre a képlet, CSE-vel bevíve:
=TEXTJOIN(",";TRUE;IF(G2:G5+1<>G3:G6+0;G2:G5+1;""))Ahol a G2:G5 a tartomány az első elemtől az utolsóelőtti elemig, míg G3:G6 a tartomány az első elem nélkül végig.
Ha a sorszám mellett van még szöveg, akkor vagy BAL vagy JOBB függvénnyel kell kinyerni a csak számot tartalmazó részt.
ABxxxx estén a jobb széle kell, így JOBB/RIGHT függvény.
=TEXTJOIN(",";TRUE;IF(RIGHT(A2:A7;6)+1<>RIGHT(A3:A8;6)+0;"AB"&RIGHT(A2:A7;6)+1;""))xxxx/YYYY esetén pedig fordítva:
=TEXTJOIN(",";TRUE;IF(LEFT(D2:D5;6)+1<>LEFT(D3:D6;6)+0;REPT(0;6-LEN(LEFT(D2:D5;6)+1))&LEFT(D2:D5;6)+1&"/2018";""))üdv
-
Mutt
senior tag
válasz
ntamas97
#40949
üzenetére
Szia,
Én ajánlanám neked még a Power Query-t is, amellyel ezt tudod pár kattintáson keresztül elérni:
Lépések:
1. Javasolt, hogy alakítsd át táblázattá az adatsorod. Beszúrás -> Táblázat.
2. Adatok -> Adatok beolvasása -> Más forrásokból -> Táblázatból
Az eredmény vmi ilyen lesz:
3. Jobb klikk az autómárkás oszlopon és válaszd a "További oszlopok eltávolítása" menüt, így csak ez az oszlop fog megmaradni (a fájlodban továbbra is megmaradnak az adatok, csak a Power Query-ből tűnnek el).
4. Ahogy látom nem mindig vesszővel vannak elválasztva a felsorolások, ezért javaslom, hogy a vesszőket cseréljük le szóközre. Kezdőlap -> Értékek lecserélése opció. Keresendő lesz a vessző, az új érték pedig egy szóköz.
5. Most már jöhet a felszabdalás, Kezdőlap -> Oszlop felosztása -> Elválasztó alapján
Szóközt válaszd ki és a speciális opcióban válaszd, hogy te sorokba akarod a felosztás eredményét.
6. Ezek után 1 oszlopban ott lesz az összes eredmény, de lesznek üres sorok is. Azokat szűrjük ki az oszlop jobb felső sarkában található szűrőre kattintva, az üres mellől vedd ki a pipát.
7. Jöhet akár egy kis csinosítgatás, pl. vegyük ki a felesleges szóközöket, illetve tegyük az első betűt nagybetűssé. Átalakítás -> Formátum alatt van a Levágás illetve a Szókezdő Nagybetűk. Használjuk ezeket.
8. Most már lehet számoltatni az ismétlődéseket. Kezdőlap -> Csoportosítási szempont legyen így kitöltve.
9. Ha ABC sorrendben akarod a márkákat, akkor pedig az első oszlopot jelöld ki, majd Kezdőlap -> Növekvő rendezés.
10. Már csak az eredményt kell visszahozni Excel munkalapra. Kezdőlap -> Bezárás és betöltés -> ..adott helyre.
Már csak azt kell megadni, hogy az eredmény táblázat hova kerüljön.
A fenti lépések előnye, hogy ha a kiinduló tábázathoz új sorokat adsz (pl. Trabant), akkor elég csak az Adatok fülön az összes frissítése opcióra kattintani és megkapod az új eredményeket.
üdv
-
#40938
Mutt
senior tag
bartucz911
#40935
Mutt
senior tag
válasz
bartucz911
#40935
üzenetére
Szia,
Lehet, hogy rosszul értelmezem a feladatot, mert a mintának megadott sorozatból nem jönnek ki az eredmények, de itt egy változat arra, hogy keressük meg hogy hány cellával később található meg ugyanazon szám.
Ha Excel 2010-ed van legalább, akkor az ÖSSZESÍT (AGGREGATE) függvény tud segíteni, a képlet B2-ben:
=HAHIBA(ÖSSZESÍT(15;6;SOR(szamok)/((szamok=A2)*(SOR(szamok)>=SOR()+1));1)-SOR();"")Ha régebbi, akkor egy tömb függvény:
=HAHIBA(HOL.VAN(1;ELŐJEL((szamok=A2)*(SOR(szamok)>=SOR()+1)*SOR(szamok));0)-SOR()+1;"")A feladathoz létrehoztam a névkezelőben egy dinamikus tartományt "szamok" névvel, a képlete:
=ELTOLÁS(Sheet1!$A$2;;;DARAB(Sheet1!$A:$A))Erre csak akkor van szükséged, ha nem tudod előre hány sorban lesz számok.
A végén pedig cella formázással adom az eredményhez a "lépés" szöveget. A formázásnak ezt kell megadni:
[>0]#" lépés";;;üdv
-
-
Mutt
senior tag
Szia,
... függvénnyel(B1) meg szeretném számoltatni, hogy az A-oszlopban mennyi egyedi (hány fajta) szám van ...
Ma SZORZATÖSSZEG napom van, B1 képlete:
=SZORZATÖSSZEG(1/DARABTELI(A2:A16;A2:A16))A tartományt mindig igazítanod kell a képletben, vagy egy dinamikus tartományra kell cserélned.
Office 365 és Google Táblázatok esetén pedig van már UNIQUE (EGYEDI?) függvény is, amit egy COUNT (DARAB) függvénybe kell rakni.
üdv
-
Mutt
senior tag
Szia,
Szeretném egy adott csapat 6 utolsó hazai meccsén a hazai csapat által rúgott gólok számát összegezni.
Remélem Excel 2010 vagy frissebb verziód van, mert akkor ez a képlet működni fog (az első 1000 sorra, ha többed van akkor nyugodtan terjeszd ki a tartományt, de teljes oszlopra nem fog működni):
=SZORZATÖSSZEG(($S$2:$S$1000=$X$2)*(SOR($S$2:$S$1000)>=MIN(HAHIBA(ÖSSZESÍT(14;6;SOR($S$2:$S$1000)/($S$2:$S$1000=$X$2);{1;2;3;4;5;6});2^99)))*($T$2:$T$1000))
Hogy működik?
1. A csak Excel 2010-től elérhető ÖSSZESÍT (AGGREGATE) függvénnyel megkeressük, hogy az X2 cellában található csapat mely sorokban van az S-oszlopban és abból a 6 legnagyobb értéket visszakérjük.
Pl. AS Roma esetén 24 és 4-es sorokat fogjuk megkapni a mutatott minta alapján.2. Az előbb kapott eredmény alapján tudjuk már, hogy a 4-es sortól lefelé akárhol fordul elő az AS Roma az az utolsó 6 meccsét fogja jelenteni. Ezért van az ÖSSZESÍT egy MIN függvénybe téve, illetve ha nincs 6 meccse akkor egy HAHIBA függvénnyel ezt is kezeljük.
3. Ezek után már csak összegeznünk kell azokakat a sorokat, ahol AS Roma van az S oszlopoban és a sor száma nagyobb vagy egyenlő mint a 2-es lépésben kapott sorszám. A SZORZATÖSSZEG (SUMPRODUCT) tud ebben segíteni.
üdv
-
Mutt
senior tag
Sziasztok,
Nekem újdonság, hogy kb. 5 éve van egy hivatalos, ingyenes VLOOKUP kiegészítő a Microsoft részéről amivel lehet találatot kapni olyan esetben is amikor a keresendő kifjezés másképpen található meg a keresési táblában.
A kiegészítő innen tölthető le.
Mr. Excel pedig be is muttatja, hogyan lehet használni, mire kell mindenképp odafigyelni.
üdv
-
Mutt
senior tag
válasz
Paxker315
#40577
üzenetére
Szia,
...a D oszlopban levő dátumok közül szeretném összegezni az összes hétfőt...& illetve az összes januárt...
Hétfőket ezzel tudod megszámolni (=1 rész nézi a hétfőt, ha kedd kell akkor =2-re írd át):
=SZORZATÖSSZEG(--(HÉT.NAPJA(D2:D1000;11)=1))Január egy kicsit trükkösebb (itt is az =1 az első hónap, az =12 pedig december lesz):
=SZORZATÖSSZEG(--(HÓNAP(D2:D1000)*NEM(ÜRES(D2:D1000))=1))Januári hétfők pedig:
=SZORZATÖSSZEG((HÓNAP(D2:D1000)*NEM(ÜRES(D2:D1000))=1)*(HÉT.NAPJA(D2:D1000;11)=1))üdv
-
Mutt
senior tag
válasz
bteebi
#40573
üzenetére
Szia,
A ...K oszlopban lévő képlet látszólagosan figyelmen kívül hagyja az autótípust... hibára a megoldás, hogy a HELYETTE($F$2;",";""))=1 részben nagyobb egyenlő kell. A teljes javított képlet:
=SOR()*ELŐJEL(SZÖVEG.KERES(A2;$F$2))*HA(HOSSZ($F$2)-HOSSZ(HELYETTE($F$2;",";""))>=1;ELŐJEL(SZÖVEG.KERES(B2;$F$2));1)*HA(HOSSZ($F$2)-HOSSZ(HELYETTE($F$2;",";""))=2;ELŐJEL(SZÖVEG.KERES(C2;$F$2));1)A névkezelőben használt a megadott képletet a dinamikus tartományhoz.
A makró megoldásnál érdemes lenne figyelni, hogy csak akkor fusson le ha az F2-ben történik változás,
továbbá előnyös lehet ha akkor is adna találatot, ha csak kis-/nagybetűkben lenne eltérés.Egy alternatív megoldás pedig lehet a Pivot Slicerek használata.
Előnye, hogy nagyon vizuális. Hátránya, hogy frissíteni kell a pivotot ha van változás az autók listáján.
üdv
-
Mutt
senior tag
válasz
bteebi
#40532
üzenetére
Szia,
Segédoszlopokkal Excel 2010-el vagy frissebbel az alábbi lépéseken keresztül tudod a feladatot megcsinálni.
A K-M oszlopokban (szürke háttérrel) vannak a segédoszlopok, ezek mehetnek másik lapra/oszlopokba is.
Ha az F2-ben MINDIG vesszővel elválasztva adod meg a márka, típust és színt és sorrend is mindig ez, akkor a lenti képletek akkor is fognak eredményt adni, ha kevesebb kereső paramétert adsz meg (pl. "Audi, A4" esetén is felsorolja az odaillő rendszámokat).K2-ben a képlet ez:
=SOR()*ELŐJEL(SZÖVEG.KERES(A2;$F$2))*HA(HOSSZ($F$2)-HOSSZ(HELYETTE($F$2;",";""))=1;ELŐJEL(SZÖVEG.KERES(B2;$F$2));1)*HA(HOSSZ($F$2)-HOSSZ(HELYETTE($F$2;",";""))=2;ELŐJEL(SZÖVEG.KERES(C2;$F$2));1)L2-ben ez:
=ÖSSZESÍT(15;6;K:K;SOR()-1)M2-ben ez:
=HA(DARAB(L:L)=0;"Nincs találat";HA(SOR()-1<=DARAB(L:L);INDEX(D:D;L2);""))Ezeket elegendően sok sorba húzd le (legalább annyiban ahány kocsid most is van).
Az első képlet megnézi, hogy az A oszlopban lévő márka az F2-es cellában megtalálható-e, ha igen akkor plusz 1-et visszaad. Ha vesszővel felsorolva van még típus és eseteg szín is, akkor a B és C oszlopokokban lévő szövegeket is megnézni. A plusz 1-el pedig megszorozza annak a sornak a számát, amely a keresési feltételekre ráillik, a többieknél találat hiányában érték hiba lesz az eredmény.
Az L-oszlopban növekvő sorrendbe tesszük a K-ban kapott számokat és az ezekhez tartozó rendszámokat az M-oszlopban ki is iratjuk.
A következő lépés hogy a névkezelőben hozz létre egy változót, ami az M-oszlopból fogja majd venni a listát amit a G2-ben a legördülő listában lehet majd látni. Mivel a találati halmaz dinamikusan változik a megadott keresési feltételek esetén, ezért az ELTOLÁS függvényt kell segítségül hívni. A képlet amit a névkezelőben meg kell adnod:
=ELTOLÁS(Munka1!$M$2;;;DARABTELI(Munka1!$M:$M;">""")-1)Az utolsó lépés, hogy a G2-es cellában az adatérvényesítésnél a névkezelőben megadott névre hivatkozz.
üdv
-
#40528
Mutt
senior tag
szatocs1981
#40525
Mutt
senior tag
válasz
szatocs1981
#40525
üzenetére
Szia,
Excel 2016-tól ez a kért mintára műkődik.
=KÖZÉP(SZÖVEGÖSSZEFŰZÉS("";IGAZ;HA(($A$1:$A$1000="x")*(($B$1:$B$1000>0)+($C$1:$C$1000>0));HA($B$1:$B$1000>0;$B$1:$B$1000;$C$1:$C$1000);""));SOR();1)
Azonban félek, hogy a feladatot leegyszerűsítetted és az A,B,C,D értékek a valóságban nem 1 karakter hosszúak, esetleg nem is csak betűk. Ez esetben a fenti képlet nem fog műkődni.
A megoldás, hogy a névkezelőben létrehozol egy változót (én az eredmeny nevet használtam), aminek ez a képlete (ha van | jel a B-C oszlopokban, akkor itt és a következő képletben is egy olyan jelet használj ami nem fordul elő az oszlopokban):=SZÖVEGÖSSZEFŰZÉS("|";IGAZ;HA(($A$1:$A$1000="x")*(($B$1:$B$1000>0)+($C$1:$C$1000>0));HA($B$1:$B$1000>0;$B$1:$B$1000;$C$1:$C$1000);""))Ezek után az E1-nek a képlete ez lesz:
=KIMETSZ(KÖZÉP(HELYETTE(eredmeny;"|";SOKSZOR(" ";HOSSZ(eredmeny)));(SOR()-1)*HOSSZ(eredmeny)+1;HOSSZ(eredmeny)))üdv
-
Mutt
senior tag
Szia,
Jöttek már megoldások erre a kérdésre, de a lentieket segédoszlop nélkül is tudod használni.
Excel 2016-tól van MAXHA függvény:
=HA(DARABHATÖBB(A:A;A1;B:B;">0");MAXHA(B:B;A:A;A1);"")Excel 2007 és 2016 között pedig a SZORZATÖSSZEG-et használhatjuk helyette:
=HA(DARABHATÖBB(A:A;A1;B:B;">0");SZORZATÖSSZEG(MAX(($A$1:$A$1000=A1)*($B$1:$B$1000)));"")üdv
-
#40464
Mutt
senior tag
Laciahegyrol
#40463
Mutt
senior tag
válasz
Laciahegyrol
#40463
üzenetére
Szia,
Én nem tudok ilyen beállításról. Nekem egy cella tartalmát rendesen másolja át a Ctrl+C és Ctrl+V páros,
pl. a 40.00 Ft-ot egy form-ba, vagy egy mezőbe simán másolja formázva.
Ahol gond lehet az amikor több cellát akarsz másolni, mert akkor már táblázatként viszi át ami el tudja rontani a dolgokat.
Ebben a példában a formon táblazatos formátum kezelhetetlen, de a több mezőt tartalmazó táblázatban (jobb oldlat) rendben van.
Esetleg próbáld meg ezt a makrót, ami a kijelölt tartomány celláinak a formázott értékét egy hosszú szövegbe helyezi át.
Sub SAP_Copy()
Dim szoveg As String
Dim cella As Range
Dim sor As Long
szoveg = ""
sor = 0
For Each cella In Selection
If sor <> cella.Row And sor > 0 Then
szoveg = szoveg & vbNewLine
End If
szoveg = szoveg & cella.Text & " "
sor = cella.Row
Next cella
Call CopyText(szoveg)
End Sub
Sub CopyText(Text As String)
'VBA Macro using late binding to copy text to clipboard.
'By Justin Kay, 8/15/2014
Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
MSForms_DataObject.SetText Text
MSForms_DataObject.PutInClipboard
Set MSForms_DataObject = Nothing
End Subüdv
-
Mutt
senior tag
válasz
sz_abika
#40408
üzenetére
Szia,
Itt az én változatom a problémára:
Dim wsSource As Worksheet
Dim i As Long
Const wsName = " 1234"
i = 0
Do
i = i + 1
On Error Resume Next
Set wsSource = Sheets(Trim("xxx" & Mid(wsName, i, 1)))
On Error GoTo 0
Loop While wsSource Is Nothing And i < Len(wsName)
If wsSource Is Nothing Then
Call MsgBox("A keresett munkalap nem található", vbOKOnly, "Információ")
Else
ActiveCell.Offset(-2, 0).Value = wsSource.Range("A1")
End Ifüdv
-
-
Mutt
senior tag
Szia,
A második sorban ahol a napok sorszámát lehet látni 1-től 31-ig, azok számként formázott dátumok vagy csak sima számok?
Ha dátumok, akkor a végén továbbra is elég a végén lévő rész erre kicserélned erre (az AH oszlopban lévő képletet módosítsd erre):
;HA(HÓNAP.UTOLSÓ.NAP(AH$2;0)=AH$2;"áv";"é");"")Ha nem dátumok vannak, akkor a füzetben vhol kell rögzíteni hogy melyik évről és hónapról van szó ténylegesen. Én a B2 és C2 cellákba beírtam az évet (2019) és a vizsgált hónapot (3). Ha máshol van ez neked, akkor a lenti képletben cseréld ki a hivatkozásokat.
;HA(NAP(DÁTUM($B$2;$C$2+1;0))=AH$2;"áv";"é");"")üdv
-
Mutt
senior tag
Szia,
A HA-ban a végefelé, a
;"é";"")részbe kellene belenyúlnod és oda is tenni egy HA függvényt, ami az utolsó nap esetén az é-t áv-vel helyettesíti.pl. ha azt akarod nézni, hogy az aktuális nap az a hónap utolsó napja-e, akkor ezzel a képlettel cseréld le az eredeti képleted végét:
;HA(HÓNAP.UTOLSÓ.NAP(MA();0)=MA();"áv";"é");"")üdv
-
Mutt
senior tag
Szia,
FFeri megoldása teljesíti a kérésedet mindig az adott hónapra.
Ha neked az kell, hogy az egész évben melyik nap, esetleg napokon volt a legnagyobb fogyasztás, akkor pedig egy tömbfüggvényt tudok javasolni Excel 2016-tól felfelé:=SZÖVEGÖSSZEFŰZÉS(", ";IGAZ;HA(G2:AK13=MAX(G2:AK13);SOR(G2:AK13)-1&"."&$G$1:$AK$1;""))Vesszővel elválasztva felsorolja, hogy mely dátumokon volt a lenagyobb a fogyasztás.
üdv
-
Mutt
senior tag
Szia,
Amivel tudunk élni ez esetben, hogy az első szóközig hasonlítjuk össze a szövegeket a két oszlopan.
A
SZÖVEG.TALÁL(" ";A1)-1megmondja, hogy az A1 cellában lévő szövegben hol van az első szóköz.
Ha ezt egy BAL függvénybe tesszük, akkor pedig megkapjuk a cella tartalmát az első szóközig.
Viszont lehet, hogy nincs is szóköz a keresett cellában; ezt is lekezelve ez lesz a teljes képlet (Excel 2007-től felfelé):=BAL(A1;HAHIBA(SZÖVEG.TALÁL(" ";A1)-1;HOSSZ(A1)))Ugyanezt kell megcsinálni a másik oszlopban lévő szövegre és a végén a két értéket összehasonlítani:
=BAL(A1;HAHIBA(SZÖVEG.TALÁL(" ";A1)-1;HOSSZ(A1)))=BAL(B1;HAHIBA(SZÖVEG.TALÁL(" ";B1)-1;HOSSZ(B1)))üdv
-
Mutt
senior tag
válasz
Faterkam
#40368
üzenetére
Szia,
Az Excelben az időformátum felépítése: óra:perc:másodperc,századmásodperc
Így "2:13:70"-es értéket 2 óra 13 perc és 70 másodpercnek gondolja, amiből lesz 2 óra 14 perc 10 másodperc.Ha neked 2 perc és 13,70 másodperc kell, akkor a cellába így vidd be az értéket: 0:2:13,7
Alapból a törtmásodpercek nem fognak megjelenni, így a cellán állva Ctrl-1-et nyomsz és az Egyéni számformátumnak ezt add meg "pp:mm,00".
üdv
-
Mutt
senior tag
Szia,
...a számoknál a tizedesjegynél még mindig a magyar vesszőt használja a pont helyett. Létezik,hogy ezt már csak a globális windows nyelvi beállításoknál lehet orvosolni?
Excel 2010-től közvetlenül is be lehet állítani a tizedespont és ezreselválasztót; ezzel függetlenítve ezt a beállítást a globálistól.
File -> Options -> Advanced -> Editing options -> Use system separatorüdv
-
Mutt
senior tag
Szia,
Jobb klikk a "Monthly Budget Report" lapon a slicer alatt található Pivot-on és beállítások részben az Data/Adatok fülön a törölt elemek megőrzését automatikusról állítsd át None/Semmennyire.
Az 'Additional data' fülön a 'PivotTable for Budget Overview chart' táblázatban nem tudom ugyanazokat a szavakat beírni mint a mellette levő táblázatban,
Ezen a fülön csak az E-oszlopban lévő kategóriákat módosítsd, majd pedig a "Monthly Expenses" fülön a kiadásokat a C-oszlopban tedd a megfelelő kategóriákba (ha egy kategória nevét módosítottad az utolsó fülön), akkor itt kézzel újra ki kell választanod a kategóriát.
Ezek után Pivot frissítés és a többi fülön mindenhol frissülnek a kategóriák.üdv
-
Mutt
senior tag
válasz
bozsozso
#40280
üzenetére
Ha tudod, hogy maximum egy gyümölcshöz mennyi szám lehet, akkor Power Pivot-os lépések (szintén Excel 2010 felett csak).
1. Alakítsd át táblázattá a listát.
2. Beszúrás -> Kimutatás opció és pipáld be az adatmodellhez hozzáadást.
3. Jobb klikk az adatforráson és Új mérték hozzáadása.
4. Mérték űrlapot így töltsd ki.
5. Egy újabb mértéket kell felvennünk mint a 3-as lépésben. A képlete ennek:
=PATHITEM([Lista];1;0)
A PATHITEM függvény a lista adott elemét adja vissza, a fenti képletben az első elemet kértük le.
Itt jön a feltételezés, hogy tudod hány szám lehet egy gyümölcshöz, mert az 5-ös lépést legalább ennyiszer kell ismételni. A képletben mindig csak növelni kell eggyel számot, vagyis a második elemnek a képlete:=PATHITEM([Lista];2;0)A harmadiknak pedig:
=PATHITEM([Lista];3;0)A példában 5x megcsináltam és a kimutatást összeraktam.
6. Már csak meg kell fordítani a kimutatást, ebben a TRANSZPONÁLÁS függvény tud segíteni.
Itt arra kell figyelni, hogy ahány sort és oszlopot kell egy másik helyre vinni, ott ugyanakkora területet kell kijelölni (egész pontosan annyi oszlopot mint ahány sor van a másik helyen és annyi oszlopot mint amennyi sor van a másik helyen). A példámban 5 sor és 5 oszlop van (nem kell a fejlés és totál sor sem).Javasolt lépések:
7. Írd be a végső hely első cellájába TRANSZPONÁLÁS függvényt, kapni fogsz egy hibát.
8. Ezen a cellán állva jelölj ki egy 5x5-ös tartományt.
9. Nyomd meg az F2-öt, ekkor az első cellában látható képlet megjelenik és ekkor Ctrl + Shift + Enter-t nyomj. A kijelölt terült egy összefüggő tömb része lesz, módosítani a cellákat benne egyesével nem fogod tudni.
A végeredmény egy kis formázás után pedig ilyen lesz:
Ha zavarnak a nullák, akkor egy számformátummal el lehet tüntetni őket.
üdv
-
Mutt
senior tag
válasz
bozsozso
#40280
üzenetére
Szia,
Excel 2010-től fölfelé a Power Query-vel megoldható. A lépések:
1. A listádat alakítsd át táblázattá.
2. Adatok fülön beolvasás Táblázatból/Tartományból.
3. Gyümölcsök oszlop kijelölése után Csoportosítás opció.
4. A megjelenő ablakot töltsd ki így:
5. Oszlop hozzáadása fülön Egyedi oszlopot válaszd és így töltsd ki. A [Lista] az előző lépésben megadott név, a [Számok] pedig az eredeti oszlop neve ahol vannak a számok.
6. Kattints az új oszlop jobb felső sarkában lévő ikonra, és válaszd az Értékek kinyerése opciót. A megjelenő ablakban válassz egy elválasztó karaktert, én a kettőspontot használtam most.
7. Jobb klikk a Lista oszlopon és eltávolítás.
8. Jelöld ki a Szám oszlopot és Átalakítás menüben Oszlop felosztása opció elválasztó karakter alapján.
9. Átalakítás menűben Transzponálás opciót használd.
10. Kezdőlapon használd az Első sor használata fejlécként.
11. Utolsó lépés kirakni egy munkalapra, Bezárás és betöltés -> ..adott helyre.
Egymás mellett az eredeti és a végső állapot:
üdv
-
#40276
Mutt
senior tag
South Velen
#40252
Mutt
senior tag
válasz
South Velen
#40252
üzenetére
Szia,
Pivotnál van olyan lehetőség, hogy két különböző sheetről gyűjtse be az azonos jellegű adatokat?
Nézd meg ezt a leírást.
Alternatíva lehet még Power Query használata.
üdv
-
Mutt
senior tag
válasz
p5quser
#40270
üzenetére
Szia,
Előbb próbáld ki ezt a cserét:
Selection.Value = Application.InputBox("Darabszám", Type:=1)Ha ez nem jó, akkor a Selection.Value helyébe tedd ezt:
vresult = InputBox("Darabszám")
If InStr(1, vresult, ",") Then
Selection.Value = CDbl(Left(vresult, InStr(1, vresult, ",") - 1) & Application.DecimalSeparator & Right(vresult, Len(vresult) - InStr(1, vresult, ",")))
Else
Selection.Value = vresult
End Ifüdv
-
Mutt
senior tag
Szia,
Excelben és nagyon sokszor kell a szavak és/vagy a karakterek (leütés) száma.
Nem igazán szöveg szerkesztő az Excel, de meg lehet oldani ezt is.
1. Szavak száma: szóközöket kell megszámolni igazából. Erre a régi megoldás az, hogy a szövegből a töröljük HELYETTE függvénnyel a szóközöket és megnézzük mennyivel csökkent a szöveg hossza:
=HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";""))+12. Karakterek száma: itt ha élünk azzal hogy csak a SZÓKÖZT nem vesszük leütésnek, de a kódtábla többi karakterét - még a nem nyomtathatókat is - igen, akkor ez a képlet segít:
=SZORZATÖSSZEG((KÓD(KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1))<>32)+0)Ha csak adott karakterek érdekelnek, akkor kell egy kicsit játszani. pl. csak számokat ez adja vissza:
=SZORZATÖSSZEG((((KÓD(KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1))>=48)+(KÓD(KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1))<=57))=2)+0)3. Ha szöközökkel együtti karakterek száma kell, akkor pedig a HOSSZ képlet tud segíteni:
=HOSSZ(A2)
üdv
-
Mutt
senior tag
Szia,
Makró nélküli megoldások erre a problémára:
1. Tömbfüggvény: az E-oszlopba előbb fel kell vinned ami alapján csoportosítani akarsz.
=TEXTJOIN(", ";TRUE;IF($A$1:$A$3000=E1;$B$1:$B$3000;""))3000 soron is gyorsan lefut, előnye hogy automatikusan frissül, viszont a duplikációkat nem szűri.
2. Power Pivot: ezt kézzel kell frissíteni, képes a duplikácókat is kiszűrni ha szükséges.
a. Kell egy fejléc az első sorba és táblázattá kell alakítani az adatsort.
b. Beszúrás -> Kimutatás (Pivot) de itt fontos hogy legyen bepipálva alul az adatmodellhez hozzáadás.
c. Az A-oszlop kerül a sorok részbe.
d. Jobb klikk a kimutatás tervezőben az adatforráson (nekem Table1-nek hívja) és Add measure (Új mérték)
e. Az ablakot így töltsd ki:
A CONCATENATEX függvényben az első paraméter a forrás neve (jelen esetben Table1-ben vannak az adatok), a második a mező amit összekell fűzni (ez a Table1-en belüli Érték oszlop), a végén pedig hogy mivel legyenek az értékek elválasztva.
Ha az ismétlődéseket nem szeretnéd listázni, akkor a forrást előbb vagy a VALUES vagy a DISTINCT függvényen kell végig pörgetni:
=CONCATENATEX(VALUES(Table1[Érték]);Table1[Érték];", ")=CONCATENATEX(DISTINCT(Table1[Érték]);Table1[Érték];", ")f. Az új mértéket húzd be a values / értékek részbe.
g. Formázd az eredményt szükség szerint.3. Power Query: ez sem fog automatikusan frissülni, de itt is tudsz ismétléseket kivenni vagy akár sorrendet módosítani.
a. Kell egy fejléc az első sorba és táblázattá kell alakítani az adatsort.
b. Data -> From table (Adatok -> Beolvasás táblázatból) opciót használd.
c. Ha szeretnéd az ismétléseket kiszűrni, akkor jelöld ki mindkét oszlopot és Home -> Remove rows -> Remove duplicates.
d. Ha szeretnéd, hogy növekvő/csökkenő sorrendben legyenek az értékek kiíratva, akkor pedig.jelöld ki a második oszlopot és Home menű alatt válaszd a megfelelő sorbarendezést.
e. Jelöld ki az első oszlopot és Home -> Group by opciót használd így:
f. Add column -> Custom column opcióval kell egy új oszlopot beszúrni.
g. Az új oszlop jobb sarkában lévő ikonra kattints és válaszd az Extract values opciót.
h. Add meg az értékek közötti elválasztó jelet.
i. Töröld a felesleges középső oszlopot. Jobb klikk rajta és Remove.
j, Jelöld ki az első oszlopot és rendezd ABC sorrendbe ha szükséges.
k. Végül Home menűben a Close & Load gomb alatt válaszd a Close & Load to opciót és add meg hol jelenjen meg az eredmény.
Ha frissíteni kell ezt lekérdezést, akkor pl. a Data fülön a Refresh All-al tudod megtenni.
üdv.
-
Mutt
senior tag
válasz
benkez
#40008
üzenetére
Szia,
Ha megpróbálod megnyitni az eredeti fájlodat Excelben, akkor a szövegbeolvasó varázsló tud segíteni, hogy az egyszerűbben tagolt adatokat táblázattá alakítsd.
Ha már összetettebb a fájl, akkor a Excel 2010-től van Power Query, ami a feldolgozást segíti.
Nézd meg ezt a leírást.üdv
Új hozzászólás Aktív témák
Hirdetés
- Milyen asztali (teljes vagy fél-) gépet vegyek?
- Samsung Galaxy S25 Ultra - titán keret, acélos teljesítmény
- Négymagos Xeon(!) CPU LGA775-ös deszkában
- Azonnali alaplapos kérdések órája
- Feketehalálra váltja a kékhalált a Microsoft
- 3D nyomtatás
- Házimozi belépő szinten
- AliExpress tapasztalatok
- The First Berserker: Khazan
- Friss koncepciót hoz a Nothing Phone (3)
- További aktív témák...
- Gyermek PC játékok
- Assassin's Creed Shadows Collector's Edition PC
- ROBUX ÁRON ALUL - VÁSÁROLJ ROBLOX ROBUXOT MÉG MA, ELKÉPESZTŐ KEDVEZMÉNNYEL (Bármilyen platformra)
- Windows, Office licencek kedvező áron, egyenesen a Microsoft-tól - Automata kézbesítés utalással is!
- Windows 10 11 Pro Office 19 21 Pro Plus Retail kulcs 1 PC Mac AKCIÓ! Automatikus 0-24
- BESZÁMÍTÁS! Apple Mac mini 2024 M4 16GB 256GB SSD számítógép garanciával, hibátlan működéssel
- Bomba ár! HP EliteBook 840 G5 - i5-8G I 8GB I 128GB SSD I 14" FHD Touch I HDMI I Cam I W11 I Gari!
- ÁRGARANCIA!Épített KomPhone Ryzen 5 5600X 16/32/64GB RAM RX 7600XT 16GB GAMER PC termékbeszámítással
- Telefon felvásárlás!! iPhone 11/iPhone 11 Pro/iPhone 11 Pro Max
- BESZÁMÍTÁS! Apple MacBook Pro 14 M4 MAX 36GB RAM 1TB SSD garanciával hibátlan működéssel
Állásajánlatok
Cég: PC Trade Systems Kft.
Város: Szeged
Cég: CAMERA-PRO Hungary Kft
Város: Budapest