Составьте краткое изложение этой истории. Вот краткое изложение содержания истории, основанное на фактах: Вот упрощенная версия содержания истории: Вот беззаботный взгляд на содержание истории: Исследуйте другую точку зрения: Покажите мне факты Объясните это так, будто мне 5 лет Дайте мне беззаботный пересказ
Microsoft Excel имеет сотни инструментов, которые вы можете использовать, чтобы заставить ваши электронные таблицы работать. Однако, если вы хотите повысить свой уровень знаний, ознакомьтесь с этими шестью основными функциями сегодня.
Это краткий обзор некоторых обязательных инструментов Microsoft Excel. Чтобы узнать больше о каждой функции, щелкните ссылки «Связанные» в каждом разделе.
6 таблиц Excel для организации данных
Рабочий лист Microsoft Excel по сути представляет собой большую таблицу, организованную в столбцы и строки, где каждое пересечение столбцов и строк образует ячейку, в которую можно вводить данные или формулы. Однако внутри этой более крупной структуры можно создавать мини-наборы данных, которые работают независимо.
Вот тут-то и вступают в игру таблицы Excel. Они имеют встроенные возможности фильтрации и сортировки, расширяются новыми данными, дублируют формулы по всему столбцу и позволяют быстро добавлять итоговую строку для мгновенного анализа данных. Более того, вы можете использовать заголовки столбцов таблицы в формулах — также известные как структурированные ссылки — а также выбирать из различных вариантов форматирования таблиц.
Если вы начинаете с чистого листа, нажмите «Таблица» на вкладке «Вставка» на ленте.
Затем нажмите «ОК» в диалоговом окне «Создать таблицу».
В только что созданной таблице переименуйте первый столбец и начните вводить данные.
Как только вы введете первую пустую ячейку под таблицей и нажмете Enter, таблица автоматически захватит эту ячейку и включит ее в набор данных. Аналогично, добавление значений в первый пустой столбец рядом с таблицей расширяет форматирование вправо.
Если у вас уже есть набор данных и вы хотите преобразовать его в форматированную таблицу, выберите любую ячейку в диапазоне и на вкладке «Главная» на ленте нажмите «Форматировать как таблицу» и выберите стиль.
Далее, если у вашей таблицы есть заголовки, отметьте этот параметр в диалоговом окне «Создать таблицу». Если нет, оставьте этот параметр неотмеченным, и Excel добавит строку заголовка за вас.
Когда вы нажимаете «ОК», ваши данные мгновенно преобразуются в аккуратно представленную таблицу.
Если ваш набор данных содержит формулы, которые ссылаются на другие столбцы в диапазоне, вам нужно будет воссоздать их с помощью структурированных ссылок после того, как вы отформатируете данные как таблицу Excel. Другими словами, если формула читается как =SUM(A2+B2), удалите A2 и B2 и замените эти прямые ссылки ссылками на столбцы, щелкнув соответствующие ячейки.
Независимо от того, начинаете ли вы с нуля или преобразуете уже существующий набор данных в форматированную таблицу, важным шагом является присвоение имени вашей таблице. Это означает, что на нее можно ссылаться в формулах за пределами таблицы, и вы можете перейти к ней в любой момент через поле имени в верхнем левом углу окна Excel.
Для этого выберите любую ячейку в таблице и на вкладке «Конструктор таблицы» перейдите в группу «Свойства» и введите новое имя.
Вкладка «Конструктор таблиц» — это место, где вы можете изменить внешний вид и поведение вашей таблицы. Вы можете настроить общую компоновку таблицы в группе «Стили таблиц», а также можете выбрать, включать ли такие элементы, как итоговая строка или полосы, в группе «Параметры стиля таблиц». Более того, вы можете добавлять срезы в свою таблицу, чтобы упростить фильтрацию данных.
Если вы никогда раньше не пользовались таблицами Excel, вы будете удивлены, насколько они упрощают управление данными и их организацию.
5 функций поиска для перекрестных ссылок
Я, вероятно, использую функции поиска в своих таблицах Excel чаще, чем любой другой тип функций. Они являются мощным и эффективным способом поиска значения в наборе данных и возврата соответствующего результата.
Microsoft перечисляет 36 функций поиска и ссылок, но XLOOKUP и INDEX с MATCH являются наиболее полезными для ежедневного обращения к данным. Позвольте мне показать вам несколько примеров.
Пример 1: Возврат значения в одномерном поиске
В этом простом примере цель состоит в том, чтобы использовать эти функции для возврата общего счета игрока, имя которого введено в ячейку F2.
Для этого в ячейке G2 с помощью функции XLOOKUP необходимо ввести:
=XLOOKUP($F$2,Очки[Имя],Очки[Всего])
в котором
- $ F $ 2 ячейка, содержащая искомое значение (в данном случае Том),
- Очки[Имя] сообщает Excel, что нужно искать это значение в столбце «Имя» таблицы с именем «Результаты» и
- Очки[Всего] возвращает соответствующее значение из столбца «Итого» таблицы «Оценки».
При нажатии Enter поиск правильно возвращает общий балл 9.
Чтобы получить тот же результат с помощью функций ИНДЕКС и ПОИСКПОЗ, в ячейке G3 необходимо ввести:
=ИНДЕКС(Очки[[День 1]:[Всего]],ПОИСКПОЗ($F$2,Очки[Имя],0),3)
в котором
- Очки[[День 1]:[Всего]] — это массив, содержащий значение, которое вы хотите вернуть,
- СОВПАДЕНИЕ($F$2,Очки[Имя],0 берет значение в F2 и возвращает номер строки соответствующего значения в столбце Имя таблицы Очки, и
- 3 возвращает значение в массиве, которое находится в третьем столбце этой строки.
Обратите внимание, что при сравнении этих примеров формула XLOOKUP выглядит гораздо более простой. XLOOKUP была введена в Excel позже, чем ИНДЕКС и ПОИСКПОЗ, поэтому это более совершенный способ выполнить эту одномерную задачу поиска.
Пример 2: Возврат значения в двумерном поиске
XLOOKUP и INDEX с MATCH также могут выполнять двусторонний поиск. В этом примере в ячейках H6 и H7 вы хотите использовать эти функции для возврата счета игрока в определенный день в зависимости от имени в ячейке F6 (в данном случае Майк) и дня в ячейке G6 (в данном случае день 2).
Чтобы сделать это в ячейке H6 с помощью XLOOKUP, необходимо ввести:
=XLOOKUP($G$6,Очки[[#Заголовки],[День 1]:[Всего]],XLOOKUP($F$6,Очки[Имя],Очки[[День 1]:[Всего]]))
где первый XLOOKUP ищет значение в ячейке G6 по заголовкам столбцов, а второй XLOOKUP ищет значение в ячейке F6 в столбце Имя. Затем он возвращает результат из массива.
Используя ИНДЕКС и ПОИСКПОЗ, в ячейке H7 введите:
=ИНДЕКС(Очки,ПОИСКПОЗ($F$6,Очки[Имя],0),ПОИСКПОЗ($G$6,Очки[#Заголовки],0))
где часть формулы ИНДЕКС определяет массив, содержащий возвращаемое значение, первая функция ПОИСКПОЗ ищет значение в ячейке F6 в столбце Имя, а вторая функция ПОИСКПОЗ ищет значение в ячейке G6 по заголовкам столбцов.
В этом случае формула при использовании комбинации INDEX-MATCH-MATCH короче, чем формула при использовании XLOOKUP-XLOOKUP, поэтому я бы предпочел использовать первую для двумерного поиска.
Пример 3: Использование функций поиска в таблице
В приведенных выше примерах поиск выполнялся вне основных таблиц данных. Однако вы также можете использовать эти функции в отформатированной таблице Excel.
В примере 3 ваша цель — вернуть оценки студентов на основе их баллов.
Чтобы сделать это с помощью XLOOKUP, в ячейке C10 введите:
=XLOOKUP([@Score],$G$10:$G$14,$H$10:$H$14,»FAIL»,-1)
в котором
- [@Счет] берет баллы каждого студента,
- $G$10:$G$14 сообщает Excel, что нужно искать каждый результат в этих ячейках,
- $H$10:$H$14 — это возвращаемое значение (в данном случае оценка) в активной ячейке,
- «НЕУДАЧА» что следует вернуть, если искомое значение не может быть найдено, и
- -1 сообщает Excel, что нужно искать точные совпадения или возвращать следующий наименьший элемент.
При нажатии Enter формула автоматически дублируется в оставшиеся ячейки столбца.
Чтобы сделать это с помощью ИНДЕКС и ПОИСКПОЗ, в ячейке C14 введите:
=INDEX($G$10:$H$14,MATCH([@Score],$G$10:$G$14,1),2)
в котором
- $G$10:$H$14 — это диапазон, в котором находятся искомые значения,
- СОВПАДЕНИЕ([@Score],$G$10:$G$14 сопоставляет оценку в столбце «Оценка» с оценками в таблице соответствия,
- 1 находит наибольшее значение, которое меньше или равно искомому значению, и
- 2 сообщает Excel, что результат следует искать во втором столбце.
Затем нажмите Enter, чтобы сохранить формулу и применить ее к другим ячейкам в этом столбце.
И XLOOKUP, и INDEX с MATCH требуют пять аргументов, и их формулы имеют схожую длину. Однако, поскольку XLOOKUP позволяет указать, что возвращать, если значение не может быть найдено, это мой выбор в таких сценариях.
4 Условное форматирование для мгновенной визуализации данных
Вы можете знать, как использовать все сложные формулы Excel в книге, но это бесполезно, если цифры, которые они выдают, нелегко читать. Вот почему условное форматирование — это важный инструмент для использования в каждой электронной таблице. Оно делает то, что следует из его названия — форматирует ячейки на основе определенных условий.
Представьте, что вы владелец продуктового магазина и хотите наглядно представить, сколько денег вы заработали на каждом фрукте.
Да, вы можете видеть цифры в столбце E, но их сейчас не так-то просто интерпретировать. Чтобы исправить это, выберите данные в столбце, нажав на черную стрелку, которая появляется при наведении курсора на заголовок столбца, и на вкладке «Главная» на ленте щелкните раскрывающееся меню «Условное форматирование», наведите курсор на «Цветовые шкалы» и выберите вариант «зеленый-красный».
Теперь вы можете мгновенно увидеть, какие цифры выше, а какие ниже.
Вы также можете использовать условное форматирование, чтобы задать более конкретные правила. Например, вместо того, чтобы раскрашивать столбец E с помощью цветовых шкал, вы хотите выделить всю строку любого фрукта, который принес более 500 долларов. Для этого выберите самую верхнюю левую ячейку под заголовками столбцов и нажмите Ctrl+A, чтобы выбрать весь диапазон. Затем нажмите Условное форматирование > Новое правило.
Теперь в диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу для определения ячеек для форматирования» и в текстовом поле введите:
=$E2>500
При этом берутся все ячейки в столбце E, начиная с ячейки E2, и оцениваются их значения, чтобы определить, превышают ли они 500.
Наконец, нажмите «Формат», чтобы выбрать стиль ячейки, который будет применен при выполнении вышеуказанного критерия. В этом случае я собираюсь использовать сиреневую заливку ячейки.
Нажмите «ОК», чтобы закрыть диалоговые окна и завершить процесс.
Поскольку на первом шаге вы выбрали все столбцы, теперь выделена вся строка для каждого соответствующего значения.
Условное форматирование можно использовать для применения форматов в различных контекстах, включая форматирование определенных дат или применение форматирования, если значение ячейки выше или ниже среднего.
3. Проверка данных на согласованность и предотвращение ошибок
Планируете ли вы поделиться своей таблицей с другими или сохранить ее для собственного использования, проверка данных — отличный инструмент для обеспечения того, чтобы ячейки содержали ожидаемые вами значения или типы значений. В результате она обеспечивает согласованность, помогает предотвратить опечатки и экономит время.
Сначала выберите ячейку или ячейки, к которым вы хотите применить правило проверки данных, и на вкладке «Данные» на ленте нажмите «Проверка данных». Если вы видите раскрывающееся меню, нажмите «Проверка данных» еще раз.
По умолчанию в ячейку Excel можно ввести любое значение. Однако, если развернуть раскрывающееся меню «Разрешить» диалогового окна «Проверка данных», можно увидеть типы ограничений, которые можно применить. Например, если выбрать «Целое число», «Десятичное число», «Дата», «Время» или «Длина текста», вам будет предоставлена возможность указать параметры для этих типов данных.
С другой стороны, если вы выберете «Список», вы можете либо ввести список допустимых значений (разделенных запятыми) в поле «Источник», либо ссылаться на ячейки, содержащие допустимые значения.
После нажатия кнопки «ОК» при выборе соответствующей ячейки появляется раскрывающаяся кнопка, нажав на которую, можно открыть параметры ввода.
Наконец, диалоговое окно «Проверка данных» содержит еще две вкладки, где можно дополнительно уточнить чистоту проверки выбранных ячеек:
- Вводное сообщение: Эта вкладка позволяет вам создать сообщение, которое появляется при выборе ячейки. Это хороший способ напомнить тому, кто вводит данные, что в этой ячейке разрешены только определенные значения.
- Предупреждение об ошибке: На этой вкладке вы можете создать сообщение, которое будет появляться при вводе в ячейку неверного типа данных.
2 Power Query для манипулирования данными
Power Query — недооцененный инструмент, который может и должен использовать каждый — от новичков Excel до опытных пользователей. Он позволяет импортировать и подключать наборы данных из различных источников, включая Интернет, PDF-файлы и отдельные рабочие листы в рабочей книге, а также манипулировать данными в соответствии с вашими потребностями.
Например, вы можете разбить ячейки на две или более новых ячеек, заменить ошибки более значимыми значениями, отменить свертывание данных для облегчения анализа, заполнить пустые ячейки на основе ячейки выше и многое другое. Вы также можете складывать таблицы с одинаковыми заголовками столбцов из разных рабочих листов.
Чтобы преобразовать существующие данные на листе, выберите любую ячейку в нужном диапазоне и на вкладке «Данные» на ленте нажмите «Из таблицы/диапазона».
Затем откроется редактор Power Query, где вы сможете начать работу с данными. Вкладка Transform — это место, где происходит большая часть магии, так что это хорошее место для начала. Щелчок правой кнопкой мыши по заголовкам столбцов также дает вам доступ ко многим инструментам, специфичным для столбцов.
Закончив редактирование данных, нажмите кнопку «Закрыть и загрузить» на вкладке «Главная» на ленте, чтобы отправить набор данных на новый лист в вашей рабочей книге.
Чтобы импортировать таблицу с веб-сайта, вернитесь на вкладку «Данные» на главной ленте Excel, нажмите «Из Интернета» и вставьте URL-адрес в текстовое поле появившегося окна.
При нажатии кнопки «ОК» появляется диалоговое окно «Навигатор», в котором можно выбрать таблицу, которую требуется импортировать и редактировать, а затем нажать кнопку «Преобразовать данные», чтобы открыть редактор Power Query.
1 Подстановочные знаки для частичных совпадений
Подстановочные знаки в Excel представляют неизвестные или неспецифические символы в поиске, фильтрах и формулах.
Существует два типа подстановочных знаков, которые можно использовать для определения значений: нечеткий Спички:
- Звездочка (*): Это представляет любое количество символов (включая отсутствие символов). Например, «OK*» вернет совпадение для «Oklahoma», но не для «look».
- Вопросительный знак (?): Это представляет один символ. Например, «?OK» вернет совпадение для «wok», но не для «Joke».
- Смешанные джокеры: Вы также можете объединить звездочку и вопросительный знак, чтобы еще больше уточнить свой поиск. Например, «*OK?» вернет совпадение для «Books», но не для «Took».
Если вы хотите искать вопросительные знаки или звездочки как самостоятельные символы, поместите тильду (~) перед любым из этих символов, чтобы отменить их функцию подстановочных знаков.
Подстановочные знаки чаще всего используются при поиске определенных значений в Excel. В этом примере после нажатия Ctrl+F для открытия вкладки «Найти» диалогового окна «Найти и заменить» введите *2??А возвращает два результата из набора данных: UK255A и CAN267A.
Используя тот же набор данных, вы можете использовать подстановочные знаки в функции СУММЕСЛИ для сложения цен на товары, поставляемые из Великобритании:
=СУММЕСЛИ(A2:A18;»UK*;B2:B18)
Подстановочные знаки и текстовые значения в формулах Excel всегда должны быть заключены в кавычки.
Наконец, после нажатия Ctrl+Shift+L для добавления кнопок фильтра в набор данных, щелкните кнопку фильтра в верхней части столбца A и введите:
*A
в текстовое поле возвращает все коды продуктов, заканчивающиеся на «A».
Как только вы освоите эти основные инструменты, проверьте свои знания, пройдя тесты How-To Geek на знание Microsoft Excel начального, среднего и продвинутого уровней.
