Excel лайфхактары отчеттуулук жана маалыматтарды иштетүү менен алектенгендер үчүн

2024 Автор: Malcolm Clapton | [email protected]. Акыркы өзгөртүү: 2024-01-13 02:14

Бул постто Mann, Ivanov жана Ferber басмаканасынын башкы директорунун жардамчысы Ренат Шагабутдинов Excel программасынын кээ бир сонун лайфхактары менен бөлүшөт. Бул кеңештер ар кандай отчеттуулукка, маалыматтарды иштетүүгө жана презентацияларды түзүүгө катышкандар үчүн пайдалуу болот.

Бул макалада Excelдеги жумушуңузду жөнөкөйлөтүү үчүн жөнөкөй ыкмалар камтылган. Алар башкаруу отчеттуулук менен алектенген, 1С жана башка отчетторду жүктөп негизинде ар кандай аналитикалык отчетторду даярдоо, башкаруу үчүн алардан презентацияларды жана диаграммаларды түзүү үчүн өзгөчө пайдалуу. Мен абсолюттук жаңылык деп ойлобойм - тигил же бул формада бул ыкмалар форумдарда талкууланган же макалаларда айтылган.

VLOOKUP жана HLOOKUP үчүн жөнөкөй альтернативалар, эгерде керектүү маанилер таблицанын биринчи тилкесинде болбосо: LOOKUP, INDEX + SEARCH

VLOOKUP жана HLOOKUP функциялары керектүү маанилер таблицанын биринчи тилкесинде же сапында болгондо гана иштейт, анда сиз маалымат алууну пландап жатасыз.

Болбосо, эки вариант бар:

1. LOOKUP функциясын колдонуңуз.

   Анын төмөнкү синтаксиси бар: ИЗДЕГЕН (издөө_маани; издөө_вектору; натыйжа_вектору). Бирок ал туура иштеши үчүн, view_vector диапазонунун маанилери өсүү тартибинде иреттелиши керек:

   

2. MATCH жана INDEX функцияларынын айкалышын колдонуңуз.

   MATCH функциясы массивдеги элементтин иреттик номерин кайтарат (анын жардамы менен изделген элемент таблицанын кайсы сабында экенин таба аласыз), ал эми INDEX функциясы берилген номери бар массив элементин кайтарат (аны биз табабыз) MATCH функциясын колдонуу).

   

   Функция синтаксиси:

   • SEARCH (search_value; search_array; match_type) - биздин учурда бизге дал келген "так дал келүү" түрү керек, ал 0 санына туура келет.

   • INDEX (массив; сап_саны; [тилке_саны]). Бул учурда, массив бир саптан тургандыктан, тилкенин номерин көрсөтүүнүн кереги жок.

Тизмедеги бош уячаларды кантип тез толтуруу керек

Тапшырма графадагы уячаларды жогору жагындагы маанилер менен толтуруу (тема тема боюнча китептер блогунун биринчи сапында гана эмес, таблицанын ар бир сабында болушу үчүн):

"Тема" тилкесин тандап, "Башкы бет" тобундагы лентаны чыкылдатыңыз, "Табу жана тандоо" баскычын басыңыз → "Уячалардын тобун тандоо" → "Бош уячаларды" жана формуланы киргизе баштаңыз (б.а. белгиси) жана баскычтоптогу өйдө жебени чыкылдатуу менен, өйдө жактагы уячага кайрылыңыз. Андан кийин Ctrl + Enter басыңыз. Андан кийин, сиз алынган маалыматтарды баалуулуктар катары сактай аласыз, анткени формулалардын кереги жок:

Формуладагы каталарды кантип тапса болот

Формуланын өзүнчө бөлүгүн эсептөө

Татаал формуланы түшүнүү үчүн (мында башка функциялар функциянын аргументтери катары колдонулат, б.а. кээ бир функциялар башкаларына уя салынган) же андагы каталардын булагын табуу үчүн көбүнчө анын бир бөлүгүн эсептөө керек. Эки жеңил жолу бар:

1. Формула тилкесинде формуланын бир бөлүгүн эсептөө үчүн, ошол бөлүктү тандап, F9 баскычын басыңыз:

   

   Бул мисалда SEARCH функциясында көйгөй бар болчу - анда аргументтер алмаштырылган. Эгерде сиз функциянын бөлүгүн эсептөөнү жокко чыгарбасаңыз жана Enter баскычын бассаңыз, анда эсептелген бөлүк сан бойдон калаарын эстен чыгарбоо керек.

2. Тасмадагы Формулалар тобундагы Формуланы эсептөө баскычын чыкылдатыңыз:

   

   Пайда болгон терезеде сиз формуланы этап-этабы менен эсептеп, ката кайсы этапта жана кайсы функцияда пайда болгонун аныктай аласыз (эгерде бар болсо):

   

Формула эмнеден көз каранды же тиешелүү экенин кантип аныктоого болот

Формула кайсы уячаларга көз каранды экенин аныктоо үчүн лентадагы Формулалар тобунда Таасир кылуучу клеткалар баскычын басыңыз:

Жебелер эсептөө натыйжасы эмнеден көз каранды экенин көрсөтүп турат.

Эгерде сүрөттө кызыл түс менен белгиленген символ көрсөтүлсө, анда формула башка барактардын же башка китептердин уячаларына жараша болот:

Аны басуу менен биз таасир этүүчү клеткалар же диапазондор кайда жайгашканын так көрө алабыз:

"Таасир кылуучу уячалар" баскычынын жанында "Көз каранды уячалар" баскычы жайгашкан, ал дагы ушундай эле иштейт: формуласы бар активдүү уячадан ага көз каранды болгон уячаларга жебелерди көрсөтөт.

Ошол эле блокто жайгашкан "Жебелерди алып салуу" баскычы таасир этүүчү уячаларга жебелерди, көз каранды уячаларга жебелерди же жебелердин эки түрүн бир эле учурда алып салууга мүмкүндүк берет:

Бир нече барактан уячалардын маанилеринин суммасын (санын, орточосун) кантип тапса болот

Кошууну, санагыңызды же башка жол менен иштеткиңиз келген маалыматтары бар бир типтеги бир нече барактарыңыз бар дейли:

Бул үчүн, сиз жыйынтыкты көргүңүз келген уячага стандарттуу формуланы киргизиңиз, мисалы, SUM (SUM) жана сиз иштеп чыгуу керек болгон барактардын тизмесинен биринчи жана акыркы барактардын атын көрсөтүңүз. кош чекит менен бөлүнгөн аргумент:

Сиз "Data1", "Data2", "Data3" барактарынан B3 дареги бар уячалардын суммасын аласыз:

Бул даректүү барактар үчүн иштейт ырааттуу … Синтаксис төмөнкүдөй: = FUNCTION (биринчи_тизме: акыркы_тизме! Диапазондун маалымдамасы).

Калып фразаларды кантип автоматтык түрдө куруу керек

Excel программасында текст менен иштөөнүн негизги принциптерин жана бир нече жөнөкөй функцияларды колдонуу менен сиз отчеттор үчүн шаблондук фразаларды даярдай аласыз. Текст менен иштөөнүн бир нече принциптери:

• Биз текстти & белгисинин жардамы менен бириктиребиз (сиз аны CONCATENATE функциясы менен алмаштырсаңыз болот, бирок бул анча деле мааниге ээ эмес).
• Текст ар дайым тырмакчага жазылат, тексти бар уячаларга шилтемелер дайыма жок.
• "Тырмакча" кызмат белгисин алуу үчүн 32 аргументи менен CHAR функциясын колдонуңуз.

Формулаларды колдонуу менен шаблондук сөз айкашын түзүүнүн мисалы:

Натыйжа:

Бул учурда, CHAR функциясынан тышкары (тырмакчаларды көрсөтүү үчүн) оң сатуу тенденциясы бар экендигине жараша текстти өзгөртүүгө мүмкүндүк берүүчү IF функциясы жана TEXT функциясы колдонулат. каалаган форматта номер. Анын синтаксиси төмөндө сүрөттөлөт:

TEXT (маани; формат)

Формат сиз Формат ячейка терезесинде ыңгайлаштырылган форматты киргизип жаткандай, тырмакчада көрсөтүлгөн.

Дагы татаал тексттерди автоматташтырууга болот. Менин практикамда башкаруу отчетуна узак, бирок көнүмүш комментарийлерди автоматташтыруу “ИНДИКАТОР планга салыштырмалуу ХХ га төмөндөдү/көбөйдү, бул негизинен 1-ФАКТОРдун ХХ-га өсүшү/төмөндөөсү, 2-ФАКТОРдун өсүшү/төмөндөөсү менен шартталган. YY …” факторлордун өзгөргөн тизмеси менен. Эгер сиз мындай комментарийлерди тез-тез жазып турсаңыз жана аларды жазуу процессин алгоритмдештирүү мүмкүн болсо, анда жумуштун жок дегенде бир бөлүгүн сактап кала турган формула же макро түзүү табышмактуу.

Бириктирилгенден кийин ар бир уячада маалыматтарды кантип сактоо керек

Сиз уячаларды бириктиргенде, бир гана маани сакталат. Excel клеткаларды бириктирүүгө аракет кылып жатканда бул жөнүндө эскертет:

Демек, эгер сизде ар бир уячага жараша формула болсо, ал аларды бириктиргенден кийин иштебей калат (мисалы 3-4-саптарда # N / A ката):

Клеткаларды бириктирүү жана алардын ар бириндеги маалыматтарды сактап калуу үчүн (балким, сизде бул абстракттуу мисалдагыдай формула бардыр; балким, сиз клеткаларды бириктиргиңиз келет, бирок бардык маалыматтарды келечекте сактаңыз же аны атайылап жашыргыңыз келет), барактагы бардык уячаларды бириктириңиз, аларды тандап, анан форматтоону бириктиришиңиз керек болгон уячаларга өткөрүү үчүн Формат боёгуч буйругун колдонуңуз:

Бир нече маалымат булактарынан пивотту кантип куруу керек

Эгерде сизге бир эле учурда бир нече маалымат булактарынан пивот куруу керек болсо, анда мындай варианты бар лентага же тез жетүү панелине "Жыйык таблица жана диаграмма устасын" кошушуңуз керек болот.

Муну төмөнкүдөй кылсаңыз болот: "Файл" → "Параметрлер" → "Тез жетүү куралдар панели" → "Бардык буйруктар" → "Баштык таблица жана диаграмма устасы" → "Кошуу":

Андан кийин, лентада тиешелүү сөлөкөт пайда болот, аны чыкылдатуу менен ошол эле устаны чакырат:

Аны басканда, диалог терезеси пайда болот:

Анда сиз "Бир нече консолидация диапазондорунда" пунктун тандап, "Кийинки" баскычын басышыңыз керек. Кийинки кадамда сиз "Бир бет талаасын түзүү" же "Барак талааларын түзүү" тандай аласыз. Эгерде сиз өз алдынча ар бир маалымат булактары үчүн ат тапкыңыз келсе, экинчи пунктту тандаңыз:

Кийинки терезеде, пивот курула турган бардык диапазондорду кошуп, аларга аттарды бериңиз:

Андан кийин, акыркы диалог терезесинде пивот таблицасынын отчету кайда жайгаштырыла турганын көрсөтүңүз - учурдагы же жаңы баракта:

Пивот столдун отчету даяр. "1-бет" чыпкасында, зарыл болсо, маалымат булактарынын бирин гана тандай аласыз:

В текстинде А текстинин пайда болушунун санын кантип эсептөө керек («МТС SuperMTS тарифи» - МТС аббревиатурасынын эки жолу)

Бул мисалда, А тилкесинде бир нече текст саптары бар жана биздин милдет алардын ар биринде E1 уячасында жайгашкан издөө тексти канча жолу камтылганын билүү:

Бул маселени чечүү үчүн төмөнкү функциялардан турган комплекстүү формуланы колдонсоңуз болот:

1. DLSTR (LEN) - тексттин узундугун эсептейт, бир гана аргумент бул текст. Мисал: DLSTR ("машина") = 6.
2. SUBSTITUTE – текст сабындагы белгилүү бир текстти башкасына алмаштырат. Синтаксис: SUBSTITUTE (текст; эски_текст; жаңы_текст). Мисал: SUBSTITUTE (“унаа”; “авто”; “”) = “мобилдик”.
3. UPPER - саптагы бардык символдорду баш тамга менен алмаштырат. Жалгыз аргумент бул текст. Мисал: ЖОГОРКУ (“машина”) = “CAR”. Бул функция бизге чоң тамгаларды сезбестен издөө үчүн керек. Анткени, ЖОГОРКУ ("машина") = ЖОГОРКУ ("Машина")

Белгилүү бир текст саптын башкасында пайда болушун табуу үчүн, анын түпнускадагы бардык көрүнүштөрүн өчүрүп, натыйжада пайда болгон саптын узундугун түпнуска менен салыштырышыңыз керек:

DLSTR (“Тариф МТС Супер МТС”) - DLSTR (“Тариф Супер”) = 6

Анан бул айырманы биз издеп жаткан саптын узундугуна бөлүңүз:

6 / DLTR (“MTS”) = 2

"МТС" линиясы түп нускага эки жолу кошулган.

Бул алгоритмди формулалардын тилинде жазуу калды (келгиле, биз окуяларды издеп жаткан текстти “текст” менен белгилейли, ал эми “изделген” дегенди - биз окуялардын саны кызыктырган текстти белгилейли):

= (DLSTR (текст) -LSTR (SUBSTITUTE (ЖОГОРКУ (текст); UPPER (издөө), ""))) / DLSTR (издөө)

Биздин мисалда формула төмөнкүдөй көрүнөт:

= (DLTR (A2) -LSTR (АЛМАШТЫРУУ (ЖОГОРКУ (A2), ЖОГОРКУ ($ E $ 1), ""))) / DLSTR ($ E $ 1)