Forum » Programska oprema » Excel 2007 - spremenljive vrste in stolpci
Excel 2007 - spremenljive vrste in stolpci
jedateruk ::
Najprej predlog: omogočite upload slike iz računalnika, da je ni treba dati na neko stran in od tam dati link slike sem. Tako sem žal zdaj naredil. Slika tabele je 
Glejte sliko in berite to: glede na vrednosti nekih celic izven te tabele se določa, v katerih vrstah se lahko uporablja celice za navpično seštevanje. Pogojno oblikovanje označi s sivo barvo vrste, ki se jih ne uporabi za seštevanje. V modri vrsti kaže vsoto vsakega stolpca, zelena barva pa kaže največjo vsoto, za delati vsote uporabljam =SUMPRODUCT(($AD$5:$AD$15>0)*X57:X67). Torej glede na vrednosti v območju AD5:AD15 se določa, v katerih vrstah se navpično sešteva celice. Če spremenim vrednosti nekih celic izven tabele, to vpliva na območje AD5:AD15 in bo spremenjeno, katere vrste bodo lahko uporabljene za seštevanje in bo največje vsota v drugem stolpcu.
Ker se spreminja vodoravna in navpična lokacija celic s številkami, iz katerih nastane največja vsota (v tej sliki so te številke 15, 16 in 17), teh spremenljivih številk ne znam locirati za druge formule ali vsaj, da bi mi te številke kazalo skupaj v nekih celicah izven te tabele.
tukaj.
Glejte sliko in berite to: glede na vrednosti nekih celic izven te tabele se določa, v katerih vrstah se lahko uporablja celice za navpično seštevanje. Pogojno oblikovanje označi s sivo barvo vrste, ki se jih ne uporabi za seštevanje. V modri vrsti kaže vsoto vsakega stolpca, zelena barva pa kaže največjo vsoto, za delati vsote uporabljam =SUMPRODUCT(($AD$5:$AD$15>0)*X57:X67). Torej glede na vrednosti v območju AD5:AD15 se določa, v katerih vrstah se navpično sešteva celice. Če spremenim vrednosti nekih celic izven tabele, to vpliva na območje AD5:AD15 in bo spremenjeno, katere vrste bodo lahko uporabljene za seštevanje in bo največje vsota v drugem stolpcu.
Ker se spreminja vodoravna in navpična lokacija celic s številkami, iz katerih nastane največja vsota (v tej sliki so te številke 15, 16 in 17), teh spremenljivih številk ne znam locirati za druge formule ali vsaj, da bi mi te številke kazalo skupaj v nekih celicah izven te tabele.
- spremenil: jedateruk ()
smetko ::
Poglej si funkcijo offset.
Pri kopiranju (oziroma razhroščevanju) pa si še poglej, kaj se zgodi če formulo zaključiš z CTRL SHIFT ENTER.
V tvojem primeru zadnja dva parametra (sta pri funkciji offset opcionalna) nastaviš na 10,1 in nato izbereš območje 10 vrstic v isti koloni in zaključiš vnos z CTRL SHIFT ENTER.
Pri kopiranju (oziroma razhroščevanju) pa si še poglej, kaj se zgodi če formulo zaključiš z CTRL SHIFT ENTER.
V tvojem primeru zadnja dva parametra (sta pri funkciji offset opcionalna) nastaviš na 10,1 in nato izbereš območje 10 vrstic v isti koloni in zaključiš vnos z CTRL SHIFT ENTER.
No comment
smetko ::
Za tvoj primer:
predpostavka: podatke imaš do kolone AD
v prejšnjem sestavku sem spregledal, da operiraš z 11 vrsticami.
Spodnja funkcija ti bo prepisala številke, kjer se prvič pojavi v vrstici 67 vrednost ki je enaka MAX(C67:AD67) in sicer na način da tam kjer je $AD$5:$AD$15>0 so 0.
{=(--($AD$5:$AD$15>0)*OFFSET(B55;1;MATCH(MAX(C67:AD67);C67:AD67;0);11;1))}
zaviti oklepaji povedo, da je bila pritisnjena kombinacija CTRL SHIFT ENTER (matrična funkcia, saj je rezultat matrika)
še malo razlage
MATCH(MAX(C67:AD67);C67:AD67;0) pove v katerem stolpcu se prvič pojavi maximalna vrednost v območju C67:AD67.
In za toliko stolpcev se funkcija offset premakne v desno od celice B55. drugi parameter je 1, in to pomeni da se premakne za eno vrstico navzdol. OFFSET(B56;0 ... to v tem primeru da enak rezultat.
--(izraz) to samo explicitno pove da se rezultati true/false pretvorijo v 1/0.
primer funkcije brez pritiskanja CTRL SHIFT ENTER
=sumproduct(--($AD$5:$AD$15>0);OFFSET(B55;1;MATCH(MAX(C67:AD67);C67:AD67;0);11;1))
tu sem sicer en * zamenjal z ; vendar ta zamenjava ni nujna, je pa dovoljena.
predpostavka: podatke imaš do kolone AD
v prejšnjem sestavku sem spregledal, da operiraš z 11 vrsticami.
Spodnja funkcija ti bo prepisala številke, kjer se prvič pojavi v vrstici 67 vrednost ki je enaka MAX(C67:AD67) in sicer na način da tam kjer je $AD$5:$AD$15>0 so 0.
{=(--($AD$5:$AD$15>0)*OFFSET(B55;1;MATCH(MAX(C67:AD67);C67:AD67;0);11;1))}
zaviti oklepaji povedo, da je bila pritisnjena kombinacija CTRL SHIFT ENTER (matrična funkcia, saj je rezultat matrika)
še malo razlage
MATCH(MAX(C67:AD67);C67:AD67;0) pove v katerem stolpcu se prvič pojavi maximalna vrednost v območju C67:AD67.
In za toliko stolpcev se funkcija offset premakne v desno od celice B55. drugi parameter je 1, in to pomeni da se premakne za eno vrstico navzdol. OFFSET(B56;0 ... to v tem primeru da enak rezultat.
--(izraz) to samo explicitno pove da se rezultati true/false pretvorijo v 1/0.
primer funkcije brez pritiskanja CTRL SHIFT ENTER
=sumproduct(--($AD$5:$AD$15>0);OFFSET(B55;1;MATCH(MAX(C67:AD67);C67:AD67;0);11;1))
tu sem sicer en * zamenjal z ; vendar ta zamenjava ni nujna, je pa dovoljena.
No comment
jedateruk ::
Prava formula je to: =IFERROR(INDEX($56:$66,SMALL(IF($AD$5:$AD$15>0,ROW($AD$5:$AD$15)-4),ROW(A1)),MATCH(1,$68:$68,FALSE)),"")
Tisti del, kjer je funkcija SMALL, da vse vrednosti skupaj, da ni razmaka enakega kot v tabeli.
Tisti del, kjer je funkcija SMALL, da vse vrednosti skupaj, da ni razmaka enakega kot v tabeli.
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | EXCEL vgnezdenje dveh vlookup funkcij?Oddelek: Programska oprema | 1878 (1577) | prtenjam |
| » | Excel Težava - fukncijaOddelek: Pomoč in nasveti | 2493 (2186) | smetko |
| » | Excel - dvojni vlookupOddelek: Programska oprema | 1604 (1376) | NeMeTko |
| » | Excel - vrednost celice glede na drugo celicoOddelek: Pomoč in nasveti | 1875 (1669) | smetko |
| » | Excel in seštevanje besedOddelek: Programiranje | 1651 (1518) | smetko |