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

Содержание

Полезные формулы Excel и «Google Таблиц» для маркетолога

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

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

Формулы Excel и Google Таблиц для работы с рекламой

Заменить значение в большом количестве объявлений

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

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

Для Excel и Google Таблиц: =ПОДСТАВИТЬ(text_to_search;search_for;replace_with)
text_to_search — строка или адрес ячейки, где нужно заменить одно слово на другое;
search_for — фрагмент или слово, которые нужно найти и заменить;
replace_with — слово или фрагмент, на которые меняем search_for.

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

Убрать лишние пробелы в объявлениях и минус-словах

Лишние пробелы — зло в любом виде текста, будь то статья, пост в соцсетях или текст на баннере. Но, когда вы создаете объявления, лишние пробелы мешают дважды: во-первых, они съедают драгоценное место, а во-вторых, «Директ Коммандер» ругается, если загрузить минус-слова с лишними пробелами.

Чтобы быстро удалить пробелы, можно использовать функцию «Сжать пробелы» — =СЖПРОБЕЛЫ(). В скобках указывается номер ячейки, из которой нужно удалить лишние пробелы.

Для Excel и Google Таблиц =СЖПРОБЕЛЫ(диапазон)

Показать клиенту сразу все варианты объявлений для РСЯ и КМС

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

=IMAGE(ссылка; [режим]; [высота]; [ширина])
Функция IMAGE есть только в Google Таблицах и только на английском языке.

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

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

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

Формулы Excel и Google Таблиц для работы с семантикой

Убрать мусорные запросы из семантики

Собрать семантическое ядро для обладателя Macbook целая проблема. Есть ощущение, что SEOшники не любят яблочников, потому что ни SlovoEb, ни KeyCollector под MAC не адаптированы.

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

В статье про мегаэффективное семантическое ядро моя коллега Екатерина Чикулаева рассказывала, что при сегментировании семантики можно воспользоваться фильтром. Так же можно удалить и мусорные запросы, но это занимает очень-ооочень-оооочень много времени.

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

Для Excel и Google Таблиц:
=НАЙТИ(искать; текст; [начиная с])
=ПОИСК(искать; текст; [начиная с])
Искать — текст, который необходимо найти; Текст — где искать; Начиная с — необязательный атрибут, который нужен, если вы хотите пропустить часть символов в строке.

Функции отличаются тем, что «Найти» учитывает регистр, а «Поиск» — нет.

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

После того как вы добавите формулу во все ячейки, нажмите «Сортировать лист А → Я», и все строки, содержащие это слово, окажутся сверху. Теперь их нужно просто удалить. Очередность ключей при этом не нарушится, но если вдруг — то просто отсортируйте этот столбец от Я до А, то есть от большего к меньшему.

Убрать дубли ключей

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

Для Excel: Вкладка «Данные» > Удалить Дубликаты
Для Google Таблиц: =UNIQUE(диапазон)

Проверить длину мета-описания

Рекомендуемая длина Title — 70-80 символов, а Description — 160-180. Если вы работаете над мета-тегами для большого количества страниц, то уследить за этими параметрами вручную можно, но сложно. Гораздо проще автоматизировать процесс с помощью формулы «Длина строки».

Для Excel и Google Таблиц: =ДЛСТР(диапазон)

Не буду подробно описывать, как она работает: это видно на примере ниже. То же самое можно проделать и с Description.

Какие мета-теги подходят, а какие требуют доработки, можно также быстро определить с помощью функции «Условное форматирование». Чтобы ее найти, нажмите в верхнем меню кнопку «Формат» и в открывшемся меню — «Условное форматирование».

Дальше выберите столбец «Длина Title», примените к нему условие «Меньше или равно» и укажите максимальную длину мета-тега. В нашем случае это 80. Затем задайте определенное выделение. Например, все правильные title функция выделит зеленым.

Готово! Теперь вы видите, сколько еще тайтлов вам осталось сократить. Повторюсь, с Description это тоже работает, как в принципе и с любыми другими текстовыми задачами с ограниченным количеством символов.

Формулы Excel и «Google Таблиц» для работы с рассылками

Добавить данные о подписчиках

Представим, что у вас есть база клиентов, которую вы хотите добавить в сервис почтовых рассылок, например, MailChimp. Вы знаете не только e-mail клиента, но и его ФИО. Чтобы сервис точно знал, что из введенных данных имя, что фамилия, а что отчество, их нужно разделить на три столбца и при выгрузке задать значение для каждой группы.

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

Для Google Таблиц: =SPLIT(диапазон; «разделитель»)
Для Excel: Данные — Текст по столбцам — С разделителями

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

Какие полезные формулы знаете вы? Расскажите в комментариях. Давайте поможем друг другу оптимизировать работу.

Практическая работа по теме «Работа с электронными таблицами Excel»

Практическая работа.

Тема: Работа с электронными таблицами Excel.

Цель: освоить основные принципы работы с электронными таблицами.

Задачи:

Изучить основные понятия.

Создать электронную таблицу в Microsoft Excel.

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

Научиться выполнять в Microsoft Excel построение диаграмм и графиков.

Теоретический материал.

Основные понятия.

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

Обработка включает в себя:

1) Проведение различных вычислений с использованием мощного аппарата функций и формул;

2) Исследование влияния различных факторов на данные;

3) Решение задач оптимизации;

4) Получение выборки данных, удовлетворяющих определенным

критериям;

5) Построение графиков и диаграмм; 6) Статистический анализ данных.

Таким образом, применение электронных таблиц имеет достаточно обширный спектр.

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

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

Переименовать контекстного меню, вызываемого правой кнопкой мыши на надписи: Лист 1.

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

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

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

Последняя строка – строка состояния.

Рабочее поле редактора Excel представляет собой таблицу. Каждая ячейка этой таблицы имеет свой адрес, который определяется латинскими буквами по горизонтали и арабскими цифрами по вертикали. Например: А4, B12, AC23 и т.д. В ссылке на ячейку сначала указывают имя столбца, затем номер строки. Всего ЭТ может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).При формировании формул используются именно ссылки на ячейки, которые содержат необходимые данные. В ячейках размещают текст, числа, формулы.

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

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

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

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

Например, ссылка $D$5 при перемещении не изменится.

$D7 – при перемещении формулы с такой ссылкой будут меняться только номера строк. Ссылка является абсолютной по столбцам и относительной по строкам.

D$7 – при перемещении формулы изменятся только имена столбцов. Ссылка будет относительной по столбцам и абсолютной по строкам.

Построение формул

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

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

Ячейка С3 содержит произведение ячеек А3 и В3. обратите внимание, что формула видна не только в самой ячейке, но и в специальной строке формул, где ее всегда удобно исправить или просмотреть.

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

Изменяя значения в ячейках А3 и В3 можно увидеть, что Excel производит расчет новых данных автоматически.

Для построения значений в Excel

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

1. Командой главного меню: Вставка / Функция.

2. При выборе знака = в ячейке с будущей формулой слева от строки формул автоматически появится выборка десяти последних вызванных формул. Если ни одна формула не подходит пользователю, необходимо выбрать в списке Другие функции…

, после чего появится Мастер функций.

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

Формат данных

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

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

1) правой кнопкой мыши вызвать контекстное меню, в котором выбрать команду Формат ячеек для вызова окна Формат ячеек;

2) выбрать команду главного меню Формат / Ячейки…

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

После открытия окна Формат ячеек необходимо выбрать в окне Числовые форматы нужный Вам формат. На рис. 3 приведен пример формата даты. Просто укажите один из предлагаемых в окне справа форматов и нажмите ОК. В выбранной ячейке будет применен выбранный вами формат.

Построение диаграмм и графиков

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

Создать диаграмму в Excel можно по шагам с помощью Мастера диаграмм, вызов которого осуществляется с панели инструментов соответствующей кнопкой. Сначала Мастер диаграмм предлагает выбрать тип диаграммы (шаг 1), затем указать диапазон ячеек из соответствующей таблицы (шаг 2), если диапазон с исходными данными заранее не был выделен. Для выделения несмежных областей с исходными данными следует сначала выделить первую область, затем нажать клавишу <Ctrl> и, удерживая ее, выделить остальные области.

На последнем шаге (шаг 3) необходимо выбрать место расположения диаграммы: на новом листе или на текущем.

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

Расчетные операции в Microsoft Excel.

Создание и редактирование таблиц. Расчет по формулам.

Задание 1.1. Создание таблиц в Microsoft Excel.

Составить таблицу по теме «Общий анализ крови», используя образец.

Форменные элементы крови	Норма	Больной
Гемоглобин	14	9
СОЭ	6	15
Эритроциты	4	3,7
Лейкоциты	8	15
Цветовые показатели	1	0,78

Задание 1. 2. Составить таблицу. «Отчет движения медикаментов за текущий месяц» в табличном процессоре EXCEL. Используя математические формулы, заполнить данными столбец «РАСХОД».

Отчет движения медикаментов за январь 2020 года
№ п/п	Название медикаментов	Единица измерения	Остаток на начало месяца	Приход	Расход	Остаток на конец месяца
1.	Аспирин	в упаковке	80	170		35
2.	Анальгин	в упаковке	67	850		216
3.	Морфин	в ампулах	3	300		0
4.	Аскорбиновая кислота	драже	72	100		10
5.	Но-шпа	в упаковке	65	230		59
6.	Дигитоксин	в упаковке	600	1000		89

Методические рекомендации для выполнения задания 1.2.

1. Сделать текущей ячейку А1. Ввести заголовок таблицы «Отчет движения медикаментов», нажать на клавиши Alt (левый)+ Enter, набрать на клавиатуре «за январь 2020 года». Выделить ячейки А1 — А7 (они становятся черными). Нажать на значок а. Заголовок располагается по середине 7 столбцов;

2. Сделать текущей ячейку А2. Ввести «№», нажать Alt + Enter, набрать на клавиатуре «п/п»;

3. Сделать текущей В2. Ввести «Наименование медикаментов»

4. Сделать текущей ячейку С2.

• Ввести «Единицы»

• Нажать на комбинацию клавиш Alt + Enter

• Ввести «измерения»

5. Сделать текущей ячейку D2

• ввести «Остаток»

• нажать на комбинацию клавиш Alt (левый) + Enter  ввести «на начало»

• нажать на комбинацию клавиш Alt (левый) + Enter

• ввести «месяца»

6. Сделать текущей ячейку Е2

• ввести «Приход»

7. Сделать текущей ячейку F2

• ввести «Расход»

8. Произвести щелчок на ячейку D2

• произвести щелчок на ПРАВКА из зоны меню

• произвести щелчок на КОПИРОВАТЬ из дополнительного меню

• произвести щелчок на ячейку G2

• произвести щелчок ПРАВКА из зоны меню

• произвести щелчок на ВСТАВИТЬ

• щелкнуть мышью на «начало» и удалить это слово

• ввести слово «конец»

9. Сделать текущим А3 , ввести «1.»

10. Сделать текущей В3 ввести «Аспирин»

Заполнить таким образом необходимое количество строк в таблице

11. После заполнения таблицы, делаем расчет столбца «Расход» используя формулы. Для этого:

• сделать текущей ячейку F3

• ввести форму для расчета =D3+E3-G3, нажать Enter, появится результат расчета

• Введенную формулу с помощью функции автозаполнения копируем на остальные строчки, следующим образом:

• Сделать текущей ячейку F3

• Установить курсор мыши на правый нижний угол ячейки таким образом, чтобы появился знак +

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

• кнопку мыши отпустить.

В результате выполненных операций во всех стоках графы РАСХОД появится результат и таблица будет полностью заполнена.

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

Задание 1.3. Рассчитать общую стоимость стоматологических услуг.

Стоматологическая поликлиника

Услуги	Стоимость услуги	Количества пациентов	Общая стоимость
Консультация ортодонта	90	35
Консультация гигиениста	230	16
Снимок ротовой полости	217	22
Удаление	250	7
Лечение	420	13
Протезирование	1200	13
Фтороризация зубов	408	18
Итого

Построение диаграмм и графиков в Microsoft Excel

Задание 2. 1. Используя данные таблицы из задания 1.2, составить диаграммы следующих разновидностей:

а) гистограмму по всем медикаментам;

б) круговые диаграммы (разных видов) по отдельным медикаментам;

Методические рекомендации для выполнения задания по построению диаграммы:

1) Построить диаграмму — гистограмму:

• выделить всю таблицу за исключением заголовка;

• щёлкнуть на ВСТАВКА;

• выбрать ДИАГРАММА;

• щёлкнуть на НА ЭТОМ ЛИСТЕ;

• натянуть курсором мыши рамку на рабочем листе, тем самым зарезервировав место для диаграммы;

шаг 1: в появившемся окне «Мастер диаграмм» выбрать Далее

шаг 2: определить тип диаграммы, через щелчок мышью на Гистограмма и нажать Далее;

шаг 3: выбрать вид гистограммы под № 8 и нажать Далее; шаг 4: в появившемся окне «Мастер диаграмм» появится образец, нажать Далее;

шаг 5: в появившемся окне «Мастер диаграмм» выбрать Добавить легенду? Да и ввести название диаграммы «Учёт движения медикаментов» и щёлкнуть на Готово.

2) Построение круговой диаграммы по отдельным медикаментам различных видов:

• выделить отдельную строку по какому-либо медикаменту;

• щёлкнуть на ВСТАВКА;

• выбрать ДИАГРАММА;

• щёлкнуть на НА НОВОМ ЛИСТЕ; шаг 1: в появившемся окне «Мастер диаграмм» выбрать Далее; шаг 2: в окне «Мастер диаграмм» выбрать тип диаграммы Круговая и Далее;

шаг 3: в окне «Мастер диаграмм» выбрать вид круговой диаграммы из предложенных вариантов и Далее;

шаг 4: в окне «Мастер диаграмм» отметить щелчком Да (Добавить легенду?), ввести название диаграмм, название медикамента и щёлкнуть на Готово.

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

Например:

График изменения температуры больного ФИО

Число	Утром	Вечером	Норма
Чч. мм	38,5	39	36,6
Чч.мм	38	38,5	36,6
Чч.мм	37,5	38	36,6
Чч.мм	37	37,5	36,6
Чч. мм	36,6	37	36,6

Задание 2.3. Построение круговой и столбиковой диаграмм.

Составить круговую и столбиковые диаграммы составных частей лекарственного препарата-драже «Ревит», ориентируясь на следующие данные:

ретинол ацетат —	0,86 мг
ретинол пальмитат —	1,38 мг
тиамин бромид —	1,29 мг
рибофлавин —	1 мг
кислота аскорбиновая —	35 мг

Задание 6. Построить гистрограмму и проанализировать демографические показатели города N(в промилях) за несколько лет. Показатели строки

«Естественный прирост» вычислите по формулам.

	2011	2012	2013	2014	2015
рождаемость	17	19	16	15	18
смертность	18	16	17	14	12
естественный прирост

Вычисление нескольких результатов с использованием таблицы данных

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

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

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

Примечание. Вы можете выполнять вычисления быстрее с таблицами данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в статье Таблицы данных «что если» в Excel: более быстрые расчеты с помощью VBA.

Типы анализа «что, если»

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

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

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

Подробнее читайте в статье Введение в анализ возможных вариантов.

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

Таблицы данных с одной переменной

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

На следующем рисунке ячейка D2 содержит формулу платежа =ПЛТ(B3/12,B4,-B5) , которая относится к входной ячейке B3.

Таблицы данных с двумя переменными

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

На следующем рисунке ячейка C2 содержит формулу платежа =PMT(B3/12,B4,-B5) , в которой используются две входные ячейки, B3 и B4.

Вычисления таблицы данных

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

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

Выполните следующие действия:

Введите список значений, которые вы хотите заменить во входной ячейке — либо вниз на один столбец, либо через одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
org/ListItem»>
Выполните одно из следующих действий:
Если таблица данных ориентирована на столбцы (значения ваших переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
Если таблица данных ориентирована на строки (ваши значения переменных находятся в строке), введите формулу в ячейку на один столбец левее первого значения и на одну ячейку ниже строки значений.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
Выберите диапазон ячеек, содержащий формулы и значения, которые вы хотите заменить. На рисунке выше это диапазон C2:D5.
На вкладке Данные нажмите Анализ «что, если» > Таблица данных (в группе Инструменты данных или Прогноз в группе Excel 2016).
Выполните одно из следующих действий:
- org/ListItem»>
  Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца . На рисунке выше входная ячейка — B3.
- Если таблица данных ориентирована на строки, введите ссылку на ячейку для входной ячейки в поле 9.0003 Строка ввода ячейки поле.
  Примечание. После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов отформатированы как валюта.

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

Выполните следующие действия

org/ItemList»>
Выполните одно из следующих действий:
Если таблица данных ориентирована на столбцы, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
Если таблица данных ориентирована на строки, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
Выберите диапазон ячеек, содержащий таблицу данных и новую формулу.
На вкладке Данные щелкните Анализ «что, если» > Таблица данных (в группе Инструменты данных или Прогноз в группе Excel 2016).
Выполните одно из следующих действий:
Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца .
Если таблица данных ориентирована на строки, введите ссылку на ячейку для входной ячейки в поле 9. 0003 Строка ввода ячейки поле.

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

Выполните следующие действия:

В ячейку рабочего листа введите формулу, которая относится к двум входным ячейкам.
В следующем примере, в котором начальные значения формулы вводятся в ячейки B3, B4 и B5, введите формулу =ПЛТ(B3/12,B4,-B5) в ячейку C2.
Введите один список входных значений в том же столбце под формулой.
В этом случае введите разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список в той же строке, что и формула, справа от нее.
Введите условия кредита (в месяцах) в ячейки D2 и E2.
Выберите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых вы хотите вычислить значения (D3:E5).
В этом случае выберите диапазон C2:E5.
На вкладке Данные в группе Инструменты данных или Прогноз (в Excel 2016) нажмите Анализ «что, если» группы Excel 2016).
В поле Row input cell введите ссылку на входную ячейку для входных значений в строке.
Введите ячейку B4 в поле Row input cell .
В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце.
Введите B3 в поле ввода столбца .
Нажмите OK .

Пример таблицы данных с двумя переменными

Таблица данных с двумя переменными может показать, как различные комбинации процентных ставок и условий кредита повлияют на ежемесячный платеж по ипотечному кредиту. На этом рисунке ячейка C2 содержит формулу платежа, =PMT(B3/12,B4,-B5) , который использует две входные ячейки, B3 и B4.

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

Выполните следующие действия, чтобы повысить производительность вычислений:

Щелкните Файл > Параметры > Формулы.
В разделе Параметры расчета в разделе Вычислить щелкните Автоматически, кроме таблиц данных .
Совет: При необходимости на вкладке Формулы щелкните стрелку Параметры расчета , затем щелкните Automatic Except Data Tables (в группе Calculation ).

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

Поиск цели

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

Решатель Excel

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

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

Здесь таблица данных представляет собой диапазон ячеек B2:D8. Вы можете изменить значение в B4, сумму кредита и ежемесячные платежи в столбце D автоматически обновляются. Используя процентную ставку 3,75%, D2 возвращает ежемесячный платеж в размере 1042,01 доллара США по следующей формуле: =ПЛТ(C2/12,$B$3,$B$4).

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

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

В этой рабочей книге ячейка D2 содержит формулу платежа = PMT (C2/12, $B$3, $B$4). Ячейка B3 — это ячейка переменной , в которую вы можете вставить другую продолжительность срока (количество периодов ежемесячных платежей). В ячейке D2 функция ПЛТ подставляет процентную ставку 3,75%/12, 360 месяцев и кредит в размере 225 000 долларов США и вычисляет ежемесячный платеж в размере 1 042,01 доллара США.

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

В этой рабочей книге ячейка C3 содержит формулу оплаты = PMT($B$3/12,$B$2,B4), в которой используются две переменные ячейки, B2 и B3. В ячейку C2 функция ПЛТ подставляет процентную ставку 3,875%/12, 360 месяцев и кредит в размере 225 000 долларов США и вычисляет ежемесячный платеж в размере 1 058,03 доллара США.

Нужна дополнительная помощь?

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

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

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

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

Таблица данных в Excel
Создать одну таблицу переменных данных
Создание таблицы данных с двумя переменными
Таблица данных для оценки нескольких формул
Таблицы данных Excel — 3 вещи, которые вы должны знать
Удалить таблицу данных в Excel
Изменить результаты таблицы данных
Пересчитать таблицу данных вручную

Что такое таблица данных в Excel?

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

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

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

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

Как создать таблицу данных с одной переменной в Excel

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

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

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

B8 содержит формулу FV, которая вычисляет конечный баланс.
B2 — это переменная, которую вы хотите протестировать (начальные инвестиции).

А теперь давайте проведем простой анализ «что, если», чтобы увидеть, каковы будут ваши сбережения через 5 лет, в зависимости от суммы ваших первоначальных инвестиций, в диапазоне от 1000 до 6000 долларов.

Вот шаги для создания таблицы данных с одной переменной:

Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных , ориентированную на столбцы , поэтому мы вводим значения наших переменных в столбце (D3:D8) и оставляем по крайней мере один пустой столбец справа для результатов.
Введите формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или свяжите эту ячейку с формулой в исходном наборе данных (если вы решите изменить формулу в будущем, вам нужно будет обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2: = B8
Совет. Если вы хотите изучить влияние значений переменных на другие формулы, которые относятся к той же входной ячейке, введите дополнительные формулы справа от первой формулы, как показано в этом примере.
Выберите диапазон таблицы данных, включая формулу, ячейки со значениями переменных и пустые ячейки для результатов (D2:E8).
Перейдите на вкладку Data > группу Data Tools , нажмите кнопку What-If Analysis , а затем щелкните Data Table…
В диалоговом окне Таблица данных щелкните в ячейке ввода столбца (поскольку наши значения Инвестиции находятся в столбце) и выберите переменную ячейку, указанную в вашей формуле. В этом примере мы выбираем B3, который содержит первоначальную стоимость инвестиций.
Нажмите OK , и Excel немедленно заполнит пустые ячейки результатами, соответствующими значению переменной в той же строке.
Примените желаемый числовой формат к результатам ( Валюта в нашем случае), и все готово!

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

Таблица данных, ориентированная на строки

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

Введите значения переменных в строке, оставив по крайней мере один пустой столбец слева (для формулы) и одну пустую строку ниже (для результатов). ). В этом примере мы вводим значения переменных в ячейки F3:J3.
Введите формулу в ячейку, которая находится на один столбец левее первого значения переменной и на одну ячейку ниже (в нашем случае E4).
Создайте таблицу данных, как описано выше, но введите входное значение (B3) в поле Строка ввода :
Нажмите OK , и вы получите следующий результат:

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

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

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

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

Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк ниже, чтобы вместить значения ваших переменных. Как и прежде, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: = B8
Введите один набор входных значений под формулой в том же столбце (инвестиционные значения в E3:E8).
Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2:h3).
На данный момент ваша таблица данных с двумя переменными должна выглядеть примерно так:
Выберите весь диапазон таблицы данных, включая формулу, строку и столбец значений переменных, а также ячейки, в которых будут отображаться вычисляемые значения. Мы выбираем диапазон E2:H8.
Создайте таблицу данных уже знакомым способом: Данные вкладка > Кнопка «Что если анализ» > Таблица данных…
В поле строки ввода введите ссылку на входную ячейку для значений переменных в строке (в этом примере это B6, содержащий лет значение).
В поле ввода столбца введите ссылку на входную ячейку для значений переменных в столбце (B3, содержащее значение Initial Investment ).
Щелкните OK .
При необходимости отформатируйте выходные данные так, как вам нужно (применив формат Валюта в нашем случае), и проанализируйте результаты:

Таблица данных для сравнения нескольких результатов

Если вы хотите оценить несколько формул одновременно, создайте таблицу данных, как показано в предыдущих примерах, и введите дополнительные формулы:

Справа от первой формулы в случае вертикальной таблицы данных, организованной в столбцы
Под первой формулой в случае таблицы данных по горизонтали , организованной в строки

Для корректной работы таблицы данных с несколькими формулами все формулы должны ссылаться на одну и ту же входную ячейку .

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

В ячейке B10 вычислите процентов по следующей формуле: =B8-B3
Расположите исходные данные таблицы данных, как мы делали ранее: значения переменных в D3:D8 и E2 связаны с B8 (формула Balance ).
Добавьте еще один столбец в диапазон таблицы данных (столбец F) и свяжите F2 с B10 (формула процентов ):
Выберите диапазон расширенной таблицы данных (D2:F8).
Откройте диалоговое окно Data Table , нажав Вкладка «Данные» > Анализ «что, если» > Таблица данных…
В поле Column Input Cell введите ячейку ввода (B3) и нажмите OK .

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

Таблица данных в Excel — 3 вещи, которые вы должны знать

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

Для успешного создания таблицы данных входные ячейки должны находиться на тот же лист , что и таблица данных.
Microsoft Excel использует функцию TABLE(row_input_cell, colum_input_cell) для вычисления результатов таблицы данных: В таблице данных с одной переменной один из аргументов опущен в зависимости от макета (ориентированного на столбцы или на строки). Например, в нашей горизонтальной таблице данных с одной переменной формула имеет вид = ТАБЛИЦА (, B3) , где B3 — ячейка ввода столбца.
В таблице данных с двумя переменными оба аргумента на месте. Например, = ТАБЛИЦА (B6, B3) , где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.
Функция ТАБЛИЦА вводится как формула массива. Чтобы убедиться в этом, выберите любую ячейку с вычисленным значением, посмотрите на строку формул и обратите внимание на {фигурные скобки} вокруг формулы. Однако это не обычная формула массива — вы не можете ввести ее в строку формул и не можете редактировать существующую. Это просто «для галочки».
Поскольку результаты таблицы данных рассчитываются с помощью формулы массива, результирующие ячейки нельзя редактировать по отдельности. Вы можете редактировать или удалять только весь массив ячеек, как описано ниже.

Как удалить таблицу данных в Excel

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

Однако можно легко очистить весь массив от полученных значений. Вот как:

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

Готово! 🙂

Как редактировать результаты таблицы данных

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

Выберите все полученные ячейки.
Удалите формулу ТАБЛИЦА в строке формул.
Введите нужное значение и нажмите Ctrl + Enter.

Это вставит одно и то же значение во все выбранные ячейки:

Как только формула ТАБЛИЦА исчезнет, прежняя таблица данных станет обычным диапазоном, и вы сможете редактировать любую отдельную ячейку в обычном режиме.

Как пересчитать таблицу данных вручную

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

Для этого перейдите на вкладку Формулы > группу Расчет , щелкните Параметры расчета , а затем щелкните Автоматически за исключением таблиц данных .

Это отключит автоматические расчеты таблицы данных и ускорит пересчет всей книги.

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

Вот как вы создаете и используете таблицу данных в Excel.