Excel: Решаем Реальные Задачи из Собеседования

В этом видео мы разберемся с тестовым заданием по Excel, которое включает в себя 5 разнообразных задач, встречающихся на реальных собеседованиях. Задания варьируются от базового до среднего уровня сложности, с фокусом исключительно на формулах Excel, без анализа данных. Научитесь эффективно использовать такие функции, как "ЕСЛИ", "ПВР", и "ПОИСКПОЗ". Этот мастер-класс поможет вам улучшить ваши навыки работы с Excel и подготовиться к вопросам на собеседованиях.
Ссылка к тестовому заданию: docs.google.com/spreadsheets/...
Timecodes
00:00 - Интро
00:44 - Задание 1
03:42 - Задание 2
10:03 - Задание 3
14:22 - Задание 4
15:28 - Задание 5

[@user-jn3hi9cz6s]

Самые интересные видеоролики- это разбор решения тестовых задач из собеседования. Лайк и подписка.

[@adey7813]

ОЧень круто!) сначала пыталась решить сама, и потом уже смотрела, что делаете вы!) спасибо, отличная практика! выставляйте побольше таких разборов

[@user-oz9pk2ty2t]

Объясняете вы хорошо динамично, понятно, не нудно.

[@user-OlgaL]

Видео и объяснения огонь. С удовольствием просмотрела и повторила задания. Отдельное спасибо за файл к этому уроку. Очень интересно и увлекательно. Успехов вашему каналу.

[@afakgulieva1418]

Очень интересное видео ! Всё предельно понятно и по делу ! Желаю процветания Вашему каналу !🎉🎉🎉

[@Berlin062011]

Спасибо за отличное видео! Потренировалась , нашла пробел в знании функции ранг и частота. Лайк и подписка.

[@SigmaOnWork]

Тема видео - великолепна. Разбор - почти прост и понятен. Я бы с удовольствием посмотрел бы ещё несколько подобных роликов для повышения самообразованности. Но задание 2 явно решается иначе. 1я мысль - через НАИБОЛЬШЕЕ. Если не лень будет разобраться - напишу ответ на этот пост.

[@KrabNaGalerah]

Напишите, интересно будет посмотреть) Ну и хотелось бы добавить, я обычный офисный сотрудник, эксель начал изучать года 3 назад, до этого имелись базовые знания, даже ВПР не знал)) Поэтому интересно посмотреть на решение с другой перспективы.

[@dianayaraschewskaya6739]

Отличное видео! Все понятно и по делу! Спасибо большое 🎉

[@user-ii5bo2cu5k]

Спасибо за видео! Успехов вашему каналу, очень ждём новые видео

[@-VelikoDushniy-]

Когда я искал место заказа от просто использовал ПРОСМОТРХ. Намнооооого удобнее чем ВПР. Заодно узнал про формулу ПОИСКПОЗ и ИНДЕКС)

[@user-li5zn3wh9h]

Интересные задачи и хорошее объяснение)

[@bmwbmw4248]

Отличная работа 👍

[@user-cp6he5wq3r]

Спасибо. В 1 задании в 3м столбце вместо индекса и поискпоз можно ввести одну функцию "просмотрx", она прекрасно делает тоже самое, но она есть только в office 365

[@KrabNaGalerah]

Все верно. Однако нужно учитывать, что на большинстве предприятий стоят более старые версии офиса. Поэтому не советую делать тестовые задания с использованием новых функций, если это конкретно не требуется в задании. Иначе его просто не смогут проверить.

[@user-op9vw4vr9m]

Ну не только в 365, в офисе 2021 тоже есть

[@user-ed2ur8pt6d]

Спасибо, очень полезно, особенно логика , последняя задача прям в тему

[@alexshavlo6701]

Колво услуг - вы приняты!

[@dmitryking]

Для пятого задания есть иной (как мне кажется - более элегантный) способ без использования доп. столбцов - через функции ЗНАК и ВЫБОР. Выделяем подзадачу, аналогично видео: ранжируем клиентов в зависимости от того, были или нет они в первые три месяца и во вторые три месяца. Если клиенты были, то сумма посещений за три месяца больше нуля, если нет - то она равна нулю. Чтобы вместо всех ненулевых сумм получить 1, а при нулевых - 0, используем функцию ЗНАК (отрицательных значений тут не будет по логике примера). ЗНАК(СУММ(B12:D12)) = для определения "да (1) или нет (0)" для первого квартала, и ЗНАК(СУММ(E12:G12)) - соответственно, для второго. Чтобы различить значение за первые три месяца от последних трех месяцев, умножим результат второй функции на 2 и сложим два полученных значения: =ЗНАК(СУММ(B12:D12))+ЗНАК(СУММ(E12:G12))*2 Пполучаем одно из четырех значений: 0+0 = 0 - Не посещал ТЦ ни в 1 кв., ни во 2 кв. = "Ушедший"; 1+0 = 1 - Посещал в 1 кв., не посещал во 2 кв. = "Спящий"; 0+2 = 2 - Посещал во 2 кв., не посещал в 1 кв. = "Новый"; 1+2 = 3 - Посещал ТЦ и в 1 кв., и во 2 кв. = "Действующий". Для выбора вариантов, вместо вложенных "Если...То...Иначе", можно использовать одноименную функцию "ВЫБОР", которая выводит один из вариантов в зависимости от первого аргумента. Т.к. номер должен быть больше нуля, то надо добавить к вычисленному выше значению 1, тогда номера вариантов изменятся на 1,2,3,4. Итоговая функция: =ВЫБОР(ЗНАК(СУММ(B12:D12)) + ЗНАК(СУММ(E12:G12))*2+1; "Ушедший";"Спящий";"Новый";"Действующий") Вставить в 12ю строку, протянуть по таблице. Подсчет кол-ва клиентов по группам - аналогично видео, по СЧЁТЕСЛИ. П.С.: - Ушедший клиент либо спился, либо умер. - Скажите, а есть другие выводы по RFM-аналитике? 🙂

[@KrabNaGalerah]

Соглашусь, Ваше решение более элегантное, однако не совсем подходит по сложности к заданию. Вроде бы функции простые, однако их комбинации сделаны очень хорошо и продуманы до мелочей, до такого уровня нужно долго практиковаться, поэтому мой "трактор", думаю здесь более уместен, чем Ваша "иномарка") Хотя для более продвинутых пользователей решение конечно более чем интересное. Спасибо)

[@jennab3889]

Мне кажется есть решение проще для определения статуса в задаче №5 (без добавления доп.столбцов) - через функцию ЕСЛИМН, у меня вышло так. Единственный момент - важно проставлять знак равно перед 0, в противном случае формула работает некорректно =ЕСЛИМН(И(СУММ(B23:D23)>0;СУММ(E23:G23)0;СУММ(E23:G23)>0);"Действующий";СУММ(B23:G23)

[@s1lentgrave]

задание 5, чтобы без дополнительных столбцов: в ячейку H12 эту формулу =ЕСЛИ(СЧЁТЗ(B12:D12)>0;ЕСЛИ(СЧЁТЗ(E12:G12)>0;"действующий";"спящий");ЕСЛИ(СЧЁТЗ(B12:D12)=0;ЕСЛИ(СЧЁТЗ(E12:G12)=0;"ушедший";"новый")))

[@TatianaBelokon]

Спасибо огромное, разбираемся с решением. Просто отлично. Для уникального значения можно было использовать готовую функцию =УНИК(B5:B17)

[@KrabNaGalerah]

Уник хорошая функция, однако много у кого еще старые версии экселя, поэтому решил показать, как можно решить задачу с ними. Ну а у кого Уник есть, что там показывать)

[@user-cg3wf6ve8s]

@@KrabNaGalerah Благодарю за заботу! У меня очень старая версия

[@user-rn6zz3cm2b]

У канала есть будущее. Только настройте поток аудитории, напр через ютифайкак вариант, а то у вас сейчас очень мало смотрят и комментируют.

[@KrabNaGalerah]

а что за ютифай? Пока что просмотров мало, так как видео мало, да и канал новый, его алгоритмы ютуба не особо продвигают. Со следующей недели буду делать еженедельно по одному видео, тогда посмотрим как пойдет.

[@atlantam9343]

@@KrabNaGalerah Рекомендую в названиях ролиуов писать ”в помощь бухгалтеру”. Сейчас эксель и такие задания очень актуально для бухгалтеров, кассиров

[@Livan_1983]

Очень очень годный контент, за такой труд можно и донатов подкинуть! Напишите пож-та свой координаты, куда можно поблагодарить за сей труд🤝

[@KrabNaGalerah]

Спасибо, очень приятно) Можете написать в телегу, если действительно все так серьезно)

[@user-nf2yq2jj4f]

Красавчик, правда пока тяжело в голову заложить массивы и индексы((.

[@BORISVLEONOV]

Да, отлично! Очень полезные видео с разбором. Например, ChatGPT не смог правильно решить задачу № 3 - Рейтинг 5 пропустил)), хотя описал настолько подробно, насколько возможно 😊

[@KrabNaGalerah]

Часто с такими задачами чатгпт не справляется, еще любит выдумывать несуществующие функции)

[@user-gl2gm3si5i]

Спасибо за очень полезный и нужный контент! Как определить, где нужно применять формулу массива (Ctrl+shift+enter), а где нет?

[@KrabNaGalerah]

Хороший вопрос, это когда на машине начинаешь ездить, и не всегда понятно, когда передачу понижать, а когда к примеру можно повысить. Здесь только практика поможет. Посоветую для начала посмотреть ролики, где разбирают саму работу формул массива, после того, как поймете принцип работы, начинайте практиковать, у меня к примеру на канале еще есть другие задания, где используются формулы массива. Сначала смотрите, потом сами стараетесь решить, и главное не заучить, а понять.

[@user-oz9pk2ty2t]

Самое сложное для меня оказалось Задание№2 - подсчёте рейтинга (сама не смогла)😢

[@user-nd7gg4hw3l]

А как файл скачать ?

[@user-op9vw4vr9m]

Не совсем понятна целесообразность добавления вспомогательных столбцов в последнем задании. Там же через функцию ЕСЛИ, с которой вы начали, всё прекрасно работает. Формула, конечно, получается длинная, так как соединяет все четыре формулы, которые вы использовали по отдельности в каждом столбце. Но с одной формулой и без дополнительных столбцов, по-моему эстетичнее выглядит и проще, так как не нужно применение ПОИСКПОЗ и ИНДЕКС

[@KrabNaGalerah]

В экселе очень много путей, в данных видео моя задача, не просто решить задачи, а еще показать, как их решать, чтобы каждый шаг был понятен для всех. Я конечно мог бы написать все в одну формулу, но проблема длинных формул в том, что в них легко допустить ошибку, во вторых их достаточно сложно "дебажить", ну и самое главное, их муторно объяснять. Если бы по условию задачи, нам нельзя было бы использовать дополнительные столбцы, был бы другой вопрос. Кстати я лично не считаю, что более эстетичный метод -- это закидывать все в одну формулу. Скажу это со стороны программиста, код должен быть читаемым, лучше больше кода, но более понятного, чем накидать все в одну строку. В экселе длинные формулы очень сложно читать, даже продвинутым пользователям, поэтому все же советую разделять их. Если хотите эстетики, то можно использовать менеджер имен, вместо дополнительных ячеек.

[@user-cp6he5wq3r]

8:37 а что значит что столбец больше чем текущая ячейка? Что с чем он сравнивает не совсем понятно. Все значения в столбце должны быть больше чем заданная ячейка?

[@KrabNaGalerah]

формула действительно очень сложная для понимания. Поэтому совету разложить ее на дополнительные столбцы. Первый столбец, это каждое значение ячейки > первая ячейка. Далее с этим столбцом делаете все те же манипуляции. Для каждой строчки свой столбец. Если Вы все это вручную введете, то думаю понимание должно придти.

[@user-er8ws6hp5q]

Здравствуйте. Я только начала разбираться в функциях excel, и не могу понять, почему в первой задаче использована функция СУММЕСЛИ. Что с чем суммируется?

[@KrabNaGalerah]

В первой задаче можно использовать и ВПР, так как номера заказов во второй таблице не повторяются. Однако, допустим, что вторая таблица будет дополнятся, либо все же есть дубликаты, в таком случае мы не правильно оборот по кассе посчитаем. В случае с СУММЕСЛИ, мы посчитаем все по кассе, сколько бы у нас кодов бы не было. Но так как код всего один, получаем мы одно значение оборота по кассе. В эксель очень много способов решить одну и ту же задачу, поэтому не стесняйтесь находить свои методы) Кстати если на собеседовании спросят о данном примере, то думаю будет большим плюсом, если Вы дадите такое пояснение.

[@atlantam9343]

СПАСИБО, очень полезно!

[@s1lentgrave]

как вы при вводе диапазона в формулу выделяете целиком все нужные значения? не могу нагуглить. и как так же быстро протягивать формулу на все строки в конкретной таблице как на 13:33?

[@s1lentgrave]

ктрл + шифт + стрелочка вниз а как заполнять не выделяя диапазон, я не нашел

[@KrabNaGalerah]

двойной щелчок по правому нижнему углу ячейки.

[@s1lentgrave]

@@KrabNaGalerah спасибо! я вроде пробовал это вчера делать но почему-то не срабатывало. сейчас все работает

[@user-cg3wf6ve8s]

Здравствуйте. Что не так с моей формулой из третьей странички : =ИНДЕКС($F$17:$F$73;ПОИСКПОЗ(1;ПОИСК(A5;$F$17:$F$73);0))

[@bokbars7737]

Задание №3 можно выполнить сводной таблицей, объединяя 2 таблицы по критерию "Субъект".

[@KrabNaGalerah]

если в тестовом задании не упоминаются сводные, то лучше их не использовать. Смысл тестовых, понять какими инструментами и на каком уровне Вы владеете.

[@Igoryan23reg]

Здравствуйте. У меня проблема, как определять, куда какую формулю подставлять??? Может что подскажите?

[@KrabNaGalerah]

Это не проблема, просто у Вас нет базовых знаний. Эти тестовые задания уже для тех, кто работает в экселе и понимает строение формул. Поэтому советую для начала пройти базовый курс по экселю, а потом уже подходить к более сложным задачам. Если будут какие то конкретные вопросы, то задавайте)

[@Igoryan23reg]

@@KrabNaGalerah может что посоветуйте из базового курса?

[@KrabNaGalerah]

@@Igoryan23reg Посмотрите Езипова Дмитрия. Если зайдет, то потом советую прочитать 2 первые книги от Павлова, после них у Вас будут знания на очень достойном уровне и такие тестовые будете щелкать как орешки.

[@Igoryan23reg]

Спасибо 🤝

[@user-km2tb9dn9y]

прикрепленный файл доступен только в режиме просмотра. У всех так?

[@ISparkl]

На какой должности могут понадобиться эти знания?

[@KrabNaGalerah]

На любой, где нужнен эксель, очень актуально для бухгалтеров, агрономов, логистов.

[@user-wv3qf8zq3l]

Я не знаком с индексом, поэтому вместо него бы использовал Если в 5-й

[@Sonni-mx9ok]

В задании №2 с рейтингом, у нас отсортирована частота упоминаний. В С5 пишем 1, в С6 формулу, если( В5=В6;С5;С5+1)

[@KrabNaGalerah]

Не совсем согласен, нигде не указано, что у нас таблица отсортирована, достаточно добавить какое то значение в нижнюю таблицу и формула работать будет не корректно. Такие формулы, я называю костылями, вроде работает, однако стоит в данных что то подправить, и костыль не справится. Возможно, тот кто делал это тестовое задание, с такой же простой формулой его и решил бы (так как задание слишком сложное для данного тестового), но опять же, в таком случае, он сам не корректно поставил задачу.

[@user-uu6iu4yr4k]

@@KrabNaGalerah Кстати, касательно задания №2 с рейтингом - пришла мысль использовать такую комбинацию, чтобы решить проблему дубликатов - РАНГ.РВ(B5;УНИК($B$5:$B$17)) - но почему-то у меня office 365 ругается на формулу. Не могу понять почему не срабатывает - может есть у кого-нибудь мысли по этому поводу ?

[@KrabNaGalerah]

@@user-uu6iu4yr4k точно так же пробовал, УНИК очень мало где в связке работать может, поэтому здесь увы без него.

[@user-uu6iu4yr4k]

@@KrabNaGalerah При этом в docs.google с UNIQUE прекрасно работает - получается недоработка майкрософтовцев)

[@user-uu6iu4yr4k]

@@KrabNaGalerah при этом в docs.google с UNIQUE прекрасно работает - получается в Excel недоработочка)

[@Paul_Bibik]

Видео для тех кто уже хоть мало мальски эксель знает, кто еще учится, какие то моменты непонятны 😔 Как в школе - приходится записывать и как стишок учить.

[@hodya84]

Что за любовь к ВПР не понимаю функция просмотрХ решает все вопросы поиска в любом столбце не нужен и впр и поиск по

[@KrabNaGalerah]

не у всех есть новая версия эксель. А так можете решать задания, как Вам нравится, здесь, ведь не важно какие функции, важно понимание что нужно делать.

[@glebkuvaldin3146]

3:07 , а что нельзя было просто столбик " место забора " скопировать и в ставить в пустой столбец " Е" и через ВПР в 2 клика все это найти , а не через эту громоздкую формулу😮

[@KrabNaGalerah]

нельзя, это тестовое задание, в этом и есть смысл, проверить знания на формулы.

[@vlad050987]

ПРОСМОТРХ для 3 задания