Как использовать формулу двустороннего поиска в Excel?

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Как пользоваться формулами в Excel: основные принципы, особые формулы

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

Для чего нужны?

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

Создание формулы

Чтобы создать формулу, необходимо:

1. Нажать левой кнопкой мыши дважды по ячейке.
2. Ввести «=».
3. Ввести «2+2».
4. Нажать на клавишу «Enter».

Затем программа автоматически рассчитает пример; результат показан на рисунке ниже.

Другие базовые вычисления (сложение, вычитание, деление (используйте / для выполнения операции), умножение (используйте * для выполнения операции)) могут быть использованы аналогичным образом.

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

Варианты работы с формулами

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

1. Ввести данные в произвольные ячейки. Например, три числа: 15, 28, 35.
2. В свободной ячейке указать =, адреса ячеек и требуемую операцию между ними. Например, сложение.
3. Нажать на клавишу «Enter».

Результат отображается на рисунке ниже.

Копирование и вставка

Формулы можно копировать и вставлять в несколько ячеек. Для этого потребуется:

1. Выделите ячейку с формулой с помощью мыши.
2. Нажмите Ctrl+C.
3. Выберите пустую ячейку.
4. Нажмите Ctrl+V.

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

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

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

1. Выберите ячейку с формулой.
2. Переместите курсор мыши в правый нижний угол ячейки. Форма курсора меняется.
3. Дважды щелкните по нему левой кнопкой мыши.

По завершении автозаполнения заполняется следующая ячейка.

Абсолютные и относительные ссылки

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

1. Выбрать ячейку с формулой, в которой нужно зафиксировать значение.
2. Добавить к ячейке $.
3. Нажать на клавишу «Enter».

После этого значение ячейки не будет меняться.

Особые формулы

Некоторые популярные и практичные формулы стоит отметить отдельно. Интересно начать с использования формулы БПФ в Excel.

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

• Создать столбцы, в которые будет помещено новое значение.
• В формулах выбрать «ВПР».

• Указать диапазон ячеек, по которым будет выполняться расчёт. В поле «интервальный просмотр» указать значение «ЛОЖЬ», только тогда значения будут точные, а не приблизительные.

• Нажать на «ОК». После этого столбец будет заполнен, при необходимости его следует растянуть.

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

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

После этого значения выбранных клеток суммируются.

Excel works!

Поиск в двумерном массиве Excel (по матрице). ВПР и ПОИСКПОЗ

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

В этом случае проект не является тривиальным. Поэтому, прежде чем читать следующую статью, я рекомендую вам ознакомиться с тем, как работают функции БПФ и ПОИСК, особенно если вы раньше не имели дела с этими функциями.

Пример

Чтобы это было понятнее, начнем с примера.

Есть таблица по городам и стоимости услуг, стоимость зависит от месяца.

Нам нужно найти стоимость за третий месяц для города Краснодар, или найти Sij, если по-умному. Как нам это сделать?

Поиск в двумерном массиве Excel. Как записать формулу

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

Где столбец G — искомый город (Краснодар), а столбец H — месяц (третий). При записи можно использовать и просто одну ячейку, например, G4 и H4

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

Описание проекта строительства:

1. Функция ВПР осуществляет поиск нужного нам значения (Города, синий столбец G) в большой таблице $B:$E (отмечена зеленым). Для этого нам необходимо указать в каком же именно столбце этой таблицы ВПРу искать город — втором, третьем или четвертом (C, D, E)
2. Номер этого столбца найдем при помощи функции ПОИСКПОЗ, которая находит номер элемента в массиве. А точнее найдем номер значения фиолетового столбца H (месяца) в массиве $B$2:$E$2. Тем самым мы определили, что ВПР надо использовать третий столбец.
3. Четвертый аргумент оставляем пустым (после последней «;»), т.к. по умолчанию можно оставить ЛОЖЬ или пусто. Подробнее здесь.

Обратите внимание! Чтобы формула работала, она должна удовлетворять двум условиям.

— Ширина массива $B$2:$E$2 в ПОИСКПОЗ и ширина таблицы $B:$E должны совпадать.

— Данные в массиве для ПОИСКПОЗ ($B$2:$E$2) должны быть отсортированы от большего к меньшему или от А до Я!

Как скопировать формулу/формулы в Excel, копирование и вставка формул

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

Что такое формула?

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

Расчет состоит из нескольких элементов (ссылок, функций и констант).

Ссылки — это адреса ячеек или диапазонов ячеек.

Функции — это предопределенные формулы, которые выполняют сложные вычисления с введенными значениями (аргументами) в определенном порядке. Различают математические, статистические, текстовые, логические и другие категории функций.

Контингенты — это величины, которые остаются неизменными с течением времени, будь то числовые или текстовые.

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

Ссылки в формулах

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

Относительные ссылки на ячейки

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

Абсолютные ссылки на ячейки

Если вы изменяете положение ячейки с помощью формулы, абсолютная ссылка остается неизменной. Перед именами столбцов (чисел) в абсолютных ссылках ставится знак $, а перед строками — $1, например, $A$1.

Смешанные ссылки на ячейки

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

Трехмерные ссылки на ячейки

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

Как создать формулу и ввести ее в ячейку?

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

Простые формулы

Простая формула — это формула, которая содержит только константы и численные операторы.

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

— выделяет ячейку, в которую нужно поместить формулу;

— Введите знак равенства (=) с цифровой клавиатуры;

Если вы вводите число, затем знак действия, затем следующее число и т.д., то вы будете действовать таким образом. =2+3*4);

— Нажмите Enter для перехода вниз, Shift+Enter для перехода вверх, Tab для перехода вправо или Shift+Tab для перехода влево.

Формулы с использованием относительных ссылок

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

— найдите ячейку, в которую будет введена формула;

— ввести символ равенства (=) с клавиатуры;

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

Вставьте оператор в формулу, адрес следующей ячейки и так далее;

— Заполните формулу таким же образом, как и в предыдущем случае.

Формулы с использованием абсолютных ссылок

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

— Выберите ячейку, в которой будет располагаться формула;

— ввести символ равенства (=) с клавиатуры;

— создайте нужную формулу, используя относительные ссылки на ячейки;

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

— Нажмите клавишу F4 на вашей клавиатуре;

После ввода формулы нажмите Enter.

Как ввести одну формулу одновременно в несколько ячеек?

Если вы хотите ввести одну формулу в диапазон ячеек, вы должны:

— чтобы выделить диапазон ячеек;

— введите формулу в первую ячейку диапазона;

Для фиксации результата используйте сочетание клавиш Ctrl+Enter.

Как выделить все ячейки с формулами?

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

Как скопировать формулу из одной ячейки в другую?

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

Вы можете скопировать формулу из выделенной ячейки любым известным способом (нажав кнопку «Копировать» на вкладке «Главная», используя сочетание клавиш Ctrl+C, выбрав пункт «Копировать» в контекстном меню и т.д.). Выберите ячейку, куда нужно вставить скопированную формулу, при этом можно использовать любой известный способ вставки (например, нажать Ctrl+V, выбрать пункт «Вставить» в контекстном меню, выбрать «Специальная вставка»). При нажатии Enter результат будет зафиксирован. Вы также можете скопировать формулу, подведя указатель мыши к правому нижнему углу маркера выделения до появления тонкого черного крестика и перетащив его через весь диапазон, удерживая левую кнопку мыши. В этом случае формула содержит ссылки на новые ячейки в каждой последующей ячейке.

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

Как заменить формулу результатом ее вычисления?

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

Как ускорить работу с формулами при создании и редактировании таблиц?

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

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

Поддерживающая структура позволяет:

Вызов диалогового окна макроса непосредственно с панели инструментов Excel осуществляется одним щелчком мыши;

2. В выделенном диапазоне найдите формулы, скопируйте и вставьте их со смещением;

Есть три режима копирования формул:

С помощью функции «Копировать формулы» можно копировать формулы, и все ссылки в формулах будут автоматически обновлены, чтобы отразить размещение формул;

— «Копировать текст формул» — точно копирует формулы, не изменяя ссылки, используемые в формулах;

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

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