Dinamikus válogatás táblázatok MS Excel - kompatibilis a Microsoft Excel 2018, Excel 2018
A táblázatban rendezze a képletek álló 2 oszlop. Rendezés fog egyike szerint a táblázat oszlopok (2 oldja meg a problémát: a válogatás és a rendezés numerikus szöveges oszlop a táblázatban). Rendezés képlet beállítva, hogy amikor új adatokat a forrás tábla rendezni a táblázat dinamikusan változik. Ez lehetővé teszi, hogy mindig is rendezni az asztalon felhasználói beavatkozás nélkül. Is, teszünk egy kétszintes válogatás, első numerikus, akkor (az ismétlődő számok) - a szöveg oszlopban.
Tegyük fel, van egy táblázat, amely a 2 oszlop. Egy oszlop - a szöveg: Lista gyümölcs; és a második - a numerikus értékesítési volumen (lásd a példa fájl.).
Zadacha1 (válogató numerikus táblázat oszlop)
Meg kell rendezni a táblázat sorait a tartalmát egy numerikus oszlop (az értékesítési volumen). Azt feltételezik, hogy a felhasználó folyamatosan kitölti a táblázat sorai, így meg kell, hogy írjon egy képlet figyelembe véve a hozzáadott értéket.
Az érthetőség kedvéért a nagysága oszlopban lévő értékeket értékesítési volumen izoláltunk feltételes formázás (Home / stílus / Feltételes formázás / hisztogram). Szintén sárgával ismétli.
Ha egy numerikus oszlop garantáltan nem tartalmaznak ismétlődő értékeket, akkor a probléma könnyen megoldható:
- Numerikus oszlop rendezési funkció nagy () (lásd a cikk rendezett lista (számérték).);
- CDF function () függvények vagy ínszalag INDEX () + MATCH () válassza az értékeket a szöveges oszlopában a megfelelő számértéket.
Ugyanakkor a valós alkalmazások numerikus oszlop tartalmazhat ismétlődés, valamint a funkció CDF () esetén ismétlés mindig kiválasztja csak az első érték a felső (lásd. Cikk WRT () függvény MS Excel), ez a megközelítés nem alkalmas (Fruit nevek származó helytelenül).
Ezért válogató mechanizmus majd végre kell hajtania egy másik.
Készítsen a kényelem a dinamikatartomány 2 Gyümölcsök és értékesítés. amely utal a tartományok a sejtek a következő értékeket tartalmazza a megfelelő oszlopokban a forrás táblázat. Ha új sort a táblázatban, a határokat a dinamikatartomány automatikusan bővül.
A D és E oszlop utáni táblázatot, amely dinamikus lesz rendezve,
Index = (Sales;
ROUND (MOD (nagy (
--(COUNTIF (Sales ";<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW () - SOR ($ E $ 6)) 1) * 1000, 0)
)
Ez a képlet fogja rendezni az oszlop Értékesítési mennyiség (dinamikus tartomány értékesítési) csökkenő sorrendben. Hiányosságok az eredeti tábla nem engedélyezettek. A sorok száma a forrás tábla kisebbnek kell lennie, mint 1000.
Nézzük a képlet még:
- COUNTIF képletű (Sales; "<"&Продажи) возвращает массив . Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
- Most, a fent említett tömb egész számok fog átalakulni egy sor számok egy tört része, ahol a tört része tartalmaz egy tételek száma a tömbben :. Ez valósul meg a kifejezést "" REPEAT ( "0", 3-LEN (ROW (Sales) Többsoros ($ E $ 6))) LINE (Sales) Többsoros ($ E $ 6)) Ebben a részben a képlet velejáró korlátozása legfeljebb 1000 sor a forrás táblázat (lásd. fent). Ha szükséges, akkor könnyen változtatni, de ez értelmetlen (lásd. Az alábbi szakaszban számítási sebesség).
- GREATEST () függvény úgy rendezi a tömb fölött.
- MOD () függvény a frakcionált része a szám, ami a pozíciók száma / 1000, például 0,005.
- Round () függvényt. szorzás után 1000, egészre kerekítve, és visszatér a cikkszám. Most az összes tétel száma megfelel az oszlopok száma az eladások, csökkenő sorrendben.
- INDEX () függvény a cikkszám a megfelelő számot.
Egy hasonló képletet lehet írni a kimeneti értékeket az oszlopban Gyümölcsök index = (gyümölcsök; round ().)
A példában fájlt. okok miatt a számítási sebesség (cm. alább), az azonos típusú képlet része, azaz mindent belül a round () függvényt. helyezni egy külön oszlopban J. Ezért, a kapott általános képletű a rendezett táblázatban a következők: index = (gyümölcsök; J7) és az index = (Sales; J7)
Ugyancsak, ha a képlet tömb nagy () függvény a kis () kap rendezve növekvő sorrendben.
Az érthetőség kedvéért a nagysága oszlopban lévő értékeket értékesítési volumen izoláltunk feltételes formázás (Home / stílus / Feltételes formázás / hisztogram). Mint látható, válogatás munkák.
Most egy új sort a forrás tábla. A dinamikusan sortable asztalok, meg kell, hogy a megfelelő válogatás.
1. A sejt A15, írja be a szót eredeti tábla sárgarépa;
2. A sejt B15 adja az értékesítési Sárgarépa = 25;
3. Miután belépett értékeket stolbtsahD és E automatikusan megjelenik csökkenő sorrendbe táblázat;
4. Táblázat rendezése egy új sor jelenik meg az utolsó előtti.
A sebesség számítása képletek
A „átlagos” számítógép teljesítményét méretezés tömb ilyen pár képletek sorokban elrendezett 100, szinte láthatatlan. Asztal 300 vonal, míg az átalakítás 2-3 másodperc, ami kényelmetlenséget. Vagy le kell tiltani az automatikus konvertálás a lemez (képletek / számításokat / számítási paramétereket), és rendszeres időközönként nyomja meg az F9. vagy megtagadja a használata tömbképletek kiváltása, a megfelelő oszlopok a képletek, illetve az általános, hogy megtagadja a dinamikus szortírozás a standard megközelítések (lásd. a következő szakaszban).
Alternatív megoldások a válogató asztalok
Rendezze a sorokat a forrás tábla a szokásos szűrő (forrás kiválasztásához táblázat fejlécét, majd nyomja meg a CTRL + SHIFT + L). A legördülő listából válassza ki a kívánt válogatás.
Megkapjuk a azonos változata az asztalra, de ha hozzáadjuk a táblázat új értékeket kell alkalmazni a szűrőt újra.
Ön is használja az eszközt Válogató (Data / Sort és Filter / rendezés). Ehhez válassza ki az összes értéket a forrás a táblázat nem tartalmazza a fejléc, hívja a Küld eszközzel jelölje ki az oszlopot, amely alapján rendezni, és rendezni az opciót.
Megkapjuk a azonos változata az asztalra, de azzal a kiegészítéssel, új értékeket is alkalmazniuk kell a szűrőt újra.
Melyik a lehetőséget érdemes választani - mint mindig -, hogy kiválassza a fejlesztő.
Egy másik általános képletű tömb (+ további oszlop). Zadacha1.1
Táblázat rendezése lehet tenni el más, egyszerűbb képlet tömb. de szükség van további (szolgáltatás) D oszlop (lásd például fájl lista 2. példa):
F oszlop tartalmaz egy rendezett oszlop a (eladások). A képlet visszaadja a pozícióját az eladási érték. Például a 86-az 5. sorban az asztalra.
Az expresszióhoz ismétli IF (F8 = $ B $ 7: $ B $ 14; LINE ($ B $ 7: $ B $ 14) Többsoros ($ B $ 6); 0) visszatér több érték: azaz A 74. számú található sorok 2. és 8..
Nagy () funkció jelenik első 2, majd 8 (külön sorokban).
Ez a képlet hangsúlyosabban jelenik, mint a fent tárgyalt az elején, de ehhez egy további oszlop.
Zadacha2 (válogató szöveges oszlop táblázat)
Rendezze a táblázat sorait a tartalmát a szöveges oszlop (a gyümölcs).
Ahogy az előző problémát, tegyük fel, hogy az az oszlop, amely válogatás végzik, vannak ismétlések (Fruit nevek ismétlődnek).
Rendezéséhez a táblázat 2 kell létrehozni overhead oszlopra (D és E).
Ez a képlet egy analóg rang értékek szövege (összesen értékek a többi felsorolt értékek). Szöveges értékek, lejjebb az ábécé felel meg a magasabb „rangot”. Például a legnagyobb érték Alma „rang” 7 (tekintve ismétlések).
E oszlopban adja meg a szokásos képlet:
Ez a képlet figyelembe veszi az ismétlődő szöveges értékek, és beállítja a „rangot”. Most Alma különböző értékek megfelelnek a különböző „fokozat” - a 7. és 8. Ez lehetővé teszi, hogy megjelenik egy lista rendezve értékeket. Ehhez használja a következő képletet (G oszlop):
Egy hasonló képletet visszavonja megfelelő térfogatú értékesítési (oszlop H).
Feladat 2.1 (duplex válogatás)
Most megint szeretnénk rendezni az eredeti táblázatot értékesítési volumen. De most az ismétlődő értékeket (az A oszlopban három érték 74), mi származik a megfelelő értékeket a betűrendben.
Ahhoz, hogy ezt elérjük, az eredmények Task 1.1 és 2. feladat.
Részletek a mintában fájl Zadacha2 lapon.