19 простых советов, приемов и ярлыков Excel

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

Нужно объединить два листа с похожими данными? Эксель может это сделать.

Нужно сделать простую математику? Эксель может это сделать.

Нужно объединить информацию в нескольких ячейках? Эксель может это сделать.

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

Что такое Эксель?

Microsoft Excel — это мощное программное обеспечение для визуализации и анализа данных, которое использует электронные таблицы для хранения, организации и отслеживания наборов данных с помощью формул и функций. Excel используют маркетологи, бухгалтеры, аналитики данных и другие специалисты. Это часть набора продуктов Microsoft Office. Альтернативы включают Google Sheets и Numbers.

Найдите другие альтернативы Excel здесь.

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

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

Вы также можете скачать шаблоны Excel ниже для всех ваших маркетинговых нужд.

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

Основы Excel

Если вы только начинаете работать с Excel, мы предлагаем вам ознакомиться с несколькими основными командами. Это такие вещи, как:

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

Давайте рассмотрим некоторые из них более подробно.

Например, почему автозаполнение имеет значение?

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

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

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

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

Нажмите на данные в столбце, который вы хотите отсортировать. Затем нажмите на вкладку «Данные» на панели инструментов и найдите параметр «Сортировка» слева. Если «A» находится поверх «Z», вы можете просто нажать на эту кнопку один раз. Если «Z» находится поверх «A», дважды нажмите кнопку. Когда «A» находится над «Z», это означает, что ваш список будет отсортирован в алфавитном порядке. Однако, когда «Z» находится над «A», это означает, что ваш список будет отсортирован в обратном алфавитном порядке.

Далее давайте изучим основы Excel (наряду с расширенными функциями).

Как использовать Эксель

Чтобы использовать Excel, вам нужно только ввести данные в строки и столбцы. А затем вы будете использовать формулы и функции, чтобы превратить эти данные в идеи.

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

Документы, которые вы можете создать в Excel

Не знаете, как вы можете использовать Excel в своей команде? Вот список документов, которые вы можете создать:

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

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

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

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

Формулы Excel

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

• Знак равенства. Перед созданием какой-либо формулы вам необходимо написать знак равенства (=) в ячейке, в которой должен отображаться результат.
• Сложение : Чтобы добавить значения двух или более ячеек, используйте знак + . Пример: =C5+D3 .
• Вычитание : Чтобы вычесть значения двух или более ячеек, используйте знак – . Пример: =C5-D3 .
• Умножение : чтобы умножить значения двух или более ячеек, используйте знак * . Пример: =C5*D3 .
• Разделение : Чтобы разделить значения двух или более ячеек, используйте знак / . Пример: =C5/D3 .

Собрав все это вместе, вы можете создать формулу, которая складывает, вычитает, умножает и делит все в одной ячейке. Пример: =(C5-D3)/((A5+B6)*3) .

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

Функции Excel

Функции Excel автоматизируют некоторые задачи, которые вы использовали бы в обычной формуле. Например, вместо использования знака + для суммирования диапазона ячеек вы должны использовать функцию СУММ. Давайте рассмотрим еще несколько функций, которые помогут автоматизировать расчеты и задачи.

• СУММ: Функция СУММ автоматически суммирует диапазон ячеек или чисел. Чтобы завершить сумму, вы должны ввести начальную ячейку и конечную ячейку с двоеточием между ними. Вот как это выглядит: SUM(Cell1:Cell2) . Пример: =СУММ(C5:C30) .
• СРЗНАЧ: функция СРЗНАЧ усредняет значения диапазона ячеек. Синтаксис аналогичен функции СУММ: СРЗНАЧ(Ячейка1:Ячейка2). Пример: =СРЗНАЧ(C5:C30) .
• ЕСЛИ: Функция ЕСЛИ позволяет вам возвращать значения на основе логического теста. Синтаксис следующий: ЕСЛИ(логическая_проверка, значение_если_истина, [значение_если_ложь]) . Пример: =ЕСЛИ(A2>B2,«Превышение бюджета»,«ОК») .
• ВПР: функция ВПР помогает вам искать что-либо в строках вашего листа. Синтаксис: ВПР(значение поиска, массив таблиц, номер столбца, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)) . Пример: = ВПР([@Attorney],tbl_Attorneys,4,FALSE).
• ИНДЕКС: функция ИНДЕКС возвращает значение из диапазона. Синтаксис следующий: ИНДЕКС(массив, номер_строки, [номер_столбца]) .
• ПОИСКПОЗ: Функция ПОИСКПОЗ ищет определенный элемент в диапазоне ячеек и возвращает позицию этого элемента. Его можно использовать в тандеме с функцией ИНДЕКС. Синтаксис: ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления]) .
• СЧЁТЕСЛИ: функция СЧЁТЕСЛИ возвращает количество ячеек, которые соответствуют определённым критериям или имеют определённое значение. Синтаксис такой: СЧЁТЕСЛИ(диапазон, критерии). Пример: =СЧЁТЕСЛИ(A2:A5"Лондон").

Хорошо, готовы перейти к сути? Давайте приступим к делу. (И всем фанатам Гарри Поттера… добро пожаловать заранее.)

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

1. Используйте сводные таблицы для распознавания и осмысления данных.

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

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

Чтобы создать сводную таблицу, я иду в Данные > Сводная таблица . Если вы используете самую последнюю версию Excel, выберите « Вставка » > « Сводная таблица ». Excel автоматически заполнит сводную таблицу, но вы всегда можете изменить порядок данных. Затем у вас есть четыре варианта на выбор.

• Фильтр отчетов : позволяет просматривать только определенные строки в наборе данных. Например, если бы я хотел создать фильтр по факультетам, я мог бы включить только учеников Гриффиндора, а не всех учеников.
• Метки столбцов : это будут ваши заголовки в наборе данных.
• Метки строк: это могут быть ваши строки в наборе данных. Метки строк и столбцов могут содержать данные из ваших столбцов (например, имя можно перетащить на ярлык строки или столбца — все зависит от того, как вы хотите видеть данные).
• Значение : этот раздел позволяет взглянуть на ваши данные по-другому. Вместо того, чтобы просто вводить любое числовое значение, вы можете суммировать, подсчитывать, усреднять, макс, мин, подсчитывать числа или выполнять несколько других манипуляций с вашими данными. На самом деле, по умолчанию, когда вы перетаскиваете поле в значение, оно всегда подсчитывается.

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

2. Добавьте более одной строки или столбца.

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

Чтобы добавить несколько строк или столбцов в электронную таблицу, выделите то же количество ранее существовавших строк или столбцов, которое вы хотите добавить. Затем щелкните правой кнопкой мыши и выберите «Вставить».

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

3. Используйте фильтры для упрощения данных.

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

Тут на помощь приходят фильтры.

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

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

Допустим, в моем примере с Гарри Поттером я хочу видеть только учеников Гриффиндора. При выборе фильтра Гриффиндора остальные строки исчезают.

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

4. Удалите повторяющиеся точки данных или наборы.

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

Чтобы удалить дубликаты, выделите строку или столбец, из которых вы хотите удалить дубликаты. Затем перейдите на вкладку «Данные» и выберите «Удалить дубликаты» (находится под подзаголовком «Инструменты» в старой версии Excel). Появится всплывающее окно для подтверждения того, с какими данными вы хотите работать. Выберите «Удалить дубликаты», и все готово.

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

5. Транспонируйте строки в столбцы.

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

Начните с выделения столбца, который вы хотите транспонировать в строки. Щелкните его правой кнопкой мыши и выберите «Копировать». Затем выберите ячейки в электронной таблице, где вы хотите, чтобы ваша первая строка или столбец начинались. Щелкните правой кнопкой мыши ячейку и выберите «Специальная вставка». Появится модуль — внизу вы увидите опцию транспонирования. Установите этот флажок и выберите ОК. Теперь ваш столбец будет перенесен в строку или наоборот.

В более новых версиях Excel вместо всплывающего окна появится раскрывающийся список.

6. Разделите текстовую информацию между столбцами.

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

Благодаря Excel возможно и то, и другое. Сначала выделите столбец, который вы хотите разделить. Затем перейдите на вкладку «Данные» и выберите «Текст в столбцы». Появится модуль с дополнительной информацией.

Во-первых, вам нужно выбрать «С разделителями» или «Фиксированная ширина».

• «С разделителями» означает, что вы хотите разбить столбец на основе таких символов, как запятые, пробелы или табуляции.
• «Фиксированная ширина» означает, что вы хотите выбрать точное местоположение во всех столбцах, которые вы хотите разделить.

В приведенном ниже примере давайте выберем «С разделителями», чтобы мы могли разделить полное имя на имя и фамилию.

Затем пришло время выбрать разделители. Это может быть табуляция, точка с запятой, запятая, пробел или что-то еще. («Что-то еще» может быть, например, знаком «@», используемым в адресе электронной почты.) В нашем примере давайте выберем пробел. Затем Excel покажет вам, как будут выглядеть ваши новые столбцы.

Когда вы довольны предварительным просмотром, нажмите «Далее». Эта страница позволит вам выбрать расширенные форматы, если вы захотите. Когда закончите, нажмите «Готово».

7. Используйте формулы для простых вычислений.

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

• Чтобы добавить, используйте знак +.
• Чтобы вычесть, используйте знак –.
• Чтобы умножить, используйте знак *.
• Чтобы разделить, используйте знак /.

Вы также можете использовать круглые скобки, чтобы убедиться, что определенные вычисления выполняются в первую очередь. В приведенном ниже примере (10+10*10) второе и третье 10 были перемножены вместе перед добавлением дополнительных 10. Однако, если бы мы сделали это (10+10)*10, первое и второе 10 были бы добавлены вместе первыми. .

8. Получите среднее число в ваших ячейках.

Если вам нужно среднее значение набора чисел, вы можете использовать формулу =СРЗНАЧ(Ячейка1:Ячейка2) . Если вы хотите суммировать столбец чисел, вы можете использовать формулу =СУММ(Ячейка1:Ячейка2) .

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

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

Для начала выделите группу ячеек, для которых вы хотите использовать условное форматирование. Затем выберите «Условное форматирование» в главном меню и выберите свою логику в раскрывающемся списке. (Вы также можете создать свое собственное правило, если хотите чего-то другого.) Появится окно, в котором вам будет предложено предоставить дополнительную информацию о вашем правиле форматирования. Когда вы закончите, нажмите «ОК», и вы должны увидеть, что ваши результаты появятся автоматически.

10. Используйте формулу IF Excel для автоматизации определенных функций Excel.

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

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

Формула: ЕСЛИ(логическая_проверка, значение_если_истина, [значение_если_ложь])

Пример показан ниже: =IF(D2="Гриффиндор", "10", "0")

В общих чертах формула будет следующей: ЕСЛИ (логический тест, значение «истина», значение «ложь»). Давайте углубимся в каждую из этих переменных.

• Logical_Test : Логическая проверка — это часть оператора «ЕСЛИ». В этом случае логика такова: D2 = «Гриффиндор», потому что мы хотим убедиться, что в ячейке, соответствующей учащемуся, написано «Гриффиндор». Не забудьте взять Гриффиндор здесь в кавычки.
• Value_if_True : это то, что мы хотим, чтобы ячейка показывала, истинно ли значение. В этом случае мы хотим, чтобы в ячейке отображалось «10», чтобы указать, что учащийся получил 10 баллов. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.
• Value_if_False : это то, что мы хотим, чтобы ячейка показывала, если значение ложно. В этом случае для любого ученика не из Гриффиндора мы хотим, чтобы в ячейке отображалось «0». Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.

Примечание . В приведенном выше примере я присудил 10 баллов каждому на Гриффиндоре. Если бы я позже захотел суммировать общее количество баллов, я бы не смог, потому что 10-е в кавычках, что делает их текстом, а не числом, которое может суммировать Excel.

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

Диапазоны — это один из способов сегментации данных для лучшего анализа. Например, вы можете классифицировать данные по значениям меньше 10, от 11 до 50 или от 51 до 100. Вот как это выглядит на практике:

=ЕСЛИ(B3<11,"10 или меньше",ЕСЛИ(B3<51,"от 11 до 50",ЕСЛИ(B3<100,"от 51 до 100")))

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

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

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

Видите ли, ссылка на ячейку — например, когда вы ссылаетесь на ячейку A5 из ячейки C5 — по умолчанию является относительной. В этом случае вы фактически имеете в виду ячейку, которая находится на пять столбцов левее (C минус A) и находится в той же строке (5). Это называется относительной формулой. Когда вы копируете относительную формулу из одной ячейки в другую, она корректирует значения в формуле в зависимости от того, куда она была перемещена. Но иногда мы хотим, чтобы эти значения оставались неизменными независимо от того, перемещаются они или нет, и мы можем сделать это, превратив формулу в абсолютную формулу.

Чтобы преобразовать относительную формулу (=A5+C5) в абсолютную формулу, мы должны поставить перед значениями строки и столбца знаки доллара, например: (=$A$5+$C$5) . (Узнайте больше на странице поддержки Microsoft Office здесь.)

12. Используйте функцию ВПР для переноса данных из одной области листа в другую.

У вас когда-нибудь было два набора данных в двух разных электронных таблицах, которые вы хотели объединить в одну?

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

Мне часто приходится комбинировать такие наборы данных — и когда я это делаю, ВПР — моя формула.

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

Формула: =ВПР(искомое значение, массив таблиц, номер столбца, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ))

Формула с переменными из нашего примера ниже: =ВПР(C2,Лист2!A:B,2,ЛОЖЬ)

В этой формуле несколько переменных. Следующее верно, когда вы хотите объединить информацию на Листе 1 и Листе 2 на Листе 1.

• Искомое значение : это идентичное значение, которое есть в обеих таблицах. Выберите первое значение в вашей первой электронной таблице. В следующем примере это означает первый адрес электронной почты в списке или ячейку 2 (C2).
• Массив таблиц: массив таблиц — это диапазон столбцов на листе 2, из которого вы собираетесь извлекать данные, включая столбец данных, идентичный вашему значению поиска (в нашем примере, адреса электронной почты) на листе 1, а также столбец данных, которые вы пытаетесь скопировать на Лист 1. В нашем примере это «Лист2!А:Б». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему значению поиска (электронной почте) на листе 1. «B» означает столбец B, который содержит информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
• Номер столбца : это сообщает Excel, в каком столбце находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец, в котором находится «Дом». «Дом» — второй столбец в нашем диапазоне. столбцы (массив таблиц), поэтому наш номер столбца равен 2. [ Примечание : ваш диапазон может состоять из более чем двух столбцов. Например, если на листе 2 есть три столбца — «Электронная почта», «Возраст» и «Дом» — и вы все еще хотите перенести «Дом» на лист 1, вы все равно можете использовать ВПР. Вам просто нужно изменить «2» на «3», чтобы получить значение в третьем столбце: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ) : Используйте значение ЛОЖЬ, чтобы обеспечить получение только точных совпадений значений. Если вы используете TRUE, функция выберет приблизительные совпадения.

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

Поэтому, когда мы вводим формулу =VLOOKUP(C2,Sheet2!A:B,2,FALSE) , мы переносим все данные о доме на лист 1.

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

13. Используйте формулы ИНДЕКС и ПОИСКПОЗ для извлечения данных из горизонтальных столбцов.

Как и функция ВПР, функции ИНДЕКС и ПОИСКПОЗ извлекают данные из другого набора данных в одно центральное место. Вот основные отличия:

• ВПР — гораздо более простая формула. Если вы работаете с большими наборами данных, которые потребуют тысяч поисковых запросов, использование функций ИНДЕКС и ПОИСКПОЗ значительно сократит время загрузки в Excel.
• Формулы ИНДЕКС и ПОИСКПОЗ работают справа налево, тогда как формулы ВПР работают только как поиск слева направо. Другими словами, если вам нужно выполнить поиск, в котором столбец поиска находится справа от столбца результатов, вам придется переупорядочить эти столбцы, чтобы выполнить ВПР. Это может быть утомительно с большими наборами данных и/или привести к ошибкам.

Итак, если я хочу объединить информацию на листе 1 и листе 2 на листе 1, но значения столбцов на листах 1 и 2 не совпадают, то для выполнения ВПР мне нужно будет переключаться между столбцами. В этом случае я бы вместо этого выбрал ИНДЕКС и ПОИСКПОЗ.

Давайте посмотрим на пример. Допустим, Лист 1 содержит список имен людей и их адресов электронной почты в Хогвартсе, а Лист 2 содержит список адресов электронной почты людей и Патронуса, который есть у каждого ученика. (Для тех, кто не является поклонником Гарри Поттера, скажем, что у каждой ведьмы или волшебника есть животное-хранитель по имени «Патронус», связанное с ним или с ней.) Информация, которая находится на обоих листах, — это столбец, содержащий адреса электронной почты, но этот столбец адресов электронной почты находится в разных номерах столбцов на каждом листе. Я бы использовал формулы ИНДЕКС и ПОИСКПОЗ вместо ВПР, поэтому мне не пришлось бы переключать столбцы.

Так какая же тогда формула? Формула на самом деле является формулой ПОИСКПОЗ, вложенной в формулу ИНДЕКС. Вы увидите, что здесь я выделил формулу ПОИСКПОЗ другим цветом.

Формула: =ИНДЕКС(массив таблиц, формула ПОИСКПОЗ)

Это становится: = ИНДЕКС (массив таблиц, ПОИСКПОЗ ( искомое_значение, искомый_массив))

Формула с переменными из нашего примера ниже: =ИНДЕКС(Лист2!A:A,(ПОИСКПОЗ(Лист1!C:C,Лист2!C:C,0)))

Вот переменные:

• Массив таблиц : диапазон столбцов на Листе 2, содержащих новые данные, которые вы хотите перенести на Лист 1. В нашем примере «A» означает столбец A, который содержит информацию о «Патронусе» для каждого человека.
• Искомое значение : это столбец на листе 1, который содержит одинаковые значения в обеих электронных таблицах. В следующем примере это означает столбец «электронная почта» на листе 1, который является столбцом C. Итак: Sheet1!C:C.
• Массив поиска : это столбец на листе 2, который содержит одинаковые значения в обеих электронных таблицах. В следующем примере это относится к столбцу «электронная почта» на листе 2, который также является столбцом C. Итак: Sheet2!C:C.

После того, как вы определились со своими переменными, введите формулы ИНДЕКС и ПОИСКПОЗ в самую верхнюю ячейку пустого столбца Patronus на листе 1, где вы хотите, чтобы объединенная информация жила.

14. Используйте функцию СЧЁТЕСЛИ, чтобы Excel подсчитывал слова или числа в любом диапазоне ячеек.

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

Например, допустим, я хочу подсчитать, сколько раз слово «Гриффиндор» появляется в моем наборе данных.

Формула: =СЧЁТЕСЛИ(диапазон, критерии)

Формула с переменными из нашего примера ниже: =СЧЁТЕСЛИ(D:D,"Гриффиндор")

В этой формуле есть несколько переменных:

• Диапазон : диапазон, который мы хотим, чтобы формула покрывала. В этом случае, поскольку мы фокусируемся только на одном столбце, мы используем «D:D», чтобы указать, что первый и последний столбцы оба являются D. Если бы я смотрел на столбцы C и D, я бы использовал «C:D». ».
• Критерии : любое число или часть текста, которые вы хотите, чтобы Excel подсчитывал. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом. В нашем примере критерием является «Гриффиндор».

Просто введя формулу СЧЁТЕСЛИ в любой ячейке и нажав «Ввод», я покажу, сколько раз слово «Гриффиндор» встречается в наборе данных.

15. Объедините ячейки с помощью &.

Базы данных имеют тенденцию разбивать данные, чтобы сделать их как можно более точными. Например, вместо столбца, в котором отображается полное имя человека, база данных может иметь данные в виде имени, а затем фамилии в отдельных столбцах. Или в нем может быть местонахождение человека, разделенное городом, штатом и почтовым индексом. В Excel вы можете объединять ячейки с разными данными в одну ячейку, используя знак «&» в своей функции.

Формула с переменными из нашего примера ниже: =A2&» «&B2

Давайте вместе разберем формулу на примере. Представьте, что мы хотим объединить имена и фамилии в полные имена в одном столбце. Для этого мы сначала поместим курсор в пустую ячейку, где мы хотим, чтобы появилось полное имя. Затем мы выделяем одну ячейку, содержащую имя, вводим знак «&», а затем выделяем ячейку с соответствующей фамилией.

Но вы еще не закончили — если вы введете только =A2&B2, то между именем и фамилией человека не будет пробела. Чтобы добавить это необходимое пространство, используйте функцию =A2&», «&B2» . Кавычки вокруг пробела сообщают Excel, что между именем и фамилией нужно поставить пробел.

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

16. Добавьте флажки.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Вот как это сделать.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Рад, что вы спросили. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.