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

[@user-oz9pk2ty2t]

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

[@adey7813]

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

[@МиссА-о6с]

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

[@SigmaOnWork]

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

[@KrabNaGalerah]

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

[@ПолинаКоношанова]

Есть ответ? Интересно

[@user-OlgaL]

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

[@ПолинаКоношанова]

Спасибо! Очень интересно, надо проработать задание с рангом. И проверить предложенные варианты других комментаторов для решениями задания 5

[@Network-nt]

=СУММПРОИЗВ((B5

[@Berlin062011]

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

[@СергейБобышкин-к5х]

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

[@KrabNaGalerah]

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

[@СергейС-э5й8й]

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

[@TatianaBelokon]

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

[@KrabNaGalerah]

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

[@user-cg3wf6ve8ssi]

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

[@-VelikoDushniy-]

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

[@s1lentgrave]

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

[@bvghk-z3j]

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

[@afakgulieva1418]

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

[@dianayaraschewskaya6739]

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

[@ЕвгенияКузина-т4э]

В ответах по статусу есть ошибки )))у вас верно получается по логике ,но если сравнить ваши ответы и ответы в задании есть несостыковки ,например в строке 85

[@ИльяОвчинников-и7г]

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

[@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]

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

[@ПолинаКоношанова]

Подскажите, а для 2го задания с рангом есть ещё варианты решения?

[@dmitryking]

@@ПолинаКоношанова На многих сайтах используется конструкция именно через СЧЁТ(1/ЧАСТОТА). Но в Excel 2021 (а в Office 365 - с 4 квартала 2020 г.) были добавлены динамические массивы и функции для работы с ними. Через них проще решаются задачи сортировки и просмотр уникальных значений списка (без дубликатов). Поэтому, если у Вас Эксель относитльно свежий, то можно использовать такую формулу: - вставляем ее в ячейку C5, нажимаем простой Enter и протягиваем ее по ячейку С17. [Душнила: ON] Объяснение работы: 1) - составляем массив уникальных (без повторов) значений по столбцу "Число упоминаний в таблице ниже (т.е. сколько раз Место забора упоминается в таблице "Исходные данные"); 2) - берем массив из п.1, сортируем его по убыванию (за это отвечает третий аргумент "-1"); можно рядом где-то вывести отдельно формулу из п.2, чтобы увидеть отсортированный массив, там будет шесть значений - {22, 15, 8, 6, 5, 4} 3) - ищем, какой позиции отсортированного списка соответствует число упоминаний конкретного ДЦ. Для 5 строки (ДЦ на Карповке) это 22 упоминания, первое место в списке. Для строк с одинаковым числом упоминаний позиция в отсортированном списке будет одна и та же. Например, для строк с 10 по 14 это будет пятая позиция, т.е. формула в столбце С для всех них выдаст результат "5". // [Душнила: OFF] П.С. Я на собеседованиях, когда у меня спрашивали "Какое у Вас уровень Эксель", задавал ответный вопрос - "А какая версия Экселя/Офиса у вас используется?". Так как использование тех же динамических массивов реально упрощает решение многих практических задач. А во многих компаниях до сих пор используется Excel 2016 (а то и 2013); поэтому часто во многих тестах проверяльщики хотят решений БЕЗ применения новых функций.

[@dmitryking]

@@ПолинаКоношанова Да, есть, например - через динамические массивы и формулы СОРТ и УНИК: =ПОИСКПОЗ(B5;СОРТ(УНИК($B$5:$B$17);;-1);0) ввести ее в ячейку C5 и протянуть по ячейку С17. Вопрос в том, что эти функции (а также функция ПРОСМОТРХ, которая также значительно проще, чем ВПР) были добавлены лишь в Excel, начиная с версии 2021 (а в Office 365 - с 4 квартала 2020 г.). А во многих компаниях до сих пор используется Excel 2016 (а то и 2013); поэтому часто во многих тестах проверяльщики хотят решений БЕЗ применения новых функций. Ранее написал расширенный ответ, но он пока на модерации.

[@ПолинаКоношанова]

@@dmitryking спасибо!

[@ЮраБуренко-л1г]

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

[@alexshavlo6701]

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

[@Ivoils]

Кошмар, в устной речи сокращать слова на столько. Прям слух режет.

[@user-oz9pk2ty2t]

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

[@ВикторЛобанов-г1я]

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

[@KrabNaGalerah]

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

[@atlantam9343]

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

[@jennab3889]

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

[@РамильНугаев-м9е]

Рахмат

[@ПолинаКоношанова]

Есть уточняющий вопрос про 2 задание с рангом. Там условие, что рейтинг по числу упоминай. Так вот, число 5 - упоминается 5 раз. Наибольшее количество повторений имеет. Поэтому должно иметь последний ранг, а не предпоследний как получилось. Этот момент не поняла 😢 помогите пожалуйста

[@Network-nt]

разобралась, вот функция в 1 строку, получаем так как надо. =СУММПРОИЗВ((B5

[@dmitryking]

@ПолинаКоношанова, думаю, все же под Числом упоминаний подразумевается "Число упоминаний в таблице ниже"; т.е.в столбце C - рейтинг по значению в столбце B.

[@BORISVLEONOV]

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

[@KrabNaGalerah]

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

[@ПолинаКоношанова]

А задачу 2 с рейтингом решил?

[@bmwbmw4248]

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

[@Network-nt]

вот решение для 2й задачи с рангом =СУММПРОИЗВ((B5

[@Дмитрий-у9ь5х]

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

[@KrabNaGalerah]

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

[@Livan_1983]

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

[@KrabNaGalerah]

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

[@С.К-я4у]

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

[@СергейС-э5й8й]

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

[@KrabNaGalerah]

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

[@bokbars7737]

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

[@KrabNaGalerah]

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

[@Elenash-d4s]

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

[@KrabNaGalerah]

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

[@Sonni-mx9ok]

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

[@KrabNaGalerah]

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

[@ЕвгенийПодольский-р1ы]

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

[@KrabNaGalerah]

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

[@ЕвгенийПодольский-р1ы]

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

[@ЕвгенийПодольский-р1ы]

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

[@s1lentgrave]

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

[@s1lentgrave]

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

[@KrabNaGalerah]

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

[@s1lentgrave]

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

[@СергейБобышкин-к5х]

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

[@KrabNaGalerah]

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

[@ISparkl]

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

[@KrabNaGalerah]

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

[@krule4ka1]

Кто знает как 3 задание решить без добавления доп столбцов. Сразу в верхнюю табличку подтянуть суммы?

[@dmitryking]

UPD: Спасибо за вопрос; через несколько месяцев вернулся к задачке "Решить задание 3 в одну формулу без доп.столбцов", докрутил её: Шаг 1 - избежать доп.столбец в верхней таблице можно через сцепку наименования округа в столбце "А" и слов "федеральный округ". =СЦЕПИТЬ(A5;" федеральный округ"); или - то же самое через амперсанд (&) Шаг 2 - избежать доп.столбец в нижней таблице; возможно, работает только в Office365 / Excel2021 и выше, т.к.сделано через динамические массивы: Формула для ячейки B5 (остальные - протянуть): =СУММПРОИЗВ(--($F$17:$F$73=A5&" федеральный округ");ВПР($E$17:$E$73;$A$18:$B$74;2;0))

[@СветланаВолкова-к3д]

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

[@Igoryan93reg]

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

[@KrabNaGalerah]

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

[@Igoryan93reg]

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

[@KrabNaGalerah]

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

[@Igoryan93reg]

Спасибо 🤝

[@ДавронРажапов-х6ц]

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

[@user-cg3wf6ve8ssi]

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

[@Mordvin-g6j]

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

[@Paul_Bibik]

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

[@hodya84]

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

[@KrabNaGalerah]

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

[@glebkuvaldin3146]

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

[@KrabNaGalerah]

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

[@atlantam9343]

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

[@vlad050987]

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