Формулы excel работа с таблицами: Работа с формулами эксель

Как работать с формулами в Microsoft Excel: инструкция для новичков

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

Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.

Поиск перечня доступных функций в Excel

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

1. Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.

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

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

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

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Вставка функции в таблицу

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

Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.

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

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

• $В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

• B$2 – неизменна строка.

• $B2 – константа касается только столбца.

Построение графиков функций

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

Читайте также: Как построить график функции в Excel

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

Работа с таблицами в excel для чайников

Содержание статьи (кликните для открытия/закрытия)

1. Знакомство с электронными таблицами
2. Внешний вид интерфейса Excel 2016
3. Видео:Сложение чисел в Excel
4. Как создать формулу в Excel
5. Расчет коммунальных услуг: электричество, газ, вода

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

Знакомство с электронными таблицами

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

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

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

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

Формулой является запись, начинающаяся со знака «=»(равно), содержащая адреса ячеек, знаки арифметических операций, числа и функции.

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

Внешний вид интерфейса Excel 2016

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

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

Внешний вид интерфейса Excel 2016

Блок №2 (выделен жёлтым цветом) содержит ленту вкладок с инструментами.

Блок №3 (выделен зелёным цветом) слева содержит поле, в котором отображается имя (адрес) активной ячейки. Далее идут три кнопки режима редактирования формулы:

• крестик – отказ от ввода в текущую ячейку – удаление ее содержимого;
• галочка – завершение редактирования содержимого ячейки;
• Fx – вызов мастера функций для заполнения ячейки встроенными функциями табличного процессора.

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

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

Блок №4 (выделен фиолетовым цветом) содержит рабочую область (таблицу). Столбцы таблицы обозначают латинскими буквами в алфавитном порядке. Строки нумеруются арабскими цифрами по возрастанию. Внизу рабочей области отображаются ярлыки листов, которые можно добавлять кнопкой «плюс».

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

Видео:Сложение чисел в Excel

Как создать формулу в Excel

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

Формирование чека стоимости покупок

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

Вычисление стоимости товара в Excel

В ячейке E5 задана формула =C5*D5, которая перемножает количество единиц товара и цену за единицу продукта. Для остальных четырех строк будут подобные формулы, только в них поменяется та часть адреса ячейки, которая отвечает за номер строки. Например, шоколад находится в девятой строке таблицы и формула для расчета стоимости в ячейке E9, соответственно примет вид =C9*D9. Формулы можно вводить для каждой строки вручную, но можно провести копирование формулы из ячейки E5 вниз до E9(включительно).

Копирование формулы в Excel

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

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

Для расчета итоговой стоимости, необходимо суммировать содержимое ячеек диапазона E5:E9. Для этого в ячейку E10 запишем формулу =E5+E6+E7+E8+E9.

Для вычисления сдачи в ячейку E12 записываем формулу =E11-E10. В ячейку E11 вписываем число наличных денег.

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

Расчет коммунальных услуг: электричество, газ, вода

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

Использовать вычисляемые столбцы в таблице Excel

Excel

Импорт и анализ данных

Столы

Столы

Использовать вычисляемые столбцы в таблице Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно. ..Меньше

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

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

Создать вычисляемый столбец

1. Создать таблицу. Если вы не знакомы с таблицами Excel, вы можете узнать больше по ссылке: Обзор таблиц Excel.

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

   Советы: 

   • Вы также можете добавить столбец таблицы с вкладки Домашняя страница . Просто нажмите на стрелку Вставить > Вставить столбцы таблицы слева .

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

   В этом случае мы ввели =sum( , затем выбрали столбцы Qtr 1 и Qtr 2 . В результате Excel построил формулу: =СУММ(Таблица1[@[Квартал 1]:[Квартал 2]]) . Это называется формулой структурированной ссылки , которая уникальна для таблиц Excel. Формат структурированной ссылки позволяет таблице использовать одну и ту же формулу для каждой строки. Обычная формула Excel для этого будет =СУММ(B2:C2) , которую затем вам нужно будет скопировать или заполнить до остальных ячеек в столбце

   .

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

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

Примечания:

• org/ListItem»>

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

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

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

• Если вы ввели или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемый столбец, дважды щелкните Отменить . Вы также можете нажать Ctrl + Z дважды

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

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

• org/ListItem»>

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

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

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

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

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

• org/ListItem»>

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

  Примечание. Это исключение не отмечено.

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

Уведомление об ошибке появится только в том случае, если у вас включена опция фоновой проверки ошибок. Если вы не видите ошибку, перейдите к File > Options > Формулы > убедитесь, что установлен флажок Включить фоновую проверку ошибок .

• Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel > Формулы .

• org/ListItem»>

  Если вы используете Mac, перейдите к Excel в строке меню, а затем нажмите Настройки > Формулы и списки > Ошибка проверки .

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

• Включение или выключение вычисляемых столбцов

  1. org/ListItem»>

     На вкладке Файл щелкните Параметры .

     Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel .

  2. Нажмите Проверка .

  3. В разделе Параметры автозамены щелкните Параметры автозамены .

  4. Перейдите на вкладку Автоформат при вводе .

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

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

  Если вы используете Mac, перейдите к Excel в главном меню, затем Настройки > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы .

• Остановить автоматическое создание вычисляемых столбцов

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

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

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

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

См. также

Обзор таблиц Excel

Отформатировать таблицу Excel

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

Суммируйте данные в таблице Excel

Использование формул с таблицами в Excel

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

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

Разделы:

Создание и применение формул к таблицам в Excel

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

Ссылка на данные таблицы извне таблицы

Ссылка на различные разделы таблицы

Заголовки столбцов со специальными символами

Изменение имени таблицы

Примечания

Создание и применение формул к таблицам в Excel

где вы хотите формулу.
1. Теперь мы используем таблицу, поэтому ссылаемся на столбцы по-другому.
   Введите [ , и Excel представит список столбцов в вашей таблице, которые вы можете использовать в своей формуле.
   (ссылки на таблицы ВСЕГДА должны начинаться и заканчиваться открывающей и закрывающей скобкой [ ] )
2. Вы можете щелкнуть один из вариантов в раскрывающемся списке, чтобы выбрать весь столбец данных, или вы можете ввести имя столбца ИЛИ, если вы просто хотите сослаться на текущую строку, введите символ @ , а затем имя столбца. Также не забудьте поставить закрывающую скобку ] после имени столбца.
3. Теперь я хочу разделить продажи на цель, чтобы увидеть, какая часть цели была достигнута.
   Я набираю знак деления / , а затем открываю квадратную скобку [ , чтобы сослаться на другой столбец в таблице, а затем @ , чтобы сделать ссылку на ту же строку, а затем набираю имя столбца Цель и затем закрываю кронштейн ]

4. Нажмите Enter, и формула автоматически скопирует всю таблицу.
5. Просто добавьте необходимое форматирование и все.

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

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

Все ссылки на столбцы таблицы ДОЛЖНЫ начинаться и заканчиваться открывающей и закрывающей квадратной скобкой [] . В противном случае это не будет интерпретироваться как ссылка на таблицу.

Ссылка на текущую строку

Ссылка только на данные из текущей строки, той же строки, в которой вы вводите формулу.

Excel может написать это несколькими способами, в том числе: #This Row и @ . Однако при создании формул следует использовать наиболее общую форму, которая приведена ниже:

=[@ имя столбца ]

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

Ссылка на весь столбец данных

Ссылка на все данные определенного столбца.

=[имя столбца]

Ссылка на данные таблицы извне таблицы

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

Вот синтаксис:

=ИмяТаблицы[ИмяСтолбца]

Если ваша таблица имеет имена SalesData и вы хотите получить общее количество продаж из столбца Sales , вы должны использовать следующую формулу:

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

Ссылка на разные части таблицы

Существуют специальные способы ссылки на определенные части таблицы. Каждая ссылка здесь должна начинаться со знака решетки 9. 0327 # .

Помните, что все ссылки на таблицы также должны начинаться и заканчиваться открывающей и закрывающей квадратной скобкой [ ] .

Вся таблица

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

=[#Все]

Все строки данных

Ссылается только на строки данных в таблице.

=[#Данные]

Заголовки

Ссылается только на заголовки таблицы.

=[#Заголовки]

Строка итогов

Ссылается только на строку итогов в таблице.

=[#Всего]

Заголовки столбцов со специальными символами

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