Эксель 2018 для начинающих формулы: Excel для чайников формулы

Содержание

Ячейка в Excel — базовые понятия




Перейти к содержанию





Search for:

Главная » Уроки MS Excel







Автор Антон Андронов На чтение 3 мин Опубликовано



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

Содержание

  1. Основные сведения о ячейках в Excel
  2. Выделение ячеек в Excel
  3. Выделение диапазона ячеек в Excel

Основные сведения о ячейках в Excel

Каждый лист в Excel состоит из тысяч прямоугольников, которые называют ячейками. Ячейка – это пересечение строки и столбца. Столбцы в Excel обозначают буквами (A, B, C), в то время как строки обозначают числами (1, 2, 3).

Исходя из строки и столбца, каждой ячейке в Excel присваивается имя, его также называют адрес. Например, C5 – это ячейка, которая находится на пересечении столбца С и строки 5. При выделении ячейки ее адрес отображается в поле Имя. Обратите внимание, что при выборе ячейки, заголовки строки и столбца, на пересечении которых она находится, становятся подсвеченными.

В Microsoft Office Excel имеется возможность выбирать сразу несколько ячеек. Набор из двух или более ячеек принято называть диапазоном. Любой диапазон, точно также, как и ячейка, имеет свой адрес. В большинстве случаев адрес диапазона состоит из адреса левой верхней и правой нижней ячеек, разделенных двоеточием. Такой диапазон называют смежным или непрерывным. Например, диапазон, который состоит из ячеек B1, B2, B3, B4 и B5, будет записан как B1:B5.

На рисунке ниже выделены два разных диапазона ячеек:

  • Диапазон A1:A8
  • Диапазон A1:B8

Если столбцы на листе обозначены числами вместо букв, необходимо изменить стиль ссылок в Excel, заданный по умолчанию. Для получения подробной информации обратитесь с уроку: Что такое стиль ссылок в Excel.

Выделение ячеек в Excel

Чтобы ввести данные или отредактировать содержимое ячейки, сначала ее нужно выделить.

  1. Щелкните по ячейке, чтобы выделить ее.
  2. Выбранная ячейка будет обозначена границами, а заголовки столбца и строки станут подсвеченными. Ячейка останется выделенной, пока Вы не выберите любую другую ячейку.

Вы также можете выделять ячейки при помощи клавиш со стрелками на клавиатуре (клавиши управления курсором).

Выделение диапазона ячеек в Excel

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

  1. Нажмите на первую ячейку диапазона и, не отпуская кнопку, переместите мышь пока все прилегающие ячейки, которые вы хотите выбрать, не будут выделены.
  2. Отпустите кнопку мыши, требуемый диапазон будет выбран. Ячейки останутся выделенными, пока вы не выберите любую другую ячейку.

Урок подготовлен для Вас командой сайта office-guru. ru
Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full
Автор/переводчик: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:






Adblock
detector

3D-движок, написанный на формулах MS Excel / Хабр

Скриншот экрана игры

Эта статья посвящена тому, как я смог написать 3D-движок только на формулах Excel. Я реализовал следующий функционал:

  • бесконечная процедурно генерируемая карта лабиринта

  • рендеринг трассировкой лучей в реальном времени

  • вычисление окклюзии

  • рендеринг простейшего освещения

  • шейдер освещения и вычислений

  • движок естественного движения

  • в 3D-движке не используются макросы


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

Можете скачать файл и протестировать его самостоятельно!

Файлы

  • Файл без vba: http://public.cbel.free.fr/?file=Doom.xlsx

  • Файл с vba для перехвата нажатий клавиш: http://public.cbel.free.fr/?file=Doom.xls-v1.1.xlsm

  • Файл с vba и высоким разрешением: http://public.cbel.free.fr/?file=Doom-HD.xls-v1.1.xlsm

  • Файл с vba и кнопками вместо горячих клавиш: http://public.cbel.free.fr/downloads/?file=Doom.xls-Buttons-v1.1.xlsm


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

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

Затем, после изучения машины Тьюринга, фраза стала для нас полностью верной, хотя и не вполне реализуемой.

Получив многолетний опыт работы с Excel, мы уже поняли, что единственное ограничение формулы Excel — недостаток способов ввода-вывода.

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

Как бы то ни было, эта работа — не просто какое-то хвастовство… У меня были для неё серьёзные причины.

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

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

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

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

При использовании в бизнесе формулы обладают следующими преимуществами перед макросами:

  • Их быстрее писать для любого человека, если он не профессиональный программист-аналитик

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

  • Гарантированное качество благодаря постоянной проверке значений. (Принудительное применение техники «разработка через тестирование»)

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

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


Примечание: эти пункты в основном относятся к процедурам, используемым как макросы; дополнительная функция, написанная на VBA, может увеличить эффективность, не снижая качества.

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

Если конкретнее, то я нашёл всего два случая, когда потребовался VBA:

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

  • Некоторые сложные задачи (например, оптимизация), в которых вычисления занимают слишком много времени и/или пространства. Но такие задачи довольно редки в реальной жизни.


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


Моя электронная таблица должна была стать игрой в стиле Doom в лабиринте.

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

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

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

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

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

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

Тогда я обнаружил метод средних квадратов (middle-square method), который на самом деле не очень «случаен», потому что в нём используются последовательные начальные состояния. Но он подсказал мне идею того, что можно брать десятичную часть любого другого вычисления.

Я выяснил, что если брать десятичные части sin(x)+cos(y), то наконец-то получаются красивые числа без какого-либо прослеживаемого паттерна, а время вычислений при этом на удивление малО.

Для получения десятичных частей математические функции mod() и floor() гораздо более эффективны по сравнению с текстовой функцией подстроки mid().

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

То есть нам нужны тонкие стены с двумя возможными стенами для каждого квадрата. Тогда мы сможем брать два блока чисел вместе с тем же случайным значением.

Плотностью размещения стен управляют два параметра.

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

Стоит заметить, что карта «плоская», без подъёмов и спусков. Можно было добавить рельеф с помощью генератора рельефа (подошёл бы алгоритм Diamond-Square, потому что его можно написать без рекурсивной функции), но весь последующий процесс сильно облегчило бы вырезание отверстий в полу и потолке с дополнительным значением уровня.

Так, похоже, мы в аду


Трассировщик лучей должен определять для каждого пикселя экрана, какой первой поверхности касается луч, и получать от неё информацию (расстояние, угол падения света, цвет и т.д.).

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

Окклюзия


Первым сложным моментом будет нахождение первого объекта на пути каждого луча.

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

То есть процесс можно упростить до горизонтального «радара» в одном измерении.

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

Определение того, какую стену нужно проверять — это всего лишь тригонометрическая задача.

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

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

Пол и потолок


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

Отдельный лист изменяет расстояние до пола или потолка в зависимости от вертикального угла.

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

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

Освещение


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

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

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

Для каждого угла радара мы получаем угол между лучом и ближайшей найденной стеной.

Коэффициент отражения — это просто функция угла.

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

Экран дисплея


Эффективный дисплей реализуется с помощью условного форматирования — градиент цвета зависит от значения ячейки.

Сокрытие значения выполняется форматированием ячейки.


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

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

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

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

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

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

Привет, красное привидение!

Графический рендеринг


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

Целый отдельный лист посвящён вычислению формы сфер с учётом горизонтального радиуса и вертикальной высоты овоида. Для анимирования существа используется соотношение высоты/ширины.

В рендеринге стен и потолка используется градиент только по одному цвету, однако Excel позволяет использовать два последовательных (не сливающихся) градиента. Поэтому врагов можно отображать другим цветом. Для этого градиента можно использовать диапазон значений ниже 0, при том, что значение 0 — это чёрный цвет.

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

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

Здесь вычисления тоже производятся в горизонтальной плоскости, а максимальный объём вычислений подготавливается перед завершением 3D-вычислений для каждого пикселя.

Поведение врагов


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

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

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

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

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

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

Атаки врагов


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

Область видимости каждого монстра вычисляется на вкладке радара с учётом расположения стен.

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

Это создаёт эффект наносимого игроку урона — простой эффект негативного цвета во весь экран.

Добавлен также ещё один эффект — отмена рендеринга всех остальных врагов при нанесении урона игроку.

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

Атаки игрока


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

Эта упрощённая модель позволяет игроку предотвратить атаку врага, снизив его жизнь до нуля.

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

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

Смерть и перезапуск


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

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

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

Сдвигание вниз — это простое смещение шейдера освещения, в который добавлены другие сообщения, например, рекорды (high score) и перезапуск игры.

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

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

Расширенные примеры формул и функций Excel

>> ЗАГРУЗИТЕ БЕСПЛАТНО ПОЛНОЕ РУКОВОДСТВО ПО ФОРМУЛАМ EXCEL!

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

Ниже вы найдете 101 Примеры формул и функций Excel для ключевых формул и функций, таких как ВПР, ИНДЕКС, ПОИСКПОЗ, ЕСЛИ, СУММПРОИЗВ, СРЗНАЧ, ПРОМЕЖУТОЧНЫЙ ИТОГ, СМЕЩЕНИЕ, ПРОСМОТР, ОКРУГЛ, СЧЁТ, СУММЕСЛИМН, МАССИВ, ПОИСК, ТЕКСТ и многие другие. Вы также найдете формулы Excel с примерами в таблице Excel бесплатно!

Посмотрите, как легко выучить лучшие формулы Excel, здесь:

Смотрите на YouTube и ставьте лайки!

Часть 1:

Загрузите учебный файл YouTube Excel

Часть 2:

Загрузите обучающий файл Excel с YouTube

 

Формулы VS Функции

Скорее всего, вы слышали, что оба слова — это формулы и функции — в Excel. В чем разница между ними?

A Формула — это выражение, которое вычисляет значение ячейки, а Функция — это предопределенная формула, доступная для использования в Excel. Ниже приведен пример обоих, но мы используем оба термина (формулы и функции) взаимозаменяемо:

👉 Нажмите здесь, чтобы присоединиться к нашему бесплатному вебинару по формулам и функциям, посвященному ВПР, ЕСЛИ, СУММЕСЛИ И ИНДЕКСУ/СОПОСТАВЛЕНИЮ, а также повышайте свой уровень Excel!

Дата и время
датиф. Поиск
Hlookup
Iferror, Vlookup
Index
Index, Match
Indirect
Match
Multiple Criteria, Vlookup
Offset
Pivot Table, GetPivotData
Sum, Lookup
Vlookup
Xlookup

  MATH
Average
Count
CountA
CountBlank
CountIf
CountIfs
Математические формулы
Mod
Проценты
Rand
Randbetween
Round
Subtotal
Sumifs
Суммарное произведение

  Другое
3D
Ассист
Диаграмма
Преобразование, значения
Оценка
Найти и выберите
FV
Справочник
Показ, скрыть
Transpect
Меж
Значение

.
Извлечь, найти, слева
Слева
Длина, длина
Правильно
Удалить
Удалить, заменить
Заменить
Заменить, очистить
Справа
Заменить
Заменить, обрезать
Текст
Верхний

Office 365
Фильтр
Sort
Sortby
Уникальный

Скачать наш
БЕСПЛАТНЫЙ Excel Guides

Нажмите на любую формулу Formula & Funct в листе Excel скачать бесплатно для практики!

👉 Нажмите здесь, чтобы присоединиться к нашему бесплатному веб-семинару по формулам и функциям по ВПР, ЕСЛИ, СУММЕСЛИ и ИНДЕКС/СОПОСТАВЛЕНИЕ и повысить свой уровень Excel!

Хотите знать, как использовать 5 наиболее часто используемых расширенных формул и функций Excel?

*** Посмотрите наше видео и пошаговое руководство ниже со всеми формулами Excel с примерами в таблице Excel, загрузите для практики ***

Посмотрите Advanced Excel Formulas на YouTube и поставьте лайк 👍

3D 

Объединение с 3D-формулами в Excel

И

И формула в Excel

Массив

Рассчитайте общие продажи с массивом массивы

Excel Mray Mrail Formulas, объясненные

Mrray, Lookup

Всего бонус с учетом сбора на массив

if
бесплатная формула Excel Formul Функция: Введение

If, And 

Функция IF в сочетании с функцией AND

Iferror 

IFERROR Функция: Introduction

Iferror, Vlookup 

Как объединить ВПР и ЕСЛИОШИБКА для замены ошибки #Н/Д в Excel Использование формулы ИНДЕКС-ПОИСКПОЗ

Индекс соответствия 2 критериям с проверкой данных

ИНДЕКС-ПОИСКПОЗ Максимальный объем продаж

Косвенный 

ДВССЫЛ Функция с использованием ссылок на листы

ДВССЫЛ Функция в Excel

Косвенная функция для зависимых раскрывающихся списков в Excel

Left

Извлечение данных Excel Lealh Formula

LEN, длина

С функцией ПОИСКПОЗ

Математические формулы 

Формула вычитания в Excel

Формула деления в Excel

Формула сложения в Excel

MOD

Получение оставшейся с несколькими критериями Excel Mod Formula

, Vlookup

Vlookup с несколькими критериями в Excel

Offset

Создайте динамический диапазон с функцией смещения

. в столбце с функцией смещения

Суммируйте последние 7 транзакций с помощью функции смещения

Проценты 

Как рассчитать проценты в Excel

Pivot Table, GetPivotData 

GETPIVOTDATA Function

Proper 

Cleaning Data with Excel’s PROPER Formula

Rand 

Create a Random List with Excel’s RAND Formula

Randbetween 

RANDBETWEEN Function

RANDBETWEEN Function for Excel Dates

Ссылка 

Перейти к ссылке на ячейку в формуле Excel

Сортировка 

Формула СОРТИРОВКИ в Excel

Сортировка 

Sortby Formula в Excel

Заменитель

Добавить запятую после фамилии с заменой Formula

с заменителем Excel Formula

. Пробелы в Excel

Промежуточный итог 

Автоматическое суммирование массива данных в Excel

Функция промежуточного итога Excel — избегайте двойного счета

Функция промежуточного итога Excel — отфильтрованные или видимые значения

Excel Subtotal Function — включайте скрытые значения

Суммирование данных с динамическими субтоталами

Sum, Lookup

Двухчастотный поиск с использованием функции SUM Intersect

Sumifs

SUMIF Функция: Введение

Функция SUMIF: One Criteria и Sum -ancom

Функция СУММЕСЛИМН: Введение

Функция СУММЕСЛИМН: Подстановочные знаки *

Суммарное произведение 

Расширенная функция СУММПРОИЗВ: Условная дата

Расширенная функция СУММПРОИЗВ: Условная сумма

Расширенная функция СУММПРОИЗВ: подсчет

Расширенная функция СУММПРОИЗВ: максимальный объем продаж

Расширенная функция СУММПРОИЗВ: суммирование нескольких критериев

Расширенная функция СУММПРОИЗВ: суммирование трех первых продаж с помощью формулы массива Функция Excel’s TEXT

Добавление начальных нулей в Excel

Время 

Вычисление прошедшего времени в Excel

 

>> ЗАГРУЗИТЕ БЕСПЛАТНО ПОЛНОЕ РУКОВОДСТВО ПО ФОРМУЛАМ EXCEL!

Об авторе

Брайан является автором бестселлеров серии 101 Excel в мягкой обложке.

Полное руководство по использованию Microsoft Excel

    Оставшееся время

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

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

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

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

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

  1. Основы Microsoft Excel

  2. Формулы Excel

  3. Сводные таблицы

  4. Функции ЕСЛИ

  5. ВПР

  6. ИНДЕКС СОВПАДЕНИЕ

Основы Microsoft Excel

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

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

Прежде чем часами считать ячейки или копировать и вставлять данные, поищите быстрое решение в Excel — скорее всего, вы его найдете.

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

Вставка строк или столбцов

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

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

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

Автозаполнение

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

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

Фильтры

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

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

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

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

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

Сортировка

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

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

Удалить дубликаты

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

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

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

Специальная вставка

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

Вместо этого позвольте Excel сделать всю работу за вас. Идите вперед и выделите столбец или строку, которую вы хотите транспонировать. Щелкните правой кнопкой мыши и выберите «Копировать». Затем выберите ячейки в электронной таблице, где вы хотите, чтобы ваша первая строка или столбец начинались. Щелкните правой кнопкой мыши ячейку и выберите «Специальная вставка». Когда появится модуль, выберите вариант транспонирования.

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

Текст в столбцы

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

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

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

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

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

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

Format Painter

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

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

Сочетания клавиш

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

Создать новую книгу

ПК: Ctrl-N | Mac: Command-N

Выбрать всю строку

ПК: Shift-Пробел | Mac: Shift-Пробел

Выбрать весь столбец

ПК: Ctrl-Пробел | Mac: Control-Пробел

Выбор остальной части столбца

ПК: Ctrl-Shift-Вниз/Вверх | Mac: Command-Shift-Down/Up

Выбрать остаток строки

ПК: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Добавить гиперссылку

ПК: Ctrl-K | Mac: Command-K

Открыть окно форматирования ячеек

ПК: Ctrl-1 | Mac: Command-1

Автосуммирование выбранных ячеек

ПК: Alt-= | Mac: Command-Shift-T

Формулы Excel

К этому моменту вы уже познакомились с интерфейсом Excel и уже летаете по своим электронным таблицам быстрее, чем Гарри Поттер на своем Nimbus 2000. Давайте углубимся в основной вариант использования программного обеспечения: формулы Excel. Excel может помочь вам выполнять простые арифметические действия, такие как сложение, вычитание, умножение или деление любых ваших данных. 9знак.

Помните, что все формулы в Excel должны начинаться со знака равенства (=). Используйте круглые скобки, чтобы убедиться, что определенные вычисления выполняются в первую очередь. Например, рассмотрим, чем =10+10*10 отличается от =(10+10)*10.

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

  • Среднее значение: =СРЗНАЧ(диапазон ячеек)
  • Сумма: =СУММ(диапазон ячеек)
  • Счетчик: =COUNT(диапазон ячеек)

Также обратите внимание, что ряды определенных ячеек разделяются запятой (,), а диапазоны ячеек обозначаются двоеточием (:). Например, у вас может быть =СУММ(4,4) или =СУММ(A4,B4) или =СУММ(A4:B4).

Условное форматирование

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

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

Знак доллара

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

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

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

Объединение ячеек с помощью символа «&»

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

В приведенном ниже примере используется эта формула: =A2&» «&B2

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

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

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

Сводные таблицы

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

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

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

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

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

Функции ЕСЛИ

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

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

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

Со значениями это может быть:

=ЕСЛИ(A2>B2 , «Превышение бюджета», «ОК»)

Другими словами, если ваши расходы (то, что в A2) больше, чем ваш бюджет (то, что в B2), эта функция ЕСЛИ упростит просмотр. Затем вы можете отфильтровать данные и увидеть только позиции, в которых вы выходите за рамки бюджета.

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

Диапазоны — это один из способов сегментации данных для лучшего анализа. Например, вы можете разделить данные на значения меньше 10, от 11 до 50 или от 51 до 100. »,ЕСЛИ(B3<100,«от 51 до 100»))))

Функция СЧЁТЕСЛИ

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

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

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

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

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

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

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

Функция СУММЕСЛИ

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

Вот ваша формула:

=СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий 2],. ..)

Критериев много. Давайте углубимся в каждую часть:

  • Sum_range: Это диапазон ячеек, которые вы собираетесь суммировать.
  • Criteria_range1: Это диапазон, в котором выполняется поиск вашего первого значения, также известного как…
  • Criteria1: Это конкретное значение, определяющее, какие ячейки в Criteria_range1 будут суммироваться. Не забудьте использовать кавычки, если вы ищете текст.

Как видно из синтаксиса формулы, в формулу можно добавить сколько угодно различных критериев.

Если И/ИЛИ

Последним членом семейства функций ЕСЛИ являются функции ИЛИ и И. Как следует из их названия, эти функции проверяют несколько аргументов и возвращают либо ИСТИНА, либо ЛОЖЬ в зависимости от того, истинен ли хотя бы один из аргументов (это функция ИЛИ) или все они истинны (это функция И). .

Затерянный в море «и» и «или»? Не проверяйте пока. На практике функции ИЛИ и И никогда не будут использоваться сами по себе. Они должны быть вложены внутрь другой функции ЕСЛИ. Вспомним синтаксис базовой функции ЕСЛИ:

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

Теперь давайте поместим функцию ИЛИ внутри логической_проверки:

=ЕСЛИ(ИЛИ(логическое1, логическое2), значение_если_истина, [значение_если_ложь03

3

])

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

ВПР

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

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

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

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

Формула с переменными из нашего примера ниже: =ВПР(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).]
  • Поиск диапазона: Используйте FALSE, чтобы обеспечить получение только точных совпадений значений.

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

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

ПОИСКПОЗ ИНДЕКС

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

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

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

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

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

Формула:

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

Получается:

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

Формула из нашего примера ниже :

=ИНДЕКС(Лист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.

После того, как вы определились со своими переменными, введите формулу INDEX MATCH в самую верхнюю ячейку пустого столбца Patronus на листе 1, где вы хотите разместить объединенную информацию.

Визуализация данных

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

Читайте также: