Как вводить формулу в excel: Добавление формулы сразу в несколько ячеек

Содержание

Формулы массива в Excel

116182
28.10.2012
Скачать пример

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

  1. выделяем ячейку С7


  2. вводим с клавиатуры =СУММ(


  3. выделяем диапазон B2:B5


  4. вводим знак умножения (звездочка)


  5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:


  6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т. е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter.

Пример 2. Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

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


  • вводим функцию транспонирования =ТРАНСП(   


  • в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

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

Для редактирования формулы массива необходимо выделить весь диапазон (A10:h21 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т. е. в диапазон A10:h21 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

  1. выделяем диапазон B2:K11


  2. вводим формулу =A2:A11*B1:K1


  3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

  • Формула массива для извлечения непустых ячеек из диапазона


  • Формула массива для извлечения уникальных ячеек из диапазона


  • Формула массива для извлечения данных из списка (многоразовый ВПР)

 

Правила ввода формул. Компьютер на 100. Начинаем с Windows Vista

Правила ввода формул. Компьютер на 100. Начинаем с Windows Vista

ВикиЧтение

Компьютер на 100. Начинаем с Windows Vista
Зозуля Юрий

Содержание

Правила ввода формул

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

? формула всегда начинается со знака =;

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

• + – сложение;

• – – вычитание;

• * – умножение;

• / – деление;

• % – процент;

•^ – возведение в степень;

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

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

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


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

? для быстрого ввода в формулу имени ячейки достаточно щелкнуть на ней кнопкой мыши;

? чтобы правильно ввести название функции и ее параметры, воспользуйтесь кнопкой Вставить функцию (fx) в строке формул; в появившемся окне Мастера функций нужно выбрать название функции из списка и указать ее аргументы;

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

Детальные описание процесса ввода формул вы найдете в инструкции к практической работе 44.

Данный текст является ознакомительным фрагментом.

6.2.2. Использование формул

6.2.2. Использование формул
Электронную таблицу можно воспринимать как большой и удобный калькулятор. В одни ячейки вы будете вводить значения, я в другие — формулы, при этом вы увидите все значения и готовые результаты расчетов. Удобно? Конечно, ведь на обычном калькуляторе

Строка формул

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

2.

2.7. Создание формул RPTwin

2.2.7. Создание формул RPTwin
RPTwin позволяет преобразовать в формулу любое поле данных. Для этого в диалоге Data Field Properties (см. рис. 2.2.4) следует щелкнуть по кнопке Formula Editor. Возникает диалог Formula Editor (рис. 2.2.10).
Рис. 2.2.10. Диалог Formula EditorПо умолчанию в верхнем поле диалога (Formula)

2.2.9. Использование формул RPTwin

2.2.9. Использование формул RPTwin
Рассмотрим построение отчета RPTwin по модели процессов, изображенной на рис. 2.2.11. Модель описывает процесс изготовления изделия и имеет три уровня декомпозиции. В ней описаны следующие свойства, определяемые пользователем (UDP):уровень

5.3. Использование формул RPTwin

5.3. Использование формул RPTwin
5.3.1. Создание формул RPTwin
RPTwin позволяет преобразовать в формулу любое поле данных. Для этого в диалоге Data Field Properties (см. рис. 5.4) следует щелкнуть по кнопке Formula Editor. Возникает диалог Formula Editor (рис. 5.10).
Рис.5.10. Диалог Formula EditorПо умолчанию в верхнем

5.3.1. Создание формул RPTwin

5.3.1. Создание формул RPTwin
RPTwin позволяет преобразовать в формулу любое поле данных. Для этого в диалоге Data Field Properties (см. рис. 5.4) следует щелкнуть по кнопке Formula Editor. Возникает диалог Formula Editor (рис. 5.10).
Рис.5.10. Диалог Formula EditorПо умолчанию в верхнем поле диалога (Formula:)

5.3.3. Использование формул RPTwin

5.3.3. Использование формул RPTwin
Рассмотрим построение отчета RPTwin по модели процессов, изображенной на рис. 5.11. Модель описывает процесс изготовления изделия и имеет три уровня декомпозиции. В ней описаны следующие свойства, определяемые пользователем (UDP):уровень

Ввод формул

Ввод формул
Для ввода формул предназначена специальная панель инструментов Microsoft Equation, которая отображается в окне Word после запуска редактора формул. Принцип работы состоит в следующем: если необходимо ввести простую формулу, нечто вроде х + 4, вы можете просто набрать ее

Редактирование формул

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

Форматирование формул

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

Стили формул

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

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

6.6. Создание формул
Формула является основным средством для анализа данных. С помощью формул можно складывать, умножать и сравнивать данные, а также объединять значения. Формулы подчиняются определенному синтаксису, в который входят знак равенства (=), вычисляемые

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

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

Вставка математических формул

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

Строка формул

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

Вставка знаков $ в различные существующие формулы Excel: просто и быстро!

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

Содержание

Спешите?

Прокрутите вниз до номера 3 и скопируйте макрос VBA в свою книгу. Пожалуй, это лучшее решение для вставки знаков доллара.

Способ 1: Вставка $-символов вручную

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

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

  1. Введите ячейку, содержащую формулу, нажав F2 на клавиатуре.
  2. Перейдите к ссылке на ячейку, которую вы хотите изменить.
  3. Нажмите F4 на клавиатуре, чтобы добавить (или удалить) знак доллара.

Способ 2. Используйте функцию «Найти и заменить» для преобразования ссылок на ячейки в абсолютные ссылки

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

Вот несколько рекомендаций и примеров. Сначала откройте функцию замены (нажмите Ctrl + H на клавиатуре или нажмите «Найти и заменить» (на ленте «Главная»), а затем «Заменить»).

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

  • Предположим, ваша формула =A1 .
    • Найдите что: =A
    • Замените на: =$A$
  • Если ваша формула =СУММ(C6:C10) (как на скриншоте):
    • Найдите что: (C
    • Замените на: ($C$
    • Опять же, найдите что: :C
    • Замените на: $C 9037 :3 $C 90 :3

Вероятно, этот метод требует проб и ошибок. И это, надо признать, довольно неудобно. Тем не менее, давайте продолжим с методом 3 ниже. под кодом):

 Подпрограмма InsertDollarSigns()
     Затемнить диапазон ячеек как диапазон
     Для каждого диапазона ячеек в ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
         cellRange.Formula = Application.ConvertFormula(cellRange.Formula, xlA1, xlA1, True)
     Следующий
 Конец сабвуфера

Вот как использовать код VBA. Если вам нужна дополнительная помощь по использованию макросов VBA, обратитесь к этой статье.

  1. Скопируйте код VBA сверху.
  2. Откройте редактор VBA, нажав Alt + F11 на клавиатуре.
  3. Вставьте новый модуль: щелкните правой кнопкой мыши имя активной рабочей книги слева.
  4. Перейдите к «Вставить» и нажмите «Модуль».
  5. Вставьте код, нажав Ctrl + V на клавиатуре.
  6. Убедитесь, что на листе Excel выбраны правильные ячейки.
  7. Нажмите «Пуск» в редакторе VBA.

Вставьте код VBA, выберите ячейки и нажмите «Старт» в редакторе VBA. Теперь макрос будет вставлять знаки §.

Вот оно!

Вы хотите повысить производительность в Excel ?

Получите ленту профессора Excel!

Узнать больше

Загрузить бесплатную пробную версию

Добавить более 120 замечательных функций в Excel!

Способ 4. Используйте надстройку Excel

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

Это очень просто: просто выберите ячейку (ячейки) и нажмите кнопку «Изменить ссылку» или маленькую стрелку рядом с ней, чтобы увидеть дополнительные параметры.

С нашей надстройкой Excel, Professor Excel Tools, вы можете изменять типы ссылок одним щелчком мыши (вставлять или удалять знаки доллара).

Эта функция включена в нашу надстройку Excel «Professor Excel Tools»

(без регистрации, загрузка начинается напрямую)

Изображение Gerd Altmann с Pixabay

Опубликовано

В категории формул и функций, VBA с тегами абсолютный, доллар, excel

Хенрик Шиффнер — независимый бизнес-консультант и разработчик программного обеспечения. Живет и работает в Гамбурге, Германия. Помимо того, что он энтузиаст Excel, он любит фотографию и спорт.

Посмотреть все сообщения Хенрика Шиффнера.

формул Excel не вычисляются? Что проверить

Кристи Перри

Категории: Базовый Excel

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

1. Проверка автоматического пересчета

На ленте Формулы посмотрите в крайний правый угол и щелкните Параметры расчета . В раскрывающемся списке убедитесь, что выбрано Автоматически .

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

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

К сожалению, если вы установите ручной режим и забудете об этом, ваши формулы не будут пересчитаны.

2. Проверьте формат ячейки для текста

Выберите ячейку, которая не пересчитывается, и на ленте Home проверьте формат числа. Если формат показывает Text , измените его на Number . Когда ячейка имеет формат Text , Excel не пытается интерпретировать содержимое как формулу.

После того, как вы измените формат, вам нужно будет еще раз подтвердить формулу, щелкнув в Панель формул , а затем нажмите клавишу Enter .

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

3. Проверка циклических ссылок

Найдите в нижней части окна Excel слова ЦИРКУЛЯРНЫЕ ССЫЛКИ .

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

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