5 челленджей, справиться с которыми поможет Excel

Вызубрили теорию, а она так и не пригодилась — с такой ситуацией регулярно сталкиваются все студенты. Но это не относится к выпускникам курса Changellenge >> ToolKit Plus, считает Полина Вериго, одна из его участниц. Спустя пару недель после прохождения курса Полина попала на стажировку в Mars и сразу столкнулась с несколькими челленджами. В статье она делится функциями Excel, которые помогли ей их решить.

Полина Вериго — студентка МГИМО МИД РФ, выпускница Школы Changellenge >> и онлайн-курса Changellenge >> ToolKit 2018.

Челлендж № 1: привести таблицу в порядок

Любой проект начинается с того, что в Excel нужно перенести данные. Но сразу после импорта работать с таблицами невозможно: с числами копируются лишние пробелы, точки, и программа не считывает информацию. Решить проблему помогут функции TRIM и SUBSTITUTE.

TRIM (СЖПРОБЕЛЫ) убирает все пробелы в текстовой строке кроме одиночных пробелов между строками. Синтаксис элементарен:

=TRIM(ссылка на ячейку, из которой нужно удалить ненужные пробелы)

SUBSTITITE (ПОДСТАВИТЬ) заменяет любой старый текст на новый. Работает эта функция так:

=SUBSTITUTE(ячейка;“старый текст“;“новый текст”)

Если ваш Excel с русскими настройками, то точку нужно заменить на запятую, чтобы программа распознала десятичный разделитель.

Для экономии времени две функции можно объединить в одну. Мы вводим в новую ячейку =TRIM(SUBSTITUTE(A1;”.”;”,”)) и растягиваем ее на весь столбец. Теперь перед нами аккуратные числа, готовые к дальнейшей работе.

Челлендж № 2: построить точечную диаграмму (scatter plot) на основе случайного массива данных

«Для презентации отдела нужно срочно создать точечный график. Данные рандомные, главное, в промежутке от 60 до 100. И чтобы их было больше 100. У тебя пара минут», — такое задание я получила от своего руководителя. Чтобы выполнить его, пришлось вспомнить функцию RAND (СЛЧИС). Она позволяет выполнить эту задачу за двадцать секунд и выглядит очень просто:

=RAND() (да, никаких аргументов).

Эта функция всегда возвращает случайное число, большее или равное 0, но меньше 1. Если нужно задать какой-либо диапазон, то формула выглядит так:

=RAND()*(верхняя граница-нижняя граница)+нижняя граница

В моем случае =RAND()*(100-60)+60. Протягиваем ее на 100+ строчек вниз и вставляем график.

Эту же задачу можно выполнить еще быстрее, воспользовавшись формулой RANDBETWEEN (СЛУЧМЕЖДУ):

=RANDBETWEEN(нижняя граница; верхняя граница)

В отличие от обычного RAND эта формула возвращает лишь целые числа, что не всегда подходит по условиям задачи.

Челлендж № 3: найти сумму чисел в столбце со скрытыми строками и ошибками

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

В этом случае можно использовать AGGREGATE (АГРЕГАТ). Она помогает применять любые функции, не боясь скрытых строк и ошибок. Ее синтаксис выглядит так:

=AGGREGATE([номер функции];[параметр (номер, который определяет, какие значения следует пропускать)];[массив, для которого надо вычислить значение];[второй массив, который требуется для определенных функций (необязательный)])

Чтобы узнать номер функции и параметр, при вводе AGGREGATE нажмите на ее название: на экране появится справка Excel со всей необходимой информацией.

Вернемся к задаче. Нам нужна сумма значений в столбце А, и мы хотим пропустить все скрытые строки и значения ошибок. Формула будет выглядеть так:

=AGGREGATE(9;7;A:А), где 9 – номер функции СУММ, а 7 – номер параметра «Пропуск скрытых строк и значений ошибок».

Челлендж № 4: спрогнозировать число пользователей, привлеченных на страницу, исходя из заданного линейного тренда

Передо мной стояла задача: спрогнозировать число пользователей, привлеченных на сайт компании в условиях сохранения текущих настроек рекламы. У меня были данные по количеству посетителей за последние 4 месяца, а требовалось найти их число на конец мая.

Здесь на помощь пришла функция FORECAST.LINEAR (ПРЕДСКАЗ.ЛИНЕЙН). Она предсказывает значение y по известному значению х. Синтаксис у нее такой:

=FORECAST.LINEAR([x (то значение, по которому делаем прогноз)]; [известные значения y]; [известные значения x])

В нашем кейсе х=5 (месяц май, на который нужно сделать прогноз), известные значения х — все предыдущие месяцы, по которым мы знаем количество привлеченных пользователей, а известные значения y — количество пользователей за каждый из прошлых месяцев. Вводим формулу:

=FORECAST.LINEAR(5;C2:C5;B2:B5) и получаем результат. Маховик времени в действии!

Челлендж № 5: найти и перенести в новую таблицу нужные данные из другой таблицы

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

Поэтому стоит познакомиться с функцией INDEX(MATCH) (ИНДЕКС(ПОИСКПОЗ)). Она состоит из двух отдельных функций:

• INDEX возвращает нужное значение по заданным параметрам поиска: массив, где искать, номер строки и номер столбца. Синтаксис у нее такой:

=INDEX([массив, где искать];[номер строки];[номер столбца])

• MATCH сообщает порядковый номер нужного нам значения в выбранном столбце или строке. Работает она так:

=MATCH([что искать (ссылка на ячейку или значение в кавычках)]; [где искать (строка или столбец, в котором ищем это значение)]; [как искать (0, 1 или -1)]

0 означает точное совпадение, 1 – поиск первого значения, которое больше искомого или равно ему (значения должны быть отсортированы по возрастанию), -1 – поиск первого значения, которое меньше искомого или равно ему (значения должны быть отсортированы по убыванию).

Эти функции хороши сами по себе. Однако если их совместить, получится максимально полезный и универсальный инструмент для поиска данных.

=INDEX(столбец в исходной таблице,  откуда нужно извлечь нужное значение); MATCH([другое значение в новой таблице, по которому мы ищем нужное нам значение];[столбец в исходной таблице, где находится это другое значение];0))

Разберем ее на примере. Нам даны имена продавцов и объем их продаж за три месяца. Предположим, нужно найти данные по работе только пяти продавцов: Б, Ж, К, О, С. В таблице может быть несколько тысяч строк, поэтому сделать все вручную не получится. Чтобы найти, сколько продавец Б продал в январе, вводим в соответствующую ячейку:

=INDEX(C3:C26;MATCH(H4;B3:B26;0)), где

• C3:C26 – столбец в исходной таблице со всеми продажами в январе;

• Н4 – ячейка в новой таблице, по которой мы ищем продажи, то есть продавец Б;

• В3:В26 – столбец в исходной таблице, в котором указаны все продавцы;

• 0 – параметр, означающий поиск точного значения.

Протягиваем формулу вниз и вправо и получаем готовую таблицу.

После стажировки, тесно связанной с работой в Excel, я уверена, что эти пять функций пригодятся вам в работе.