Excel-elämän hakkerointi osallistuville raportointiin ja tietojenkäsittely
Vinkkejä / / December 19, 2019
Renat ei ole ensimmäinen kerta, kun vieras kirjailija puhuu Layfhakere. Aiemmin olemme julkaissut erinomaisen materiaalia häneltä siitä, miten luodaan harjoitusohjelman: tärkein kirja ja verkkoaineistot sekä inkrementtialgoritmi Luo harjoitusohjelman.
Tässä artikkelissa on joitakin yksinkertaisia tekniikoita, jotka auttavat työn yksinkertaistamiseksi Excelissä. ne ovat erityisen hyödyllisiä niille, jotka osallistuvat johdon raportointi, valmistelee erilaisia analyyttisiä perustuvia raportteja puretuista 1C raportteja ja muita näitä esityksiä ja kaavioita johtajuutta. Älä teeskentele olla ehdoton uutuus - muodossa tai toisessa, näitä menetelmiä varmasti keskustellaan foorumeilla tai artiklassa tarkoitettua.
Yksinkertainen vaihtoehto VLOOKUP ja HLOOKUP, jos vaaditut arvot eivät kuitenkaan ensimmäisessä sarakkeessa: VIEW, INDEX + VASTINE
VLOOKUP (VLOOKUP) Ja PGR (VHAKU) vain, jos tavoitearvot ovat ensimmäisessä sarakkeen tai rivin taulukon, josta haluat saada tietoja.
Muissa tapauksissa on olemassa kaksi vaihtoehtoa:
- Käytä View -ominaisuus (HAKU).
Hän on seuraava syntaksi: tarkkailu (hakuarvo Haku vektori; vektor_rezultata). Mutta jotta se toimisi oikein, sinun täytyy aluearvoja haku vektori Heidät lajiteltava nousevasti: - MATCH käyttää yhdistelmää toimintoja (MATCH) ja indeksi (INDEX).
VASTINE palauttaa sarjanumeron alkion taulukossa (sen avulla voit löytää, peräkkäin taulukon haluttu elementti), ja funktio INDEX palauttaa matriisin elementti, jossa on ennalta määrätty määrä (jonka tiedämme toiminnon MATCH).Syntaksi vaihtoehtoa:
• VASTINE (hakuarvo massiv_poiska; match_type) - meidän tapauksessamme, me tarvitsemme jonkinlaista vertailua "tarkka haku", se vastaa numeron 0.
• INDEX (matriisi; ROW_NUM; [COLUMN_NUM]). Tässä tapauksessa ei ole tarpeen määrittää sarakkeen numero kuin joukko koostuu yhdestä linja.
Kuinka nopeasti täyttää tyhjät solut luettelossa
Tehtävänä - täyttää solujen sarakkeen arvot edellä (edellyttäen, että seisovan kunkin rivin taulukon, ei vain ensimmäisen lohkon rivi kirjoja aiheesta):
Valitse sarake "mukaan", paina nauhan ryhmässä "Home" -painiketta "Etsi ja valitse» → «Korosta ryhmä soluja» → «Tyhjä solu "ja ala kirjoittaa kaava (eli laittaa yhtä merkki) ja viittaa soluun, edellä, yksinkertaisesti painamalla nuoli ylös näppäimistö. Paina Ctrl + Enter. Tämän jälkeen tiedot voidaan tallentaa arvo, koska kaavat ei enää tarvita:
Miten löytää virheitä kaavassa
Laskelma erillinen osa kaava
Ymmärtää kompleksin kaava (jossa funktiona argumentteja käyttäen muita toiminto, eli jotkut toiminnot on upotettu muihin), tai löytää se lähde virheitä, usein tarvetta laskea osa sitä. On kaksi helppoa tapaa:
- Laskea oikealla puolella kaava kaavarivillä, valitse osa, ja paina F9:
Tässä esimerkissä, oli ongelma toiminto HAKU (SEARCH) - oli sekoitettu ylös argumentteja. On tärkeää muistaa, että jos et peruuta laskennan funktion ja paina Enter, niin laskettu osuus pysyy numero. - Paina "Laske kaava" ryhmässä "kaava" nauhalle:
Ikkunassa, että näyttöön tulee, voidaan laskea kaavan askel askeleelta ja määrittää, missä vaiheessa ja missä funktio virhe (jos sellaisia on):
Miten määritellä, mitä määrittää, että kaava tai viitattu
Sen määrittämiseksi, mitkä solut riippuu kaavan ryhmää, jolla on "kaava" on nauha, klikkaa painiketta "Trace ennakkotapauksia":
On nuolet, josta tulos riippuu.
Jos symboli valitun kuvan punainen, kaavan riippuu soluja, jotka ovat muiden listojen tai muita kirjoja:
Klikkaamalla sitä, voimme nähdä tarkalleen missä vaikutuksen solun tai alue:
Seuraava "Trace ennakkotapauksia" on painike "huollettavien", toimi samalla tavoin: se näyttää nuolilla aktiivisen solun, jolla on kaava soluihin, jotka ovat riippuvaisia siitä.
Painike "Poista nuolet" sijaitsee samassa lohkossa, voidaan poistaa ennakkotapauksia, huollettavien, tai molempia samanaikaisesti nuolet:
Miten löytää määrään (numero, keskiarvo) arvot solujen useita arkkeja
Oletetaan, että on useita samanlaisia arkin tiedot, jotka haluat lisätä, löytää tai käsitelty muulla tavoin:
Voit tehdä tämän, solu, johon haluat nähdä tuloksen, kirjoita standardikaava, kuten SUM (summa), ja syöttää argumentin seuraa kaksoispiste ja sukunimi ensimmäisen levyn luettelosta arkkien tarvitset prosessi:
Saat määrä soluosoitteen B3 lakanat "Data1", "Data2", "data3":
Kuten osoitteiden antaminen arkkien järjestetty peräkkäin. Syntaksi on: FEATURE = (pervyy_list: posledniy_list viittaus alue!).
Miten automaattisesti rakentaa kaavamainen lauseita
Käyttäen perusperiaatteet tekstipohjaisille Excel ja muutamia yksinkertaisia toimintoja voidaan valmistaa kaavamaisesta lausekkeet raportteja. Useat periaatteet käsitellä tekstiä:
- Yhdistä tekstiä Mark & (voi korvata sen toimintaa CONCATENATE (CONCATENATE), mutta tämä ei ole paljon käyttöä).
- Teksti kirjoitetaan aina lainausmerkeissä, solu viittaukset tekstiin - ei aina.
- Saada erikoismerkki "lainausmerkkejä", käyttäen merkki toiminto (CHAR) argumentti 32.
Esimerkki luoda malli lauseita käyttäen kaavat:
tulos:
Tällöin lisäksi funktiosymboliin (CHAR) (näyttää lainausmerkkejä) käyttämällä toimintoa, jos (IF), joka mahdollistaa muuttaa tekstiä Riippuen siitä, onko olemassa positiivinen myynnin kehitys ja toiminta tekstin (text), jonka avulla on mahdollista näyttää useita millään formaatissa. Sen syntaksi on kuvattu alla:
Teksti (arvo; muoto)
Muoto on määritetty lainausmerkkeihin samalla tavalla kuin jos syötit mukautetun muodon "Muotoile solut".
Voit automatisoida monimutkaisempia tekstejä. Minun Käytännössä oli automatisoida pitkä, mutta rutiini kommentit tileissä muodossa "luku putosi / ruusu klo XX suunnitelmasta lähinnä lisäys / lasku FAKTORA1 XX lisäys / lasku FAKTORA2 YY... »muuttuvaa lista tekijät. Jos kirjoitat tällaisia kommentteja usein ja kirjoittamassa can algorithmization - arvoinen kerran ymmällään kaavan muodostamiseen tai makro, joka säästää ainakin osa työtä.
Miten tallentaa tiedot jokaisessa solussa sulautumisen jälkeen
Kun yhdistetään solut säilytetään vain yksi arvo. Excel varoittaa, kun yrität yhdistää soluja:
Vastaavasti, jos sinulla olisi kaava, joka riippuu kunkin solun, se lakkaa toimimasta, kun heidän liittonsa (bugi # N / A riveillä 3-4 esimerkkejä):
Ja yhdistämisen soluja, samalla kun säilytetään data kullakin niistä (ehkä on kaava, kuten tässä esimerkissä, tiivistelmä; kannattaa yhdistää soluja, mutta pitää kaikki tiedot tulevaisuudessa, tai piilottaa aikeistaan), yhdistää eri solussa levyt, valitse ne ja sitten käyttää komentoa "Muotoilusivellin" siirron muodossa soluihin että sinun ja yhdistellä:
Miten rakentaa yhteenveto useiden tietolähteiden
Jos täytyy rakentaa konsolidoitiin useita tietolähteitä, on tarpeen lisätä nauha tai pikatyökaluriville "PivotTable Wizard ja kaavioita", jossa on tällainen vaihtoehto.
Voit tehdä tämän seuraavasti: "Tiedosto» → «Settings» → «Pikatyökalurivi» → «kaikki tiimin» → «PivotTable Wizard ja kaavioita» → «Lisää»:
Tämän jälkeen nauha tulee vastaava symboli, klikkaamalla joka aiheuttaa lisäyksen Master:
Kun klikkaat sitä, valintaikkuna:
Siinä sinun täytyy valita "Muutama bändit vakauttamisen" ja klikkaa "Next". Seuraavassa osassa, voit valita "Luo yhden sivun kentän" tai "Luo sivun kenttään." Jos haluat itse keksiä nimi jokaiselle tietolähteiden - Valitse toinen kohde:
Seuraavassa ruudussa lisätä kaikki alueet pohjalta, joka rakentaa tiivistelmän, ja kysyä heiltä nimet:
Tämän pisteen jälkeen isännöi pivot viime valintaikkuna - olemassa olevaan tai uuteen laskentataulukkoon:
Raportissa PivotTable valmiina. Suodatin "Sivu 1" voit valita vain yhden tietolähteistä tarvittaessa:
Miten lasketaan esiintymisten määrä tekstiä tekstiin A B ( «MTS tariffi SuperMTS" - kaksi esiintymää lyhenne MTS)
Tässä esimerkissä sarakkeessa A, on olemassa muutama rivi tekstiä, ja meidän tehtävä - selvittää, kuinka monta kertaa kukin niistä täyttää vaaditut solun tekstin E1:
Tämän ongelman ratkaisemiseksi, voit käyttää monimutkaista kaavaa, joka koostuu seuraavista toiminnoista:
- LEN (LEN) - laskee tekstin pituus, yksi argumentti - tekstin. Esimerkki: LEN ( "kone") = 6.
- SUBSTITUTE (varajäsen) - korvaa merkkijono tietyn tekstin toisen. Syntaksi: korvaava (tekstin; star_tekst; nov_tekst). Esimerkki: SUBSTITUTE ( "auto", "auto", "") = "matkapuhelin".
- Ylempi (ylempi) - korvaa kaikki merkkijonon merkkien isoiksi. Ainoa argumentti - tekstiä. Esimerkki: UPPER ( "kone") = "kone". Tätä ominaisuutta tarvitaan, jotta haku Kirjainkoolla. Loppujen lopuksi ISOT ( "Kone") = ISOT ( "Kone")
Löytää tietty tulo merkkijonon toiseen, sinun on poistettava kaikki sen esiintymät alkuperäisessä ja vertaa merkkijonon pituus alkuperäisestä:
LEN ( "Tariffi Super MTS MTS") - LEN ( "Super Rate") = 6
Ja sitten jakaa erotus pituuden linjan, etsimme:
6 / LEN ( "MTS") = 2
Se on kaksinkertainen rivi "MTS" on alkuperäisessä.
Vielä on kirjoitettu kielellä algoritmin kaavojen (merkitty "text" tekstiä joihin haemme maahantulon ja "haluttu" - tapahtumien määrä, että olemme kiinnostuneita):
= (LEN (teksti) -DLSTR (SUBSTITUTE (ylempi (teksti); UPPER (haluttuja varten); ""))) / LEN (haluttuja varten)
Tässä esimerkissä kaava on seuraava:
= (LEN (A2) -DLSTR (SUBSTITUTE (ylempi (A2); YLÄLANGAN ($ E $ 1); ""))) / LEN ($ E $ 1)