Есеп беру және деректерді өңдеумен айналысатындарға арналған Excel лайфхактары

2024 Автор: Malcolm Clapton | [email protected]. Соңғы өзгертілген: 2024-01-13 02:10

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

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

Қажетті мәндер кестенің бірінші бағанында болмаса, VLOOKUP және HLOOKUP үшін қарапайым баламалар: ІЗДЕУ, INDEX + SEARCH

VLOOKUP және HLOOKUP функциялары қажетті мәндер деректерді алуды жоспарлаған кестенің бірінші бағанында немесе жолында болған жағдайда ғана жұмыс істейді.

Әйтпесе, екі нұсқа бар:

1. ІЗДЕУ функциясын пайдаланыңыз.

   Оның келесі синтаксисі бар: ІЗДЕУ (іздеу_мәні; іздеу_векторы; нәтиже_векторы). Бірақ оның дұрыс жұмыс істеуі үшін 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 («Тариф MTS Super MTS») - DLSTR («Супер тариф») = 6

Содан кейін бұл айырмашылықты біз іздеген жолдың ұзындығына бөліңіз:

6 / DLSTR («MTS») = 2

Түпнұсқаға «МТС» желісі екі рет қосылды.

Бұл алгоритмді формулалар тілінде жазу қалды (біз іздеп отырған оқиғаларды «мәтінмен» белгілейік, ал «ізденген» - бізді оқиғалардың саны қызықтыратын мәтінді белгілейік):

= (DLSTR (мәтін) -LSTR (SUBSTITUTE (ЖОҒАРЫ (мәтін); UPPER (іздеу), ""))) / DLSTR (іздеу)

Біздің мысалда формула келесідей көрінеді:

= (DLSTR (A2) -LSTR (АЛМАСТЫРУ (ЖОҒАРЫ (A2), ЖОҒАРЫ ($ E $ 1), ""))) / DLSTR ($ E $ 1)