Excel-elämän hakkerit raportointiin ja tietojenkäsittelyyn osallistuville

2024 Kirjoittaja: Malcolm Clapton | [email protected]. Viimeksi muokattu: 2024-01-13 01:27

Tässä viestissä Mann, Ivanov and Ferber Publishing Housen apulaisjohtaja Renat Shagabutdinov jakaa hienoja Excel-elämän hakkereita. Näistä vinkeistä on hyötyä kaikille, jotka ovat mukana raportoinnissa, tietojenkäsittelyssä ja esitysten luomisessa.

Tämä artikkeli sisältää yksinkertaisia tekniikoita Excel-työn yksinkertaistamiseksi. Ne ovat erityisen hyödyllisiä niille, jotka ovat mukana johdon raportoinnissa, valmistelevat erilaisia analyyttisiä raportteja 1C:n ja muiden raporttien latausten perusteella, muodostavat niistä esityksiä ja kaavioita johtamista varten. En väitä olevani absoluuttinen uutuus - muodossa tai toisessa näistä tekniikoista on luultavasti keskusteltu foorumeilla tai mainittu artikkeleissa.

Yksinkertaisia vaihtoehtoja VLOOKUP:lle ja HLOOKUP:lle, jos halutut arvot eivät ole taulukon ensimmäisessä sarakkeessa: HAKU, INDEKSI + HAKU

VLOOKUP- ja HLOOKUP-funktiot toimivat vain, jos halutut arvot ovat ensimmäisessä sarakkeessa tai rivissä taulukossa, josta aiot saada tietoja.

Muuten on kaksi vaihtoehtoa:

1. Käytä HAKU-toimintoa.

   Sillä on seuraava syntaksi: HAKU (hakuarvo; hakuvektori; tulosvektori). Mutta jotta se toimisi oikein, view_vector-alueen arvot on lajiteltava nousevaan järjestykseen:

   

2. Käytä MATCH- ja INDEX-toimintojen yhdistelmää.

   MATCH-funktio palauttaa taulukossa olevan elementin järjestysluvun (sen avulla saat selville millä taulukon rivillä etsitty elementti on) ja INDEX-funktio palauttaa taulukon elementin annetulla numerolla (jonka saamme selville käyttämällä MATCH-toimintoa).

   

   Funktion syntaksi:

   • SEARCH (search_value; search_array; match_type) - meidän tapauksessamme tarvitsemme hakutyypin "tarkka haku", se vastaa numeroa 0.

   • INDEKSI (taulukko; rivin_numero; [sarakkeen_numero]). Tässä tapauksessa sinun ei tarvitse määrittää sarakkeen numeroa, koska taulukko koostuu yhdestä rivistä.

Kuinka nopeasti täyttää tyhjät solut luettelossa

Tehtävänä on täyttää sarakkeen solut yläosassa olevilla arvoilla (niin, että aihe on taulukon jokaisella rivillä, eikä vain aiheen kirjalohkon ensimmäisellä rivillä):

Valitse "Aihe" -sarake, napsauta "Etusivu"-ryhmän nauhaa, "Etsi ja valitse" -painiketta → "Valitse soluryhmä" → "Tyhjät solut" ja aloita kaavan syöttäminen (eli aseta yhtä suuri merkki) ja viittaa yläreunassa olevaan soluun napsauttamalla näppäimistön ylänuolta. Paina sen jälkeen Ctrl + Enter. Tämän jälkeen voit tallentaa vastaanotetut tiedot arvoina, koska kaavoja ei enää tarvita:

Kuinka löytää virheitä kaavasta

Kaavan erillisen osan laskenta

Ymmärtääksesi monimutkaisen kaavan (jossa muita funktioita käytetään funktion argumentteina, eli jotkut funktiot ovat sisäkkäisiä toisissa) tai löytääksesi siitä virheiden lähteen, sinun on usein laskettava osa siitä. On kaksi helppoa tapaa:

1. Laskeaksesi osan kaavasta suoraan kaavapalkista, valitse kyseinen osa ja paina F9:

   

   Tässä esimerkissä SEARCH-funktiossa oli ongelma - argumentit vaihdettiin siinä. On tärkeää muistaa, että jos et peruuta funktion osan laskentaa ja paina Enter, laskettu osa jää numeroksi.

2. Napsauta Laske kaava -painiketta nauhan Kaavat-ryhmässä:

   

   Näyttöön tulevassa ikkunassa voit laskea kaavan vaihe vaiheelta ja määrittää, missä vaiheessa ja missä toiminnossa virhe tapahtuu (jos sellainen on):

   

Kuinka määrittää, mistä kaava riippuu tai mihin se viittaa

Määrittääksesi, mistä soluista kaava riippuu, napsauta nauhan Kaavat-ryhmässä Vaikuttavat soluihin -painiketta:

Näkyviin tulee nuolet osoittamaan, mistä laskennan tulos riippuu.

Jos kuvassa punaisella korostettu symboli näkyy, kaava riippuu muiden arkkien tai muiden kirjojen soluista:

Napsauttamalla sitä voimme nähdä tarkalleen missä vaikuttavat solut tai alueet sijaitsevat:

"Vaikuttavat soluihin" -painikkeen vieressä on "Riippuvaiset solut" -painike, joka toimii samalla tavalla: se näyttää nuolet aktiivisesta solusta kaavan kanssa soluihin, jotka ovat riippuvaisia siitä.

Painikkeella "Poista nuolet", joka sijaitsee samassa lohkossa, voit poistaa nuolia vaikuttaviin soluihin, nuolia riippuvaisiin soluihin tai molempia nuolia kerralla:

Kuinka löytää soluarvojen summa (lukumäärä, keskiarvo) useilta arkeilta

Oletetaan, että sinulla on useita samantyyppisiä taulukoita, joissa on tietoja, joita haluat lisätä, laskea tai käsitellä jollain muulla tavalla:

Voit tehdä tämän syöttämällä soluun, jossa haluat nähdä tuloksen, vakiokaavan, esimerkiksi SUMMA (SUMMA) ja määrittämällä ensimmäisen ja viimeisen arkin nimet niiden taulukkojen luettelosta, jotka sinun on käsiteltävä argumentti kaksoispisteellä erotettuna:

Saat solujen summan osoitteella B3 arkeilta "Data1", "Data2", "Data3":

Tämä osoitus toimii sijoitetuille levyille johdonmukaisesti … Syntaksi on seuraava: = FUNCTION (first_list: last_list! Alueviite).

Kuinka luoda automaattisesti mallilausekkeita

Käyttämällä Excelin tekstin kanssa työskentelyn perusperiaatteita ja muutamaa yksinkertaista toimintoa voit valmistaa mallilauseita raportteja varten. Useita tekstin kanssa työskentelyn periaatteita:

• Yhdistämme tekstin käyttämällä &-merkkiä (voit korvata sen CONCATENATE-funktiolla, mutta siinä ei ole paljon järkeä).
• Teksti kirjoitetaan aina lainausmerkeissä, viittaukset tekstiä sisältäviin soluihin ovat aina ilman.
• Saadaksesi palvelumerkin "lainausmerkit", käytä CHAR-funktiota argumentilla 32.

Esimerkki mallilauseen luomisesta kaavoilla:

Tulos:

Tässä tapauksessa CHAR-toiminnon (lainausmerkkien näyttämiseksi) lisäksi käytetään IF-toimintoa, jonka avulla voit muuttaa tekstiä sen mukaan, onko positiivista myyntitrendiä, ja TEKSTI-toimintoa, jonka avulla voit näyttää numero missä tahansa muodossa. Sen syntaksi on kuvattu alla:

TEKSTI (arvo; muoto)

Muoto on määritetty lainausmerkeissä aivan kuin syöttäisit mukautetun muodon Muotoile solut -ikkunaan.

Monimutkaisempia tekstejä voidaan myös automatisoida. Käytännössäni automatisoitiin johdon raportointiin pitkiä, mutta rutiineja kommentteja muodossa INDIKAATTORI laski / nousi XX suunnitelmaan verrattuna, mikä johtui pääasiassa TEKIJÄ1:n kasvusta / laskusta XX:llä, TEKIJÄ2:n kasvusta / laskusta YY …” muuttuvassa tekijäluettelossa. Jos kirjoitat tällaisia kommentteja usein ja niiden kirjoitusprosessi voidaan algoritmisoida, kannattaa kerran hämmentävänä luoda kaava tai makro, joka säästää ainakin osan työstä.

Kuinka tallentaa tietoja kuhunkin soluun ketjutuksen jälkeen

Kun yhdistät solut, vain yksi arvo säilyy. Excel varoittaa tästä yrittäessään yhdistää soluja:

Vastaavasti, jos sinulla oli jokaisesta solusta riippuva kaava, se lakkaa toimimasta niiden yhdistämisen jälkeen (# N / A virhe esimerkin riveillä 3-4):

Yhdistääksesi solut ja säilyttääksesi tiedot jokaisessa niistä (ehkä sinulla on kaava, kuten tässä abstraktissa esimerkissä; ehkä haluat yhdistää solut, mutta säilyttää kaikki tiedot tulevaisuutta varten tai piilottaa ne tarkoituksella), yhdistä kaikki taulukon solut, valitse ne ja käytä sitten Format Painter -komentoa muotoilun siirtämiseen yhdistettäviin soluihin:

Pivotin rakentaminen useista tietolähteistä

Jos sinun on rakennettava pivot useista tietolähteistä kerralla, sinun on lisättävä "PivotTable and Chart Wizard" -valintanauhaan tai pikakäyttöpaneeliin, jossa on tällainen vaihtoehto.

Voit tehdä tämän seuraavasti: "Tiedosto" → "Asetukset" → "Pikatyökalupalkki" → "Kaikki komennot" → "Ohjattu pivot-taulukko- ja kaaviotoiminto" → "Lisää":

Sen jälkeen nauhaan ilmestyy vastaava kuvake, jota napsauttamalla kutsuu sama ohjattu toiminto:

Kun napsautat sitä, näkyviin tulee valintaikkuna:

Siinä sinun on valittava kohde "Useissa konsolidointialueissa" ja napsauta "Seuraava". Seuraavassa vaiheessa voit valita "Luo yksi sivukenttä" tai "Luo sivukenttiä". Jos haluat itsenäisesti keksiä nimen kullekin tietolähteelle, valitse toinen kohde:

Lisää seuraavassa ikkunassa kaikki alueet, joiden perusteella pivot rakennetaan, ja anna niille nimet:

Määritä sen jälkeen viimeisessä valintaikkunassa, mihin pivot-taulukkoraportti sijoitetaan - olemassa olevalle tai uudelle arkille:

Pivot-taulukkoraportti on valmis. "Sivu 1" -suodattimessa voit valita vain yhden tietolähteistä tarvittaessa:

Kuinka laskea tekstin A esiintymismäärä tekstissä B ("MTS SuperMTS -tariffi" - kaksi lyhennettä MTS)

Tässä esimerkissä sarake A sisältää useita tekstirivejä, ja tehtävämme on selvittää, kuinka monta kertaa jokainen niistä sisältää solussa E1 olevan hakutekstin:

Tämän ongelman ratkaisemiseksi voit käyttää monimutkaista kaavaa, joka koostuu seuraavista funktioista:

1. DLSTR (LEN) - laskee tekstin pituuden, ainoa argumentti on teksti. Esimerkki: DLSTR ("kone") = 6.
2. SUBSTITUTE - korvaa tietyn tekstin tekstijonossa toisella. Syntaksi: SUBSTITUTE (teksti; vanha_teksti; uusi_teksti). Esimerkki: SUBSTITUTE ("auto"; "auto"; "") = "matkapuhelin".
3. UPPER - korvaa kaikki merkkijonon merkit isoilla kirjaimilla. Ainoa argumentti on teksti. Esimerkki: UPPER ("kone") = "CAR". Tarvitsemme tätä toimintoa tehdäksemme hakuja, joissa kirjainkoko ei erotella. Loppujen lopuksi UPPER ("auto") = UPPER ("kone")

Jos haluat löytää tietyn tekstijonon esiintymisen toisesta, sinun on poistettava kaikki sen esiintymät alkuperäisestä ja verrattava tuloksena olevan merkkijonon pituutta alkuperäiseen:

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super" = 6

Ja sitten tämä erotus etsimämme merkkijonon pituudella:

6 / DLSTR ("MTS") = 2

Se on tasan kaksi kertaa, kun rivi "MTS" sisältyy alkuperäiseen.

On vielä kirjoitettava tämä algoritmi kaavojen kielellä (merkitkäämme "tekstillä" tekstiä, josta etsimme esiintymiä, ja "etsimällä" - sitä, jonka esiintymien lukumäärästä olemme kiinnostuneita):

= (DLSTR (teksti) -LSTR (KORVAA (UPPER (teksti); UPPER (haku), ""))) / DLSTR (haku)

Esimerkissämme kaava näyttää tältä:

= (DLSTR (A2) -LSTR (KORVAA (UPPER (A2), UPPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)