Excel формулы таблицы: Обзор формул — Служба поддержки Майкрософт

Содержание

Какие существуют ограничения для формул Excel — Трюки и приемы в Microsoft Excel

Все формулы в Excel имеют следующую базовую структуру: они начинаются со знака равно (=), следом идут операнды — значения, ссылки на ячейки, диапазоны и их имена, названия функций — и все это может быть разделено операторами — символами, которые сочетают операнды определенным образом, такими как знак плюса (+) или знак больше (>). Ничего страшного, если вы будете использовать пробелы между операторами или операндами. Напротив, это повысит читаемость документа. Вы также можете перенести часть формулы на другую строку, используя сочетание клавиш Аlt + Enter. Несмотря на то что маловероятно превышение этих ограничений, неплохо быть всегда в курсе и знать возможные пределы. Все ограничения представлены в следующей таблице.

Ограничения формул в Excel 2010

Объект	Новый максимум	Старое ограничение (до Excel 2007)
Столбцы	16 384	1024
Строки	1 048 576	65 536
Длина функции (символов)	8 192	1 024
Аргументы функции	255	30
Уровень вложения функций	64	7
Ссылки	Неограничено	65 335
Столбцы сводной таблицы	16 384	255
Строки сводной таблицы	1 048 576	65 536
Поля в сводной таблице	16 384	255
Уникальность элементов в сводной таблице	1 048 576	3 268

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

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

При этом в Excel имеется три механизма ввода, в соответствии с которыми программа интерпретирует ваши действия:

Когда вы вводите знак равенства (=) для начала формулы, Excel переходит в режим ввода, который используется вами для ввода операторов и операндов формулы.
Если вы нажмете на любую из клавиш навигации клавиатуры (это все клавиши со стрелками, а также «Page Up», «Page Down», «Ноmе» и «End») или если вы кликнете мышью на любую другую ячейку, Excel переходит в режим указания. В данном режиме вы выбираете ячейки или диапазоны как операнды. Excel возвращается в режим ввода, когда вы введете с клавиатуры любой символ.
При нажатии на F2 Excel переходит в режим редактирования. В данном режиме вы можете производить изменения в формуле. К примеру, вы можете использовать клавиши «←» и «→» клавиатуры для перемещения по различным частям формулы для удаления или вставки символов. Также в этот режим вы можете войти, дважды кликнув на ячейке с формулой или просто внутри формулы.Нажмите F2 для возврата в режим ввода.

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

Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo. gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).

— Если результат формулы несколько ячеек
— Объединение нескольких диапазонов данных для использования в формулах
— SORT
— Как в SORT добавить заголовки таблицы?
— FILTER
— FILTER, два условия и работа с датой
— Интерактивный график при помощи FILTER и SPARKLINE
— IMPORTRANGE
— Импорт форматирования из исходной таблицы
— IMPORTRANGE как аргумент другой функции
— IMAGE: добавляем изображения в ячейки
— GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

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

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

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

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

! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные). Они как бы “зарезервируются” под весь открытый диапазон — ведь его размерность неизвестна заранее.

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

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

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

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

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

[source_language] – язык, с которого мы переводим;

[target_language] – язык, на который мы переводим.

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

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

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

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

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

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

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

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

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

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

org/ListItem»>
Выполните одно из следующих действий:
Если таблица данных ориентирована на столбцы (значения ваших переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
Если таблица данных ориентирована на строки (ваши значения переменных находятся в строке), введите формулу в ячейку на один столбец левее первого значения и на одну ячейку ниже строки значений.
Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.

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

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

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

Вы можете использовать надстройку 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 состоят из заголовка, который обычно находится в первой строке таблицы, и столбцов под заголовками. Вы можете использовать строку заголовка для фильтрации данных в таблице Excel.

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

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

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

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

Вуаля! У вас есть таблица Excel! Теперь вы можете использовать заголовки для сортировки и фильтрации данных любым удобным для вас способом. Однако наша работа здесь еще не сделана. Пришло время создать формулу для расчета общего объема продаж для каждого сотрудника:

В строке формул введите следующую формулу:
=СУММ(Таблица1[@[Продажи за Q1]:[Продажи за Q2]])
Лучше ввести формулу, содержащую ссылки на структуру, а не вставлять ее, поскольку заголовки уникальны. Как только вы введете знак at после скобки, Excel предложит вам доступные заголовки в Table1 . В этой формуле мы призвали 9Функция 0402 SUM и структурные ссылки на Q1 Sales и Q2 Sales столбец. В результате эта формула суммирует числа в этих двух столбцах.

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

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

Excel формулы таблицы: Обзор формул — Служба поддержки Майкрософт

Какие существуют ограничения для формул Excel — Трюки и приемы в Microsoft Excel

Ограничения формул в Excel 2010

Популярно

Метки

ВКонтакте

Полезные функции Google Таблиц, которых нет в Excel / Хабр

SORT

FILTER

IMPORTRANGE

Импорт форматирования из исходной таблицы

IMAGE: добавляем изображения в ячейки

GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

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

Как создать таблицу в Excel

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

Как создать таблицу в Excel

Таблицы Excel упрощают анализ

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

Ошибка в виндовс: Справка по ошибкам Windows обновления и установки

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

Сравнить ноутбук: Сравнение ноутбуков – NanoReview