7 инструментов Microsoft Excel, которые упростят работу с данными: советы и рекомендации по Office

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

Следующие функции Excel можно использовать в новых или существующих базах данных для быстрого и простого поиска или обобщения информации. Вы можете использовать их, чтобы превратить стену чисел в значимые данные всего за несколько простых кликов.

Хотя мы предоставили здесь только 7 инструментов, они будут иметь огромное значение для ваших возможностей. Вы даже можете комбинировать большинство из них, чтобы еще больше усилить их влияние, и ваш босс и коллеги будут в восторге от ваших навыков.

Большинство из этих инструментов являются относительно базовыми, но некоторые из более поздних могут граничить со средними. Хотя, немного потренировавшись, любой может успешно их использовать и придать своим проектам Microsoft Office эффект «вау».

Итак, без лишних слов, давайте рассмотрим наш первый простой инструмент для работы с вашими данными.

Сумма

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

Давайте посмотрим на создание базовой формулы Sum:

= Сумма (значение1, значение2, значение3…)

Чтобы суммировать диапазон данных, вы заменяете значение в формуле ячейками. Например, чтобы добавить ячейки от A1 до A20, вы должны использовать «Сумма (A1: A20)». Дополнительные ячейки или диапазоны могут быть добавлены после запятой «= Сумма (A1: A20, B15, C2: C5)».

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

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

Считать

Самая простая функция Count делает именно то, что говорит. Он подсчитывает общее количество ячеек, выделенных в диапазоне. Однако он суммирует не общее содержимое ячеек, а только количество присутствующих ячеек. Мы используем следующую формулу:

=Счетчик(значение1,значение2,значение3…)

Внутри скобок вы можете либо перечислить отдельные значения/ячейки, либо указать диапазон ячеек, которые вы хотите рассмотреть. Диапазон может охватывать несколько строк и столбцов, например A1:A50 или A1:D50.

В приведенном выше примере показано общее количество транзакций, совершенных клиентами в небольшой базе данных.

Функция Count будет подсчитывать только ячейки, содержащие числа, поэтому, если ячейка пуста или содержит текст, она будет полностью игнорировать ячейку.

Если вы хотите подсчитывать ячейки более конкретным способом, например, ячейки, содержащие определенное число или определенную строку текста, читайте дальше, чтобы узнать о более мощном методе CountIf!

CountIf

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

=CountIf(диапазон,критерий)

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

Если бы мы хотели, скажем, подсчитать продажи только выше 100 долларов, вы бы просто установили критерий в формуле >100. Для этого формула будет «=CountIf(F1:F19, «>100»)». Критерии могут быть весьма универсальными; вы можете считать по числам в диапазоне, считать, если это определенный фрагмент текста, или считать, если он соответствует содержимому другой указанной ячейки.

СуммаЕсли

Подобно CountIf, мы можем использовать SumIf, чтобы рассматривать только те ячейки, которые соответствуют заданному набору критериев. Разница здесь в том, что Countif будет подсчитывать только количество ячеек, тогда как SumIf суммирует содержимое ячеек, если они соответствуют вашему набору рекомендаций.

= Сумма, если (диапазон, критерии, (сумма_диапазон))

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

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

Мин Макс

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

= Мин (значение1, значение2, значение3…)

или

=Макс.(значение1,значение2,значение3…)

Этот маленький инструмент может реально сэкономить время. Когда ваши данные изменяют ранги, Min и Max всегда будут оставаться актуальными, постоянно просматривая формулу и проверяя ее правильность.

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

Классифицировать

Вместо того, чтобы просто просматривать самые высокие и самые низкие значения с помощью функций Min/Max, вы также можете ранжировать выборку данных по их индивидуальным значениям. Это делается с помощью формулы ранга.

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

Давайте посмотрим на пример:

=РАНГ(число,ссылка,(порядок))

Вы выбираете число, которому хотите присвоить ранг, затем «ref» — это группа значений, с которыми оно сравнивается, за которым следует необязательное значение «order» — это может быть либо 0 для убывания, либо 1 для возрастания.

Приведенный выше пример показывает, кто из наших клиентов тратит больше/меньше всего на продажи. Вместо этого мы могли бы легко перейти к просмотру объема продаж, выбрав предыдущий столбец, в зависимости от наших потребностей в то время.

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

ВПР

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

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

Для этого используется формула:

=ВПР(искомое_значение,массив_таблиц,номер_столбца,(диапазон_просмотра)

Хотя на первый взгляд это кажется сложным, на самом деле все довольно просто. Давайте разберем это:

Искомое_значение — это ячейка, которую он считывает, прежде чем начать поиск. Это уникальная строка информации, которую вы вводите для запуска поиска. Например, вы можете ввести номер транзакции, чтобы найти конкретную продажу клиента.

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

Col_index_num — это столбец из приведенного выше Table_array, который предоставит вам данные для вывода. Поиск возьмет ваше lookup_value, найдет его в table_array, а затем предоставит данные, которые присутствуют в номере столбца, который мы ввели здесь.

Range_lookup — это необязательное поле. Здесь вы можете ввести либо ИСТИНА, либо ЛОЖЬ. Если TRUE, поиск будет искать приблизительное совпадение. Если FALSE, поиск требует точного совпадения. Если оставить пустым, по умолчанию всегда будет TRUE.

В примере формула ВПР в ячейке I4 считывает номер транзакции из ячейки H4. Он находит этот номер в таблице, затем просматривает столбец 2, который в данном случае является именем клиента. Затем он читает этот столбец и отправляет обратно имя Дэн.

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

Какими бы полезными они ни были, Vlookups имеют несколько ограничений. Во-первых, значение VLookup не распознает повторяющиеся значения. Если бы у нас было две транзакции с одинаковым идентификационным номером, функция нашла бы первое вхождение, а затем остановилась бы. Вот почему важно иметь уникальное значение — если бы мы использовали фамилии, например, и у нас было 2 Смита, ВПР всегда останавливался бы на первом Смите.

Во-вторых, Vlookups может только смотреть правильно. Это не ссылка на Zoolander, а особенность работы поиска. Он просматривает левый столбец для «Искомое_значение», а затем возвращает информацию из столбцов справа от него (как указано пользователем). Есть способ обойти это, например, функция XLOOKUP, но они немного более продвинуты.

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

Используя инструменты из этой статьи, а также советы, представленные в наших 10 лучших советах по Microsoft Excel, которые помогут вам стать волшебником электронных таблиц, вы сможете создавать сложные базы данных в кратчайшие сроки. Добавьте немного собственного стиля и таланта, и вы сможете легко создавать профессионально выглядящие страницы, чтобы удивить своих коллег и клиентов или даже просто произвести впечатление на себя.

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

Дополнительные ресурсы

Дополнительные полезные статьи, охват и ответы на распространенные вопросы о Windows 10 и Windows 11 см. на следующих ресурсах:

Лучшие на сегодня предложения Microsoft Office 365