- sziku69: Fűzzük össze a szavakat :)
- Elektromos rásegítésű kerékpárok
- eBay-es kütyük kis pénzért
- sziku69: Szólánc.
- gerner1
- pusszycat: JEYI - okos, színes, interaktív, ssd külső ház.
- Luck Dragon: Asszociációs játék. :)
- gban: Ingyen kellene, de tegnapra
- Sub-ZeRo: Euro Truck Simulator 2 & American Truck Simulator 1 (esetleg 2 majd, ha lesz) :)
- hcl: Philips M120D/10 kamera hackelés és parajelenségek
-
LOGOUT
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
-
#77257183
törölt tag
válasz
Fferi50
#51392
üzenetére
Szia! Köszi, hogy időt szántál rá.
A segédoszlopokat sikerült a mintád alapján a saját táblázatomba megcsinálnom.
Viszont továbbra sem teljes a kép, hogy azokat miként kell a Szumhatöbb és Darabhatöbb függvényekbe használni. Hogyan kapom meg automatikusan, hogy x csapat utolsó öt meccsén pl. hány gólt lőtt vagy mennyi győzelmet szerzett. -
-
#51396
rovgab
csendes újonc
Fire/SOUL/CD
#51388
rovgab
csendes újonc
válasz
Fire/SOUL/CD
#51388
üzenetére
Köszönöm! ezzel egy lépéssel közelebb kerültem a megoldáshoz, de a "mind nulla" kivételével mindenhova #értéket ír.
-
underdark
aktív tag
Sziasztok
Egy kis segítség kéne, mert istenért nem ugrik be a megoldás, pedig régen csináltam ilyesmitSzóval adott egy nagyobb adatbázis több száz sorból, 3 oszlopból áll
Példaként
Gép, Új, Régi
Az új es régi oszlopban egy x szerepel, azt egy szamlalonak szánom, mikor hogy
Azt kéne megcsinálnom, hogy ha a gép résznél motor szerepel az hanyszor új es hányszor régi abból a több százból
Ha fék akkor az mennyiszer lett új stb.Nem tudom.mennyire érthető
-
Fferi50
Topikgazda
válasz
#77257183
#51391
üzenetére
Szia!
Arra van szükséged, hogy a legutolsó x mérkőzés kezdő sorát meghatározd a tartományod számára. Csak a tartomány meghatározásra egyszerűsítve a feladatot segédoszloppal lehet megoldani szerintem:
F2 cella képlete:=NAGY(HA($B$2:$B$127=$E2;SOR($B$2:$B$127);HA($C$2:$C$127=$E2;SOR($B$2:$B$127);""));5)
Ez a tömbképlet megadja, hol található a tartomány végétől visszafelé számolva az 5. olyan sor, amelyben az adott csapat neve van. Mivel mindkét oszlopban szerepelhet, ezért kell a kétszeres ha függvény. Ha csak az egyik oszlopot (hazai/vendég) szeretnéd tudni - utolsó x hazai ill. vendég eredményt, akkor csak az adott oszlopra kell a ha függvényt felírni.
A tömbképletet Shift+Ctrl+Enter kombóval kell lezárni, az Excel pedig kapcsos zárójelbe teszi.
A H2 cella képlete:=DARABTELI(INDIREKT("$B"&F2):$C$127;$E2)
Ez is húzható lefelé.
Mutatja, hogyan alkalmazd a tartomány meghatározásához a segédoszlopot az indirekt függvénnyel. Itt csak azt mutatja, hogy valóban 5 darab tétel van az adott sortól kezdődő tartományban.
Üdv. -
-
Xterms
tag
Sziasztok!
Van egy macros excel-em, eleg egyszeru, nehany gomb van benne, amiknek a lenyomasakor megvaltozik a gomb szine, ennyi a kod:
Private Sub ToggleButton2_Click()
With Me.ToggleButton2
Me.Columns("AH:AJ").EntireColumn.Hidden = .Value
.BackColor = IIf(.Value, vbRed, vbGreen)
End With
End Sub
Ezt szeretnem androidon barmilyen formaban ugyanilyen funkcionalitassal hasznalni. Nem sok remenykeltot olvastam a neten az androidos excel es a macrok kapcsolatarol, hatha itt van mas otlet.Köszönöm,
X -
#51388
Fire/SOUL/CD
félisten
rovgab
#51387
-
rovgab
csendes újonc
Sziasztok!
Adott egy 6x37-es cellatartomány. Minden sorban van egy adott érték meg mellette 5 nullás cella (vagy néhány esetben mind nullás). A hetedik oszlopba szeretném kiszedni az adott értékeket, mert engem a nullák nem érdekelnek. Tulajdonképp ki szeretném szűrni soronként. Először a szűrő függvényre gondoltam, de az a régebbi excelekben még nincs. Tudnátok valami alternatívát mondani? Előre is köszönöm! Alább egy példa:
-
#77257183
törölt tag
Sziasztok.
Alapszinten vagyok az excelben. Az excel szakértőket szeretném kérdezni egy feladat megoldásához.
Adott egy bajnoki eredményeket tartalmazó táblázat. Az első munkafűzeten a dátum és a mérkőzesek előre vannak beírva. Az első csatolt képen ez látható.
A lényeg, hogy csak a gólokat kell beírnom és automatikusan számol az excel és megkapom a bajnoki tabellát.
Az megoldottam, hogy az összes mérkőzést beleszámitva, hogyan változik aktuálisan a tabella. A második munkafűzeten illetve a második csatolt képen példaként a hazai meccseket számoltam ki függvényekkel.
Viszont szükségem lenne olyan tabellákra is, ahol csak pl. az utolsó öt mérkőzést számolja az excel minden csapatra külön hazai és vendég meccsekre , hogy látni lehessen a csapatok aktuális formáját. Itt megállt a tudományom. Valakinek van ötlete?
-
Delila_1
veterán
válasz
-szabi-
#51383
üzenetére
Szia!
Ha ezt egyszer kell összeszámolni, akkor szűrd az oszlopot színre, majd a másikat a szövegre. A RÉSZÖSSZEG függvény megadja a darabszámot.
Ha viszont állandó jellegű a feladat, akkor egy oszlopba a színnek megfelelően vigyél be 1; 2; vagy 3 értéket. Akkor a DARABHATÖBB függvénnyel oprálhatsz. -
-szabi-
addikt
Sziasztok
Egy oszlopban zöld és sárga cellák vannak.
Szeretném megszámolni mennyi színes cella, mennyi sárga cella és mennyi zöld cella van.
A zöldekre százalékot számolnák.
Hogy bonyolultabb legyen a mellette lévő oszlopban mindegyik cellában igen vagy nem szöveg szerepel.
Csak azokat a színes cellákat kellene figyelembe vennem számolásnál amelyik mellett igen szövegű cella van.
Ehhez tudnátok egyszerű módszert javasolni?
Előre is köszönöm.üdv
szabi -
#51382
lappy
őstag
minimumgame
#51381
lappy
őstag
válasz
minimumgame
#51381
üzenetére
Igy is lehet
-
#51381
minimumgame
tag
minimumgame
tag
Sziasztok!
Van két ugyan olyan excel dokumentum. Viszont az egyikbe kerültek változások, frissítések, stb, és a másikba is különféle változások kerültek, pl. név, város, stb.
A kettőt kellene egyesíteni, azaz, hogy újra egy dokumentumot kelljen szerkesztgetni.
Hogyan lehetséges ez a legegyszerűbben? Köszi a választ -
Mutt
senior tag
válasz
Zazunga
#51377
üzenetére
Szia,
A BK18-as cellában állsz és a képleten látszik, hogy a 19-es sorból olvassa az adatokat, ami hibás.
Írd át 18-asra, illetve érdemes lenne a KERES-ben rögzíteni a tartományt, mert el fog mászni másoláskor.
=HA(BI18="";"";DARAB(BI$18:BI18)+KERES(2^10;BE$18:BE$56))Érdemes lenne az félidős tábla végén (57-es sor?) az eredményt kiíratni és a második félidőben ezt az értéket továbbvinni, mint állandóan KERES-el visszanézni az eredményt.
üdv
-
Zazunga
újonc
válasz
Zazunga
#51370
üzenetére
A következő hibát találtam még. Ha eléri a 2.félidős adatkitöltés az 1.félidőt, onnantól "#hiányzik" jelenik meg a gólszám mezőben. Az össz eredményhez hozzászámolja, de vizuálisan nem jelenik meg.
A képen a használt függvények zölddel, pirossal a cella azonosító
MI lehet a hiba?
Köszönöm -
Geryson
addikt
Sziasztok!
Van egy cellaértékem, aminek a tagoltsága mindig a következő:
xxxxxx-xxx_211022
Szeretnék kérni 3 db függvényt, hogy elmentsem őket:
- az egyiknél a kötőjel előtti részt kellene csak eredményként hoznia (a hossz változó, de mindig a kötőjel előtt van)
- a következőnél a kötőjel és az alsóvonás közötti rész kellene
- a harmadiknál meg a dátumos rész, ami mindig az alsóvonás után vanNagyon szépen köszönöm!
-
Mutt
senior tag
válasz
Zazunga
#51368
üzenetére
Szia,
Ha a 3. sortól indul a gólszerzők felvitele, és a gólok a D:E oszlopokba kerülnek, akkor
D3-ban a képlet: =HA(B3="";"";DARAB(B$3:B3)) ezt húzhatod le.
Az eredeti kérdésre: "..hogyan tudom megtalálni az akár több üres sorral fentebb lévő utolsó gólértéket"
Változatok:
1. Mivel növekvő sorszámokról van szó ezért a MAX függvény tud segíteni. pl. a 7-es sorban: =MAX(D3:D6)
2. Legutolsó kitöltött cellaértéke: =KERES(2^10;D3:D6)üdv
-
Zazunga
újonc
Üdv!A mellékelt kép alapján tudnám vázolni a megoldandó feladatot:
-azt szeretném, hogy amennyiben a "MEZ" oszlop A vagy B részébe beírom kézzel az aktuális gólszerző mezszámát, akkor a "GÓL" oszlop megfelelő mezőjébe automatikusan töltődjön a gólok száma.Az megvan, hogy a a "HA" függvény "logikai vizsgálat"-ban utalok a MEZ oszlop A és B mezőire. Ha "nem" a válasz, akkor üres a mező.
De ha "igen" a válasz, akkor hogyan tudom megtalálni az akár több üres sorral fentebb lévő utolsó gólértéket, amihez +1-et adok?Köszönöm a segítséget
-
repvez
addikt
valaki képben van a google sheets használatában?
Miért kapom azt a hibát, hogy ha egy adatot weblaprol akarok behuzni, hogy az érték amit beir a cellába szoveg és nem szám? és nem alakithato számmá.hiába állitom be a cella tipusát számra akkor sem változik semmi.
De van hogy akkor is ezt a hibát irja ki amikor egy sima cellába irok egy értéket, de ha egy jo cellábol huzom le , hogy ugyan azt legyen a cella akkor ugy meg jó.De mihelyt meg akarom az értéket manuálisan változtatni megint azt irja , hogy szöveg.A másik kérdésem, hogy egy weblapnál ahol táblák szerepelnek adatokkal, ott hogy lehet megtudni, hogy az adott tábla az hányadik amire hivatkozni kell? Mert a sor és oszlop az tiszta, de ha össze vissza vannak az oldalon a táblázatok akkor ott mi a sorrend?
-
Mutt
senior tag
Sziasztok,
51352-es kérdésben rangsor és korreláció számításhoz adtunk Excel függvényes megoldást, de közben készült egy Power Query (PQ) megoldás is.
A probléma az, hogy PQ-ben nincs se korrelációs, se rang.átlag függvény. Hasonlók vannak, de nem ugyanazok. Természetesen lehet saját függvényeket létrehozni és most ezt szeretném bemutatni azoknak akiket érdekel.
A saját függvény egy lekérdezés, csak úgy mint a többi, de itt nekünk kell az M-kódot (PQ nyelvezete) írni.
A korreláció függvény kódja ez lett://the formula is available on https://support.microsoft.com/en-US/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
(numbers1 as list, numbers2 as list) as number =>
let
avarage1 = List.Average(numbers1),
avarage2 = List.Average(numbers2),
x1 = List.Transform(numbers1, each _ - avarage1),
y1 = List.Transform(numbers2, each _ - avarage2),
x = List.Zip({x1, y1}),
nominator = List.Sum(List.Transform(x, each _{0} * _{1})),
denominator = Number.Sqrt(List.Sum(List.Transform(x, each _{0} * _{0})) * List.Sum(List.Transform(x, each _{1} *_ {1}))),
result = nominator / denominator
in
resultAz elején megadjuk hogy milyen inputokat fogad el a függvény, majd jönnek a számítási lépések (átlagot számolunk, majd eltérést az átlagtól, majd négyzetes eltérést és a végén már csak osztanunk kell.
Többször használtam a List.Transform iterátort, amely ahogy végig megy a lista elemein kiszámolja az értékeket a transzformációs függvény alapján.Használata utána már olyan egyszerű, mint az Excel függvényé.
Rangsorhoz van beépített megoldás (Table.AddRankColumn), de nem úgy működik mint a RANG.ÁTL, így ezt is inkább megcsináltam függvénnyel. Ennek egy kicsit csúnyább lett a kódja, mert próbáltam meg dinamikusra csinálni illetve hogy egyszerre számolja ki a rangsort két különoszlopra.
(input as table, sort1 as text, sort2 as text) as table =>
let
Source = input,
//memoize the original columns for rearrange
OriginalColumnOrder = Table.ColumnNames(Source),
//add index to be able to restore original
AddIndex = Table.AddIndexColumn(Source, "RowID", 1, 1, Int64.Type),
//sort the table by given criteria
SortBy1 = Table.Sort(AddIndex, {sort1, Order.Descending}),
//add ranking column
AddRankIndex1 = Table.AddIndexColumn(SortBy1, "Rank", 1, 1, Int64.Type),
//calculate the average rank (as excels RANK.AVG function)
GroupBy1 = Table.Group(AddRankIndex1, sort1, {
{Text.Combine({"Rank ", sort1}), each List.Average([Rank])},
{"Data", each _, type table}
}),
//get the list of the columns what we need for the next step
Columns1 = List.RemoveItems(Table.ColumnNames(GroupBy1[Data]{0}), {sort1, "Rank"}),
//by expanding the columns we are
Expand1 = Table.ExpandTableColumn(GroupBy1, "Data", Columns1),
//do the same things for the other column
SortBy2 = Table.Sort(Expand1, {sort2, Order.Descending}),
AddRankIndex2 = Table.AddIndexColumn(SortBy2, "Rank", 1, 1, Int64.Type),
GroupBy2 = Table.Group(AddRankIndex2, sort2, {
{Text.Combine({"Rank ", sort2}), each List.Average([Rank])},
{"Data", each _, type table}
}),
Columns2 = List.RemoveItems(Table.ColumnNames(GroupBy2[Data]{0}), {sort2, "Rank"}),
Expand2 = Table.ExpandTableColumn(GroupBy2, "Data", Columns2),
//restore the table to original sequence
OriginalSort = Table.Sort(Expand2, "RowID"),
RemoveExtraColumn = Table.RemoveColumns(OriginalSort, "RowID", MissingField.Ignore),
//reorder the columns to original logic
ReOrderColumns = Table.ReorderColumns(RemoveExtraColumn, List.Combine({OriginalColumnOrder, {Text.Combine({"Rank ", sort1})}, {Text.Combine({"Rank ", sort2})}}))
in
ReOrderColumnsItt ahogy látszik egy adattáblát adunk át a függvénynek és a két oszlop nevét, amelyekre sorrendet kell meghatározni. A sorrend meghatározás 4 lépésből áll:
1. csökkenő érték szerint sorba rendezzük a táblát
2. hozzáadunk egy sorrend (Rank) oszlopot a táblához.
3. csoportosítjuk érték szerint az adatokat és a sorrend átlagát számoljuk ki közben.
4. kibontjuk a csoportosítot adatokat.Ezt pedig így lehet használni.
üdv
-
Delila_1
veterán
Egy másik megoldás Fire-éhez képest, hogy a laphoz rendelsz egy kódot (lásd az Összefoglalóban).
Private Sub Worksheet_Change(ByVal Target As Range)Application.EnableEvents = FalseIf Target.Address = "$G$1" And Target = "" ThenRange("A1").AutoFilter: Range("A:E").AutoFilterRange("G1:K1").ClearContentsApplication.EnableEvents = TrueExit SubEnd IfIf Target.Row = 1 ThenSelect Case Target.ColumnCase 7ActiveSheet.Range("$A:$E").AutoFilter Field:=1, Criteria1:=Target.ValueCase 8ActiveSheet.Range("$A:$E").AutoFilter Field:=2, Criteria1:=Target.ValueCase 9ActiveSheet.Range("$A:$E").AutoFilter Field:=3, Criteria1:=Target.ValueCase 10ActiveSheet.Range("$A:$E").AutoFilter Field:=4, Criteria1:=Target.ValueCase 11ActiveSheet.Range("$A:$E").AutoFilter Field:=5, Criteria1:=Target.ValueEnd SelectEnd IfApplication.EnableEvents = TrueEnd Sub
G1-be beviszed az A oszlop egyik értékét, mire az autoszűrő szűri erre az állományt. A H1-be a szűrt állomány B oszlopából veszed a következő értéket, ekkor folytatódik a szűrés a B oszlopra is. És így tovább a K oszlopig, amikor A-tól E-ig minden oszlop szűrve van.
Mikor törlöd a G1 cella értékét, a szűrő "kinyit", a G1: K1 tartományból törlődnek az értékek.
-
#51361
Fire/SOUL/CD
félisten
vamzi
#51360
-
vamzi
senior tag
Sziasztok,
Van valakinek arra valami ötlete, hogy hogyan tudnék egymástól függő legördülőmenüket csinálni? Fa struktúrájú adatszerkezetről van szó:
A B C D E1 A 1 7 0 1
2 A 2 8 1 2
3 A 6 9 2 3
4 A 4 0 3 1
5 A 5 0 4 1
6 A 6 0 4 *1
7 B 7 1 1 1
8 B 8 1 1 2
9 B 9 1 1 3
10 B 0 1 2 1
11 B 1 2 1 1
12 B 2 1 1 1
13 C 1 1 1 1
14 C 1 1 1 2
15 C 1 1 1 3
16 C 1 1 2 1
17 C 1 2 1 1
18 C 2 1 1 1A terv, hogy az 5 oszlop kapna 1-1 legördülő menüt, aminek a tartalma mindig az azt megelőző kiválasztástól függene. Tehát, hogy elérjem az E6 cellát, az
1. kiválasztásnál [A;B;C] az "A" értéket választom,
2. kiválasztásnál [1;2;4;5;6] a "6" értéket választom,
3. kiválasztásnál [0;9] a "0" értéket választom,
4. kiválasztásnál [4] a "4" értéket választom,
5. kiválasztásnál [1] az "1" értéket választom.Az A oszlop 17 különböző értéket tartalmaz
A B oszlop 21 ismétlődő értéket tartalmaz
A C oszlop 135 ismétlődő értéket tartalmaz
A D oszlop 126 ismétlődő értéket tartalmaz
Az E oszlop 30 ismétlődő értéket tartalmazUgyan úgy kellene működnie, mint az excel Filter funkciónak.
Az elvégezni kívánt feladat: A fenti táblázat egy szabályrendszer, ami mentén kódokat kell előállítani. A fenti leírás alapján az előállt kód "A6041".
Az egyetlen megoldás, amit ismerek, ha összecsoportosítom a cellákat, táblázatként formázom és indirekt behivatkozom a legördülőmenüben. Viszont ez túl nagy munka és még rugalmatlan is lenne.
Van ötletetek?
Köszi,
Ádám. -
Mutt
senior tag
Szia,
"...mi alapján működik a tartományok elkülönítése?"
A DARABHATÖBB esetén az első két páros feltétel ($B:$B;$B2 és $D:$D;$D2) válogatja ki, hogy az adott sor melyik tartományba kerül, mivel mindig az aktuális sor értékelőivel ($B2 és $D2 feltétel, ami lefelé húzva mindig az aktuális sorhoz fog igazodni) megegyező értékeket mutat. A DARABTELI/DARABHATÖBB kevésbé használt funkciója, hogy nem csak a konkrét egyezéseket lehet megszámolni hanem lehet kisebb vagy nagyobb relációt is használni és ezzel sorrendet is lehet számolni.
A korrelációhoz más megoldás kell, mivel oda cellatartományt (range) kell megadni, amit INDEX / ELTOLÁS / XKERES-al szoktunk meghatározni dinamikusan. Itt a segít az ÖSSZESÍT függvény, amely tud tömb-műveleteket elvégezni (ezek olyan műveletek amikor egy lépésben több cellával dolgozunk és eredményként az összes cellához kapott értéket megkapjuk). Előnye még ennek a függvénynek, hogy nem kell Control+Shift+Enter (CSE)-rel bevinni (a SZORZATÖSSZEG még ilyen), a többi függvényhez viszont Excel 2021-ig kell használni. Végül pedig egy hasznos opciója az ÖSSZESÍT-nek hogy a hibás értékeket tudja kezelni, ez a második paraméter (a 6 azt jelenti, hogy ugorja át a hibás értékeket).
A képletben HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0) a lényeg a tartományok kiválasztásánál, ugyanúgy mint a DARABHATÖBB-nél az aktuális sor értékelőit keresi meg a B és D-oszlopokban (pontosabban az első 10 ezer sorában). Ha egyezik akkor 1-et ad vissza, ha nem akkor 0-t. A két feltétel között szorzás van, ami az ÉS kapcsolatnak felel meg (csábító lenne az ÉS függvény használata helyette, de az nem add vissza tömböt. Megjegyzés: haa VAGY kapcsolatra lenne szükség akkor plusz jel kellene ide.). Szóval kapunk 10 ezer db eredményt amik között lesz 1 és 0, ezzel ha elosztjuk a sorszámokat, akkor ahol egyezés van ott számot kapunk minden más esetben a 0-val osztás miatt hibát. Ekkor jön a 6-os opciója az ÖSSZESÍT-nek, ami csak a számokat tartja meg ahonnan már csak a legkisebb és legnagyobb számokat kell megtartanunk hogy tudjuk mettől-meddig tart a tartomány.
A végén az INDEX-el használom ezeket (ELTOLÁS is lehetne ahogy Fferi mutatja, de ezzel az a gond, hogy lassítja a füzetet, miert minden esetben újraszámolja az értékeket feleslegesen. Angolul [L:https://www.youtube.com/watch?v=Jev5ATXwnOs]Mynda videóját[/L] javaslom erről).
üdv -
Fferi50
Topikgazda
Szia!
Csak hogy még jobban főjön a fejed, itt egy másik megoldás:
A könnyebb képletezés miatt két tartományt a névkezelőben neveztem el:
értékelő1=ELTOLÁS(Munka1!$B$1;0;0;DARAB2(Munka1!$B$1:$B$3000);1)
értékelő2=ELTOLÁS(Munka1!$D$1;0;0;DARAB2(Munka1!$D$1:$D$3001);1)
A két név mindig az adott oszlop aktuálisan kitöltött részére hivatkozik. Ha 3000-nél több sorodban lenne adat, akkor légy szíves módosítani a 3000-t a végén. FIgyelj a $ jelekre kérlek.
Ezután a képletek:
F2 cella=RANG.ÁTL($C2;INDEX(ELTOLÁS($C$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1);0)
G2 cella=RANG.ÁTL($E2;INDEX(ELTOLÁS($E$1;HOL.VAN($D2;értékelő2;0)-1;0;DARABHA(értékelő2;$D2);1);0;1);0)
A két képlet húzható lefelé, ameddig adatod van.
A DARABHA függvény korábbi Excel verziókban DARABTELI névre hallgatott, de úgy gondolom nálad már ez van.
Mielőtt a korreláció képletet mutatnám, a tartományok szétválasztásának módszeréről írok:
A magyarázat az F2 cella függvényeit veszi alapul:
Az INDEX függvény képes tartományt visszaadni a következőképpen: Ha a sor paraméter értékét 0 -ra állítjuk be, akkor a tartomány adott oszlopát, ha az oszlop paraméter értéke 0, akkor a teljes sort adja vissza. (Figyelem, nem elhagyjuk a sort/oszlopot, hanem 0-t írunk be paraméternek.)
A számunkra szükséges tartományt pedig az ELTOLÁS (lánykori nevén OFFSET) függvény és a HOL.VAN (MATCH) függvény használatával kaphatjuk meg:
Az ELTOLÁS függvény paraméterei:
1. A cella címe, ahonnan a tartományt elmozdítjuk. (A fix pont, ahonnan Arkhimédész kimozdítaná a Földet... )
Ez nálunk a C1 cella, mivel itt kezdődnek az értékelő1 által adott eredmények
2. A sorok száma, amennyivel lejjebb-feljebb tesszük a kezdősor értékét.
Ez lesz annak a sornak a száma, ahol az értékelő1 először fordul elő a B oszlopban (az értékelő1 elnevezésű tartományban).HOL.VAN($B2;értékelő1;0)-1Azért kell a végére -1, mert a függvény a paraméter úgy használja, hogy a kiinduló sorhoz hozzáadja a sor eltolás mértékét. MIvel az első találat a 2. sorban lesz és az elsőből indulunk, ezért vonunk le 1-et.
3. Az oszlopok száma: esetünkben 0, mert a kiinduló oszlopban maradunk
4. Magasság: Az új tartomány sorainak száma:DARABHA(értékelő1;$B2)ahányszor előfordul az első értékelő neve a tartományban.
5. Szélesség: Az új tartomány oszlopainak száma: esetünkben 1, mivel maradunk az eddig oszlopban.
Így az új tartományunk a C2 cellában kezdődik és a C7 cellában végződik. Ezt az oszlop tartományt adja vissza nekünk az INDEX függvény, mivel a sor paraméternek 0-t adtunk meg.
Most pedig a korreláció függvény: Egyszerűbb képlet:
H7 cellába=KORREL($F$2:$F$7;$G$2:$G$7)
H11 cellába=KORREL($F$8:$F$11;$G$8:$G$11)
Ezeket gondolom nem okozna nagy problémát beírni akár egyenként.
De lehet automatizálni:
I2 cella képlete:=KORREL(INDEX(ELTOLÁS($F$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1);INDEX(ELTOLÁS($G$1;HOL.VAN($B2;értékelő1;0)-1;0;DARABHA(értékelő1;$B2);1);0;1))
Ez húzható lefelé.
Ezután az ízlésednek megfelelő cellában hagyod meg a képletet.
FIGYELEM!
A tartományok ilyetén szétválasztása csak akkor működik, ha az értékelők szerint sorba van rendezve a táblázatod.
Ne ijedj meg, megcsinálni sokkal könnyebb és rövidebb ideig tart, mint ezt az egészet elolvasni.
Üdv. -
ben11
őstag
Wow, azt hiszem most elmegyek tanulmányi szabadságra egy hétre, hogy ezt megértsem
Köszi szépen! Kipróbálom, hogy működik-e jól az aktuális táblázaton.
Abba esetleg be tudsz avatni, hogy mi alapján működik a tartományok elkülönítése?
Illetve a tartományra a sorrendhez is szükség van, mert tartományonként kell ellenőrizni. -
Mutt
senior tag
Szia,
Milyen Excel verziót használsz?
Egy 2010-től működő megoldás két segédoszloppal, hogy rövidebbek legyenek a képletek.
Az F2-ben az alábbi képlet van:
=DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)(Ez egyenlőség esetén nem pont azt a számot mutatja, mint a mintádban van 1,5 v. 2,5. Ha a te számod kell akkor ez a képlet:
=DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+HA(DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)>1;1+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2);1)A G2-ben a képlet majdnem ugyanaz csak a ">" és "=" jelek után a C2-t E2-re kell cserélned.
A korrellációhoz tudnunk kell hogy melyik sortól kezdődig és melyik sorig tart az összefüggő tartomány, erre van a két segédoszlop (min/max).
I2-ben a képlet:
=ÖSSZESÍT(15;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)
J2-ben majdnem ugyanaz, csak a NAGY függvényt kell használni.
=ÖSSZESÍT(14;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)Ezek után H2-ben a képlet:
=HA(SOR()=$J2;KORREL(INDEX($F$1:$F$10000;$I2):INDEX($F$1:$F$10000;$J2);INDEX($G$1:$G$10000;$I2):INDEX($G$1:$G$10000;$J2));"")üdv
-
-
ben11
őstag
Sziasztok!
Segítséget szeretnék kérni egy feladat egyszerűsítésére.
Adott egy ehhez hasonló táblázat:
Értékelő páronként van x számú vizsgázó, akiknek két eredményét kéne vizsgálni. Változó, hogy az adott párhoz hány db vizsgázó tartozik. Jelenleg szakaszonként manuálisan van sorrend számoltatva, majd a korreláció, de baromi sok adat van. Valahogy lehetne gyorsítani ezen?
Előre is köszönöm!
-
Fferi50
Topikgazda
válasz
lacipapi
#51347
üzenetére
Szia!
Próbáltad már, hogy időformátumban beírtad a kezdő és a vége időt, majd a kettő különbsége =vége-kezdő (az Excel ugyanis számként kezeli a dátum/idő értékeket.)
Pl. =B1-A1 a C1 cellába, ha mindkét cellában idő formátum van, a C1 cellában is idő formátumban jelenik meg.
A második kérdésedre: egy cellában vagy képlet van vagy egyedi érték. Ha egy olyan cellába írsz számot, amelyben képlet van, akkor a képlet megsemmisül. Ehhez a feladathoz 3 másik cellát kell használnod, a megfelelő képlettel.
Üdv. -
lacipapi
csendes tag
Sziasztok!
Azt hogy lehet megcsinálni, hogy egy cellába beírsz egy időpontot kezdetként, majd egy másikba az időpont végét, és akkor egy harmadik cellába kiszámolja a különbséget,
Pl: A- oszlop az idő kezdete
B- oszlop az idő vége
C- oszlop az eltelt idő
Az is jó lenne ha úgyis müködne hogy ha a C értéket irom be akkor számolja ki az A ismeretében a B értéket.
Előre is köszönöm a segítséget -
rappit4
őstag
Sziasztok!
Viszonylag egyszerű problémával fordulok hozzátok. Egy időmérő futamra szeretnék rangsorolást excelben. Van 3 oszlopom, 3 mért idő, 100 nevezővel. A RANK funkció nagyon szépen működik de csak 1db oszlopra. Olyat sehogy sem tudtam, hogy a 3 időből a legjobbat rendszerezze nevezőként, ezért egy új oszlopba a SMALL funkcióval kiírattam a leggyorsabb időt egy nevezőnél a háromból és erre az oszlopra írtam egy RANK-ot. Így működik is, amig mind a 100 nevezőnél van idő. Ha már egyvalakinél nincs idő akkor nem tud számolni, azt írja "HibaA(z) SMALL függvénynek nincs érvényes bemeneti adata."Köszi ha tudtok segíteni.
-
Mutt
senior tag
Tegnap felrobbant az összes külföldi Excel-es MVP blogja/Youtube csatornája, ma pedig már a magyar tech fórumokon is jött a hír, hogy már Excelben is lehet Python-t használni.
Azonban még nem kell temetni se a VBA-t, se az OfficeScript-et, mert:
- még csak azok próbálhatják ki akik a BETA csatornára vannak feliratkozva,
- jelenlegi formájában adatok feldolgozásában és vizualizációban tud segíteni,
- kicsit körülményes a használata még (talán a régi Excel4-es makrókhoz hasonlít).Lássuk mi fog kisülni belőle. Amint lehet én is megnézem.
-
#51340
föccer
nagyúr
Darko_addict
#51339
föccer
nagyúr
válasz
Darko_addict
#51339
üzenetére
Mintha a cimke tartomány sérült vagy törlésre került volna. Súgóba írd be, hogy névkezelő, és kiadja, hogy hol van a cimke tartomány, azt ellenőrízd.
üdv, föccer
-
#51339
Darko_addict
őstag
Darko_addict
őstag
Sziasztok!
Segítséget szeretnék kérni egy hiba megértéséhez.
A táblázatot örököltem, egy nálam ügyesebb csaj rakta össze évekkel ezelőtt külön megbízásra.
A táblázat csomagolóanyag árkalkulációhoz van kitalálva és különböző munkafüzetekből szedi össze az infót.
A "Csomagolóanyagot" ugyanolyan módszerrel keresi, mint a "Címkét", az utóbbi képlete mégsem működik. A két munkafüzet 100%-ban azonos: Cikkszám, Megnevezés, Szállítási egység, Ár oszlopokból épül fel ugyanolyan sorrendben.Csatolok három képet.
Csomagolóanyag képlete: [kép]
Címke képlete: [kép]
Hiba: [kép]Milyen lépéseket tegyek a hiba elhárításához?
Köszönöm szépen!
-
#51338
Fire/SOUL/CD
félisten
wwhy
#51335
1. A1-be beírod a kezdő szöveget
2. A2-be ezt a képletet és lemásolod=BAL(A1;7)&SZÖVEG(2+SOR(A1)-SOR($A$1);"00")&JOBB(A1;12)
Értelem szerűen, ha nem az A1-ben van a kezdő szöveg, akkor módosítod a cellaképletet (fenti képen B10-től kezdtem)
Azt nem tudom milyen típusú vonalkód kell (sajnos a MS Barcode Controll Add.in-t már kiszedték az Excelből), de ez talán a legegyszerűbb megoldás. Ez egy sima betűkészlet, amit letöltesz, kicsomagolsz, TTF fájlon jobb egér, telepíteted, Excel restart és ott lesz a betűkészlet(font) lenyílóban.
-
wwhy
veterán
Megköszönve a korábbi segítséget, egy újabb "feladvánnyal" jövök, ezúttal vonalkód a téma.
1) Adott a következő formátum: 34533-01-2023.10.25, amiből első körben "sorozatot" kellene generálni úgy, hogy a középső szám változik csak:
pl. 345333-01-2023.10.25 .... 34533-40-2023.10.25
Ezt jó lenne úgy, hogy a cella sarkát húzva töltse ki az oszlopot az adott sorozattal.2) majd ezeknek az adatsoroknak kellene a B oszlopban vonalkódot generálni úgy, hogy a vonalkód felett legyen az adatsor dátuma, alatta pedig a sorszám rész (34533-01), vagy, ha egyszerűbb, akkor a teljes adatsor lehet a vonalkód alatt (345333-01-2023.10.25).
-
Fferi50
Topikgazda
Szia!
" ha valahol azt látod, hogy 17-31-ig zárva van valami, akkor nem hagyod ki a 17-et, hanem azt is beleszámolod a zárva tartott napok közé."
Ebben teljesen igazad van, csakhogy a függvény súgója szerint
"az Excel a vég_dátum – kezdő_dátum művelettel számítja ki a két dátum közötti napok számát."
Ez van bele "égetve", ezt kell figyelembe venni a képlettel való számolásainknál. Tehát, ahogy az előttem szóló is írta, ha a kezdő napot is bele szeretnéd számolni, hozzá kell adni az eredményhez 1-et.
Üdv. -
_NCT
addikt
válasz
Fferi50
#51324
üzenetére
Szerintem ez nem annyira egzakt és egyértelmű, hogy nem veszi bele a mai dátumot, legalábbis tól-ig lehetőségnél. Ha tól-ig beállítok értéket, akkor azért teszem, hogy azok is benne legyenek. Értem a matematikáját, csak ez eléggé félrevezető, legalább egy hint lehetne erről a súgóban. Szerintem ha valahol azt látod, hogy 17-31-ig zárva van valami, akkor nem hagyod ki a 17-et, hanem azt is beleszámolod a zárva tartott napok közé.
-
Fferi50
Topikgazda
Szia!
"ő azt mondja, hogy pl 08.17 és 08.31 között 14 nap van, ami a valóságban 15."
Szerinted hogyan lehetne a 31-17 kivonás értéke 15?
Az Excel pont ugyanúgy számol a dátumokkal, mint a számokkal - mivel a dátumokat egyébként számokként ábrázolja.
Amúgy hány nap is van hátra 17 -től 31-ig?
Üdv. -
_NCT
addikt
Sziasztok!
Segítsetek légyszi megérteni az excel lelki világát. Szóval szeretnék egy olyat, hogy adott dátum között mondja meg, hány nap van. Ez a DÁTUMTÓLIG vagy NAPOK függvénnyel akár működhetne is. Viszont az a bajom, hogy ő azt mondja, hogy pl 08.17 és 08.31 között 14 nap van, ami a valóságban 15. A kezdő dátumot nem számolná bele? Ugyanez ha MA()+4 napra 20-át ír, a mai napot nem számolja bele. Kell valamit trükközni az órákkal?
-
p5quser
tag
válasz
Fferi50
#51321
üzenetére
Az a baj az FmMatchEntryComplete-tel, hogy nem látom a listát.
Mellékelek egy videot, hogy kb. hogyan néz ki a felvitel működés közben.
Ez most egy random lista, de jól szemlélteti hogy rutinból ütve, tudja a felhasználó hogy a lista alján lesz amit keres (de nem OCD-s hogy megjegyezze a cikkszámát ). -
Fferi50
Topikgazda
válasz
p5quser
#51320
üzenetére
Szia!
Szerintem egyszerűbb módon is meg lehetne oldani a lista szűrését. Most nincs túl sok időm, csináltam egy nagyon egyszerű példát, ahol a betűk beírásával folyamatosan változik a kiválasztott érték és megjelenik a textboxban.
[link]
Csak indítsd el a Userform1-et.
Üdv.
Ps: A Data.hu egy darabig ellenőrizgeti a vírusügyet, csak később lesz elérhető a letöltés. -
p5quser
tag
válasz
Fferi50
#51316
üzenetére
Először is, köszönöm a segítséget Neked és Muttnak!
Nyilván kicsit több infoval könnyebb kontextusba helyezni ezt "fel-le nyilasdit".
Szóval így néz ki a form;
Ez pedig a combobox3 (megnevezés) kódja;Private Sub ComboBox3_Enter()ComboBox3 = scstr 'az előző keresési stringEnd SubPrivate Sub ComboBox3_Change()If ComboBox3.Enabled = True Thendic.RemoveAll 'Törli a dropdown list elemeket.If Not Comb_Arrow And Len(ComboBox3) > 1 Then 'elkezd keresni a második leütött karaktertőlFor i = 1 To lrdIf InStr(LCase(ciktomb(i, 2)), LCase(ComboBox3)) > 0 And ComboBox3 <> vbNullString _And Not dic.Exists(ciktomb(i, 2)) Thendic.Add ciktomb(i, 2), NothingComboBox3.List = dic.keys 'a találatokat egy dictionary-be gyűjtiEnd IfNext iWith ComboBox3scstr = .Text 'lementi az előző keresési stringet, mert sok hasonló nevű van, így nem kell újra beírniIf dic.Count = 0 Then .List = dic.keysIf dic.Count = 1 Then 'ha csak egy találat van beugrik a textbox1-be..ListIndex = 0With TextBox1.SetFocus.SelStart = 0.SelLength = Len(.Text)End WithExit SubEnd IfIf .Enabled = True And dic.Count > 1 Then .DropDownEnd WithEnd IfEnd IfEnd SubPrivate Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)Comb_Arrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown) 'a fel-le nyíllal belelép a listába, de mindig a tetejére.If KeyCode = vbKeyReturn Then 'enterrel (vagy clickkel) kiválasztja a tételt és a textboxba lépComboBox3.List = dic.keysWith TextBox1.SetFocus.SelStart = 0.SelLength = Len(.Text)End WithEnd IfEnd Sub
Ezek alapján lehetséges egyáltalán hogy alulról lépjen bele a legördülő menübe a felfelé nyíllal?
Még egyszer köszönöm az ötletelést! -
Fferi50
Topikgazda
Szia!
Szerintem egyáltalán nem kell a billentyűvizsgálat hozzá, a ComoBox maga is kezeli ezt. Annyi kell csak, hogy az Inicializálásnál vagy a fókuszba kerülésnél be kell állítani a ListIndexet a ListCount-1 értékre.
Én biztosan nem foglalkoznék itt a KeyDown, KeyUp eseményekkel.
Pl:Private Sub UserForm_Initialize()With Me.ComboBox1.AddItem "Géza".AddItem "Paula".AddItem "Kriszta".AddItem "Aladár".AddItem "Blöki".AddItem "Maffia".AddItem "Mz/X".AddItem "Máris szomszéd".ListIndex = .ListCount - 1End WithEnd Sub
Ha a Textbox1ből kilépünk és azt szeretnénk, hogy az utolsóra ugorjon:Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)With Me.ComboBox1.ListIndex = .ListCount - 1End WithEnd Sub
Bármelyik listaelemet be lehet ezzel a módszerrel állítani szerintem.
Üdv. -
Mutt
senior tag
válasz
p5quser
#51315
üzenetére
Szia,
Ezt hoztam össze. A KeyDown-al nem, de a KeyUp-al megy amit akarsz.
Annyit kacifántoztam, hogy csak az első UP esetén ugrik a legutolsó elemre, hogy lehessen felfelé pörgetni a listát. Ehhez kell egy globális változó, bKeyUp nálam.Dim bKeyUp As Boolean 'igaz ha utoljára felfelé nyíl volt használva
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With ComboBox1
If KeyCode = vbKeyUp Then
'ha már nyomtak felfelé nyilat akkor kiléphetünk innen
If bKeyUp Then Exit Sub
'ha nem nyomtak akkor ugrunk a végére és beállítjuk hogy volt már felfelé nyíl használva
.ListIndex = .ListCount - 1
bKeyUp = True
Else
'ha más billentyüt nyomtak akkor elfelejtük hogy volt már felfelé nyíl nyomva
bKeyUp = False
End If
End With
End Sub
Private Sub UserForm_Initialize()
bKeyUp = False
With ComboBox1
.AddItem "Géza"
.AddItem "Paula"
.AddItem "Kriszta"
.AddItem "Aladár"
.AddItem "Blöki"
.AddItem "Maffia"
.AddItem "Mz/X"
.AddItem "Máris szomszéd"
.ListIndex = 0
End With
End Subüdv
-
Fferi50
Topikgazda
-
p5quser
tag
válasz
Fferi50
#51304
üzenetére
Valami hasonlóval próbálkoztam még régebben;
If KeyCode = vbKeyUp ThenIf ComboBox3.ListCount > 0 ThenComboBox3.ListIndex = ComboBox3.ListCount - 1End If......
De ez volt az, amelyiknél felugrott alulról a második elemre a listában és onnan sehová nem mozdul a nyilakkal.
Ez egyébként egy "kereső"-combobox, a bevitt karakterekre szűrve változik a legördülő lista. -
wwhy
veterán
Ilyen, amikor egy bölcsész (én) ekszcelül fogalmaz
Semmit nem kell kiszámolni, egyszerűen át kell vinni a sofőr által beírt legnagyobb értéket.
Azaz ha ABCD123 egy nap megtett 3 utat, 10, 20 és 30 km-el, akkor azokat beírja az útjai végén, és csak a 30 km kell szerepeljen az én listámban.
És ugyanígy, XYZ123, megtesz napi 4 utat - 5, 10 ,15, 20 km - , akkor ahhoz a rendszámhoz nekem a 20 km-t kell beírni.Tehát a napi elszámolás listában meg kell keresni az adott rendszámhoz tartozó legnagyobb értéket, és azt változatlanul beíratni az autólistába
Fferi50: nagyon köszönöm!
Köszönöm a türelmeteket
-
-
-
p5quser
tag
Sziasztok!
Azt hogyan lehetne elérni egy comboboxban, hogy a felfelé nyíllal a legördőlő lista aljára lépjen? Most csak belelép a listába a nyilakkal.
Ez lenne a combobox kódja;Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)Comb_Arrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)If KeyCode = vbKeyReturn ThenComboBox3.List = dic.keysWith TextBox1.SetFocus.SelStart = 0.SelLength = Len(.Text)End WithEnd IfEnd Sub
Egyébként hibátlanul működik, csak gyorsítana a felvitel folyamatán.
F2 cella képlete:
A következő hibát találtam még. Ha eléri a 2.félidős adatkitöltés az 1.félidőt, onnantól "#hiányzik" jelenik meg a gólszám mezőben. Az össz eredményhez hozzászámolja, de vizuálisan nem jelenik meg.
Üdv!
A két név mindig az adott oszlop aktuálisan kitöltött részére hivatkozik. Ha 3000-nél több sorodban lenne adat, akkor légy szíves módosítani a 3000-t a végén. FIgyelj a $ jelekre kérlek.
Mielőtt a korreláció képletet mutatnám, a tartományok szétválasztásának módszeréről írok:
Ezután az ízlésednek megfelelő cellában hagyod meg a képletet.
Új hozzászólás Aktív témák
Hirdetés
- Mibe tegyem a megtakarításaimat?
- sziku69: Fűzzük össze a szavakat :)
- Audi, Cupra, Seat, Skoda, Volkswagen topik
- Hobby rádiós topik
- AMD Ryzen 9 / 7 / 5 / 3 5***(X) "Zen 3" (AM4)
- Apple MacBook
- A fociról könnyedén, egy baráti társaságban
- Elektromos autók - motorok
- Telekom otthoni szolgáltatások (TV, internet, telefon)
- Autós topik
- További aktív témák...
- GYÖNYÖRŰ iPhone 13 Pro 256GB Graphite -1 ÉV GARANCIA - Kártyafüggetlen, MS3074, 100% Akkumulátor
- DeepCool CC560 Black V2 garis
- Surface Laptop 4 i7-1185G7 16GB 256GB
- GYÖNYÖRŰ iPhone 13 mini 256GB Midnight -1 ÉV GARANCIA - Kártyafüggetlen, MS3402
- HIBÁTLAN iPhone XR 128GB Black -1 ÉV GARANCIA - Kártyafüggetlen, MS3267, 96% Akkumulátor
Állásajánlatok
Cég: CAMERA-PRO Hungary Kft.
Város: Budapest
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest