Как пользоваться в экселе формулами: Полные сведения о формулах в Excel

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

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

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

Особенности ввода формул

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

1. Выберите нужную ячейку, нажав на нее левой кнопкой мыши. В нашем случае это ячейка B2, где «B» означает второй столбец, а «2» – вторую строку листа.
   Нажимаем на ячейку B2 левой кнопкой мыши

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

   В выделенную ячейку В2 с помощью клавиатуры вводим число, так же и в ячейку В3

3. Найдем сумму указанных ячеек. Выделите ячейку B4 и поставьте там знак равенства, он означает, что дальше будет вводиться текст формулы. Затем кликните левой кнопкой мыши на ячейку B2 – ссылка на ее добавится в верхнюю строку формулы. С помощью клавиатуры поставьте «+». Кликните на следующую ячейку – B3.

   В ячейку B4 вводим знак «=», затем кликаем левой кнопкой мыши на ячейку B2, ставим знак «+», далее кликаем на ячейку B3

4. Вы закончили вводить формулу. Нажмите «Enter», и программа выполнит необходимое вычисление. Обратите внимание – в ячейке появился результат вычисления (в данном случае, сложения положительного и отрицательного чисел), но в верхней строке остался первоначальный вид формулы. Вы сможете внести в нее изменения в любой момент.
   Нажимаем «Enter», программа выполнит вычисления, в верхней панели видим первоначальный вид формулы

5. Выделив ячейку B4, замените в строке формулы «+» на «*» – знак умножения. Нажмите «Enter», и увидите, как изменится конечный результат.

   Левой кнопкой мыши кликаем на ячейку B4, вместо знака «+» ставим знак «*»

   Нажимаем «Enter», формула автоматически пересчитает результат

6. В строке формул можно использовать не только ссылки на ячейки, но и обыкновенные цифры. Давайте прибавим десять к полученному произведению. Тогда формула в ячейке B4 примет вид: «=B2*B3+10».

   Левой кнопкой мыши щелкаем на ячейку B4 в конце формулы добавляем «+» и цифру 10

   Нажимаем «Enter», результат автоматически изменится

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

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

Вернемся к рассмотренному примеру и заменим число в ячейке B2 на большее. Как видите, результат в ячейке B4 кардинально изменился.

Заменив число в ячейке B2, результат в ячейке с формулой автоматически изменился

Формулы, интегрированные в MS Excel

Более сложные выражения сгруппированы по принадлежности к определенной отрасли знаний. Найдите на панели инструментов программы вкладку «Формулы», и сможете сами в этом убедиться.

На панели инструментов программы открываем вкладку «Формулы»

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

Автосумма

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

1. Введите в нужную ячейку текст формулы «=СУММ».

   Вводим в нужную ячейку текст формулы «=СУММ»

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

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

3. Закройте скобку и нажмите «Ввод».

   Закрываем скобку, нажимаем «Enter»

   Результат формулы значения «СУММ»

4. Кроме того, можно воспользоваться специальным пунктом «Автосумма» на панели инструментов. Тогда результат появится сразу под выделенными данными.

   Выделим ячейку и нажимаем на кнопку «Автосумма», нажимаем «Enter»

Среднее арифметическое

Определяется как сумма значений, деленная на их количество.

1. Введите в нужную ячейку текст формулы «=СРЗНАЧ».

   Вводим в нужную ячейку текст формулы «=СРЗНАЧ»

2. Откройте круглую скобку и выделите фрагмент столбца, протянув курсор от первой до последней ячейки с зажатой левой кнопкой мыши.», корень не может быть вписан в формулу одним знаком. Для его нахождения:
   1. Введите в нужную ячейку текст формулы «=КОРЕНЬ».

      Вводим в нужную ячейку текст формулы «=КОРЕНЬ»

   2. После чего выделите ячейку с исходным числом.

      Щелкаем на ячейку с нужным числом, нажимаем «Enter»

   3. Кроме того, функцию можно задать, двигаясь по цепочке «Формулы»-«Математические»-«КОРЕНЬ».

      Формула можно создать двигаясь по цепочке «Формулы»-«Математические»-«КОРЕНЬ»

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

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

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

   Читайте также полезную информацию в статье — «Как в таблице Excel пронумеровать строки». 

   Видео — Как написать формулу в Excel? Обучение азам работы в Excel

   Понравилась статья?
   Сохраните, чтобы не потерять!

   Формулы EXCEL с примерами — Инструкция по применению

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

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

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

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

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

   В программе Excel можно производить самые сложные математические вычисления.

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

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

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

   • максимальные и минимальные значения;
   • средние показатели;
   • проценты;
   • критерий Стьюдента и многое другое.

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

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

   При этом необходимость вводить дополнительные данные и параметры отпадает.

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

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

   Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.

   Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:

   =СУММ(A1;B1).

   Вычисление суммарного значения двух чисел

   Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.

   После введения итог появляется автоматически.

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

   Если одно из значений изменить, то перерасчет происходит автоматически.

   Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.

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

   Сумма дробных чисел

   В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».

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

   Любая формула должна начинаться знаком «=».

   Если вначале не поставить «равно», то программа не сможет выдать необходимое значение, так как данные введены неправильно.

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

   В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

   Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

   В скобках числятся координаты ячеек. Выбирая ячейки, следует не забывать разделять их знаком «;».

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

   =СУММ(A1;B1;C1).

   Формула суммы трех заданных чисел

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

   Например,

   =СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

   Определение диапазона ячеек для формулы сложения

   Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

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

   Совет! Применяя формулу «ПРОИЗВЕД» для определения значения диапазона чисел, можно задать несколько колонок и столбцов. При выборе диапазона =ПРОИЗВЕД(А1:С10), программа выполнит умножение всех значений ячеек в выбранном прямоугольнике. Обозначение диапазона – (А1-А10, В1-В10, С1-С10).

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

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

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

   Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

   =ЕСЛИ(СУММ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

   Решение задачи с помощью комбинированной формулы

   В примере задействованы две функции – ЕСЛИ и СУММ. Первая обладает тремя аргументами:

   • условие;
   • верно;
   • неверно.

   В задаче – несколько условий.

   Во-первых, сумма ячеек в диапазоне А1:С1 меньше 90.

   В случае выполнения условия, что сумма диапазона ячеек будет составлять 88, то программа выполнит указанное действие во 2-ом аргументе функции «ЕСЛИ», а именно в СУММ(А1:С3)*1,4.

   Если в данном случае происходит превышение числа 90, то программа вычислит третью функцию – СУММ(А1:С1)*1,5.

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

   Чтобы научиться выполнять разнообразные расчеты и использовать программу Excel со всеми ее возможностями, можно использовать самоучитель, который можно приобрести или найти на интернет-ресурсах.

   Встроенные функции программы

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

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

   Если выбрать в главном меню раздел «формулы», то здесь сосредоточены все известные функции, в том числе финансовые, инженерные, аналитические.

   Для того чтобы выбрать, следует выбрать пункт «вставить функцию».

   Выбор функции из предлагаемого списка

   Эту же операцию можно произвести с помощью комбинации на клавиатуре — Shift+F3 (раньше мы писали о горячих клавишах Excel).

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

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

   Мастер функций

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

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

   Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.

   Выбор функции и заполнение полей

   Далее нужно заполнить поля чисел и выбрать условие. Таким же способом можно найти самые различные функции, в том числе «СУММЕСЛИ», «СЧЕТЕСЛИ».

   Функция ВПР

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

   После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.

   Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий. Задача – по предложенному номеру найти фамилию.

   Применение функции ВПР

   Формула показывает, что первым аргументом функции является ячейка С1.

   Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.

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

   Вычисление заданной фамилии с помощью функции ВПР

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

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

   Поиск фамилии с пропущенными номерами

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

   Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».

   Округление чисел с помощью функций

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

   А полученное значение можно использовать при расчетах в других формулах.

   Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.

   Первый аргумент – 76,375, а второй – 0.

   Округление числа с помощью формулы

   В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».

   Округление происходит до целого числа. В нашем случае до 77 или 76.

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

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

   Вся правда о формулах программы Microsoft Excel 2007

   Формулы EXCEL с примерами — Инструкция по применению

   ---

    

   « Предыдущая запись Следующая запись »

   Как в excel ввести формулу Excelka.ru

   Создание простой формулы в Excel

   Можно создать простую формулу для сложения, вычитания, умножения и деления числовых значений на листе. Простые формулы всегда начинаются со знака равенства (=), за которым следуют константы, т. е. числовые значения, и операторы вычисления, такие как плюс (+), минус (—), звездочка (*) и косая черта (/).

   В качестве примера рассмотрим простую формулу.

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

   Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

   В нашем примере введите =1+1.

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

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

   Нажмите клавишу ВВОД (Windows) или Return (Mac).

   Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.

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

   Для быстрого суммирования чисел в столбце или строке можно использовать кнопку «Автосумма». Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная, а затем нажмите клавишу ВВОД (Windows) или Return (Mac).

   Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

   Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT+ += (Mac), и Excel автоматически вставит функцию СУММ.

   Пример: чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая находится прямо под столбцом с числами. Затем нажмите кнопку Автосумма. В ячейке В7 появляется формула, и Excel выделяет ячейки, которые суммируются.

   Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

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

   Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

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

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

   Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).

   Работа в Excel с формулами и таблицами для чайников

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

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

   Формулы в Excel для чайников

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

   В Excel применяются стандартные математические операторы:

   Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

   Ссылки можно комбинировать в рамках одной формулы с простыми числами.

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

   В нашем примере:

   1. Поставили курсор в ячейку В3 и ввели =.
   2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
   3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

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

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

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

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

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

   1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
   2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
   3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

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

   Ссылки в ячейке соотнесены со строкой.

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

   Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

   1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
   2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
   3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

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

   1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
   2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
   3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

   При создании формул используются следующие форматы абсолютных ссылок:

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

   Как составить таблицу в Excel с формулами

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

   Простейшие формулы заполнения таблиц в Excel:

   1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
   2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
   3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
   4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

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

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

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

   3. В том случае, если вам требуется включить в формулу данные из текущей таблицы, то вставляйте в формулу ссылку на ячейку с этими значениями. Если вам нужно вычислить значения умножения числа, которое находится в ячейке А1 на число из ячейки А2, то формула должна выглядеть следующим образом: =А1*А2. Для использования ссылки достаточно установить курсор в нужное место в формуле и кликнуть по нужной ячейке.

   Инструмент «Мастер функций»

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

   Инструмент «Вставить функцию»

   5. Нажмите на пункт меню «Категория», в открывшемся окне укажите значение «Статистические». Из открывшегося списка выберите параметр СРЗНАЧ. Для удобства выбора под каждой из функций имеется их описание.

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

   7. Нажмите кнопку ОК и в определенной ячейке появится результат вычисления функции.

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

   Видео: Расчеты в Excel

   Как вставить формулу в таблицу Excel

   Для полного знакомства с электронными таблицами требуется понять, как сделать формулу в Excel. Э.

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

   Какие функции доступны для пользователя Эксель:

   • арифметические вычисления;
   • применение готовых функций;
   • сравнения числовых значений в нескольких клетках: меньше, больше, меньше (или равно), больше (или равно). Результатом будет какое-то из двух значений: «Истина», «Ложь»;
   • объединение текстовой информации из двух и более клеток в единое целое.

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

   Основы работы с формулами в Excel

   Чтобы вникнуть в то, как вставить формулу в Эксель, следует понять базовые принципы работы с математическими выражениями в электронных табличках Microsoft:

   1. Каждая из них должна начинаться со значка равенства («=»).
   2. В вычислениях допускается использование значений из ячеек, а также функции.
   3. Для применения стандартных математических знаков операций следует вводить операторы.
   4. Если происходит вставка записи, то в ячейке (по умолчанию) появляются данные итоговых вычислений.
   5. Увидеть конструкцию пользователь может в строчке над таблицей.

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

   «Эксель» допускается использовать, как настольный калькулятор, при этом сложные формулы могут состоять из элементов:

   • постоянных чисел-констант;
   • операторов;
   • ссылок на иные клетки-ячейки;
   • математических функций;
   • имён диапазонов;
   • встроенных формул.

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

   Как фиксировать ячейку

   Для фиксации значения в клетке применяют стандартный символ «$». Ставить знак допускается с помощью «быстрой» клавиши F4. Используют три варианта фиксации:

   Чтобы предотвратить сдвиг ячейки по горизонтали и по вертикали используют обозначения вида: $A$1. Это может понадобиться, если в расчётах используется постоянное значение, например, расход топлива или курс валют.

   Если требуется закрепление по вертикали, то обозначение будет таким: $A1.

   Для фиксации по горизонтали понадобится указать адрес в виде: A$1.

   Делаем таблицу в Excel с математическими формулами

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

   • поставить значок «=» в клетку — здесь будут отображаться результаты вычислений;
   • далее выделить клетки с исходными значениями и указание нужного оператора – это знаки: «+», «-», «*», «/»;
   • повторяем то же с другими «клеточками», которые будут участвовать в вычислениях;
   • нажать клавишу «равно».

   Это самый доступный метод создания формулы.

   Пошаговый пример №1

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

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

   Выделить клетку в 1-ой графе и щелкнуть правой кнопкой мышки.

   Нажать «Вставить». Или использовать комбинацию «CTRL+ПРОБЕЛ» – чтобы выделить полный столбик листа. Затем кликнуть: «CTRL+SHIFT+»=»» – для вставки столбца.

   Новую графу рекомендуется назвать, например, – «No п/п».

   Ввод в 1-ю клеточку «1», а во 2-ю – «2».

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

   Аналогичным способом допускается запись дат. Но промежутки между ними должны быть одинаковые, по принципу: день, месяц и год.

   Ввод в первую клетку информации: «окт.18», во вторую – «ноя.18».

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

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

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

   Пошаговый пример №2

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

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

   Чтобы сгенерировать результаты вычислений в процентах (в Эксель), не требуется умножать полученное ранее частное на 100. Следует выделить клетку с нашим результатом и нажать «Процентный формат». Также допускается использование комбинации горячих клавиш: «CTRL+SHIFT+5».

   Теперь необходимо скопировать формулу на столбец: будет изменяться первое значение в нашей формуле (относительная ссылка). А второе (абсолютная ссылка) останется прежним. Проверка правильности вычислений – поиск итога. Если равен 100%, то вычисления выполнены верно.

   Заключение

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

   Если вдруг получается неверный результат, то наверняка присутствуют ошибки. Присутствует вероятность, что выражения записаны неверно или указаны некорректные ссылки на «клетки». Важно понять: данные следует записывать скрупулёзно. Нельзя не учитывать, что стандартные математические функции пишутся не на английском, а только на русском. Кроме того: формулы следует записывать, начиная с символа «=» (равно). Часто неопытные составители таблиц забывают об этом.

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

   Формулы в excel для чайников

   Самоучитель по Microsoft Excel для чайников

   ​Смотрите также​ на составляющие. Выделим​​ Согласитесь, что при​ функции – становится​Таким же способом можно​Отредактировать значения можно и​ во вторую –​ ячейку. То есть​ ячейку, достаточно щелкнуть​ вместо ввода «A1:C2»​ формулу из ячейки​А2​ данную формулу и​ (=). Далее, выделяем​ Excel​ Excel​ Excel​

   ​Самоучитель по работе в​ фрагмент​ таком подходе формула​ доступным порядок заполнения​ копировать строки.​ через строку формул.​ «2». Выделяем первые​ при автозаполнении или​ по этой ячейке.​ вручную выделите мышью​A4​.​ для других строк​ ячейку с количеством​Подходы к редактированию формул​Условное форматирование​Перенос текста и объединение​ Excel для чайников​$A$2:$A$25=$H$7​

   • ​ становится «прозрачной», и​ аргументов. Чтобы завершить​Удалим столбец: выделим его​
   • ​ Выделяем ячейку, ставим​ две ячейки –​ копировании константа остается​
   • ​В нашем примере:​ этот диапазон.​в​Совет:​ таблицы.​ товара. Как видим,​ массива в Excel​
   • ​Условное форматирование в Excel​ ячеек в Excel​позволит Вам легко​и нажмём​ становится очевиден результат​ введение аргументов, нужно​ – правой кнопкой​ курсор в строку​ «цепляем» левой кнопкой​

   Раздел 1: Основы Excel

   1. ​ неизменной (или постоянной).​
      • ​Поставили курсор в ячейку​
      • ​Урок подготовлен для Вас​
      • ​B4​
      • ​Вместо того, чтобы​Нужно заметить, что не​
   2. ​ ссылка на неё​Настройка интерфейса​
      • ​Сводные таблицы и анализ​Форматирование ячеек​
      • ​ понять и усвоить​
   3. ​F9​ функции​
      • ​ закрыть скобку и​ мыши – «Удалить».​
      • ​ формул, редактируем текст​
      • ​ мыши маркер автозаполнения​Чтобы указать Excel на​
   4. ​ В3 и ввели​
      • ​ командой сайта office-guru.ru​протягиванием. Выделите ячейку​
      • ​ вручную набирать​
      • ​ обязательно все данные​ сразу же появляется​
      • ​Как настроить Ленту в​
      • ​ данных​Настройка шрифта в Excel​
   5. ​ базовые навыки работы​. Это логическое выражение​
      • ​ПОИСКПОЗ()​ нажать Enter.​ Или нажав комбинацию​
      • ​ (число) – нажимаем​ – тянем вниз.​ абсолютную ссылку, пользователю​
      • ​ =.​Источник: http://www.excel-easy.com/introduction/formulas-functions.html​А4​
      • ​А1​ формулы должны располагаться​
   6. ​ после знака равно.​
      • ​ Excel 2013​
      • ​Общие сведение о сводных​Выравнивание текста в ячейках​
      • ​ в Excel, чтобы​ даёт значение​
      • ​Чтобы привести формулу в​
   7. ​Аргумент функции КОРЕНЬ –​ горячих клавиш: CTRL+»-«(минус).​
      • ​ Enter.​По такому же принципу​
      • ​ необходимо поставить знак​Щелкнули по ячейке В2​Перевела: Ольга Гелих​
      • ​, зажмите её нижний​
   8. ​и​
      • ​ в соседних ячейках,​ Затем, после координат​
      • ​Режим сенсорного управления Лентой​ таблицах в Excel​ Excel​
   9. ​ затем уверенно перейти​
      • ​ИСТИНА​ первоначальный вид, нажмём​
      • ​ ссылка на ячейку​Чтобы вставить столбец, выделяем​
      • ​Для удаления значения ячейки​ можно заполнить, например,​ доллара ($). Проще​

   Раздел 2: Формулы и функции

   1. ​ – Excel «обозначил»​
      • ​Автор: Антон Андронов​ правый угол и​А2​
      • ​ или в границах​ ячейки, нужно вставить​
      • ​ в Excel 2013​
   2. ​Сведение данных, фильтры, срезы​
      • ​Границы, заливка и стили​ к более сложным​
      • ​, если значение ячейки​ESC​
   3. ​ A1:​
      • ​ соседний справа (столбец​
      • ​ используется кнопка Delete.​
      • ​ даты. Если промежутки​ всего это сделать​
   4. ​ ее (имя ячейки​
      • ​Формула предписывает программе Excel​ протяните до ячейки​
      • ​, просто кликните по​
      • ​ одной таблицы. Они​
      • ​ арифметический знак. В​

   Раздел 3: Работа с данными

   1. ​Стили ссылок в Microsoft​ и сводные диаграммы​
      • ​ ячеек в Excel​ темам. Самоучитель научит​
      • ​ диапазона​.​ВВОД – программа нашла​
   2. ​ всегда вставляется слева),​
      • ​Чтобы переместить ячейку со​ между ними одинаковые​
      • ​ с помощью клавиши​
      • ​ появилось в формуле,​
   3. ​ порядок действий с​
      • ​В4​ ячейкам​
      • ​ могут находиться в​
   4. ​ данном случае, это​ Excel​
      • ​Анализ «что если” в​Числовое форматирование в Excel​
   5. ​ Вас пользоваться интерфейсом​
      • ​$A$2:$A$25​Ещё немного потренируемся: выделим​
   6. ​ квадратный корень из​
      • ​ нажимаем правую кнопку​ значением, выделяем ее,​
      • ​ – день, месяц,​ F4.​
      • ​ вокруг ячейки образовался​ числами, значениями в​

   Раздел 4: Расширенные возможности Excel

   1. ​. Это намного проще​A1​
      • ​ другой таблице, или​
      • ​ будет знак умножения​
      • ​Хотите узнать об Excel​ Excel​
   2. ​Основные сведения о листе​ Excel, применять формулы​
      • ​равно выбранному названию​ фрагмент​ числа 40.​
   3. ​ мыши – «Вставить»​
      • ​ нажимаем кнопку с​
   4. ​ год. Введем в​Создадим строку «Итого». Найдем​
      • ​ «мелькающий» прямоугольник).​ ячейке или группе​
      • ​ и дает тот​и​
      • ​ даже на другом​ (*). Далее, кликаем​

   Раздел 5: Продвинутая работа с формулами в Excel

   1. ​ еще больше?​Решаем задачи с помощью​
      • ​ Excel​ и функции для​
      • ​ отдела​$B$2:$B$37​Эта статья будет интересна​
      • ​ — «Столбец». Комбинация:​ ножницами («вырезать»). Или​
   2. ​ первую ячейку «окт.15»,​ общую стоимость всех​
      • ​Ввели знак *, значение​ ячеек. Без формул​ же результат!​
      • ​A2​ листе документа. Программа​ по ячейке, где​
      • ​Специально для Вас​ логических функций​
      • ​Переименование, вставка и удаление​ решения самых различных​
   3. ​$H$7​, нажмём​
      • ​ тем, кто пытается​ CTRL+SHIFT+»=»​ жмем комбинацию CTRL+X.​
      • ​ во вторую –​ товаров. Выделяем числовые​ 0,5 с клавиатуры​
      • ​ электронные таблицы не​Результат:​.​ все равно корректно​
      • ​ размещаются данные с​ мы припасли целых​Как задать простое логическое​
      • ​ листа в Excel​ задач, строить графики​
   4. ​, и​
      • ​F9​ понять работу сложных​
      • ​Чтобы вставить строку, выделяем​ Вокруг ячейки появляется​
      • ​ «ноя.15». Выделим первые​ значения столбца «Стоимость»​ и нажали ВВОД.​
   5. ​ нужны в принципе.​
      • ​Формула в ячейке​Измените значение ячейки​
      • ​ подсчитает результат.​ ценой единицы товара.​
      • ​ два простых и​ условие в Excel​
      • ​Копирование, перемещение и изменение​ и диаграммы, работать​
   6. ​ЛОЖЬ​, затем выделим функцию​
      • ​ формул.​ соседнюю снизу. Комбинация​ пунктирная линия. Выделенный​
      • ​ две ячейки и​ плюс еще одну​Если в одной формуле​
      • ​Конструкция формулы включает в​B4​A1​Хотя, основной задачей программы​
      • ​ Арифметическая формула готова.​ полезных самоучителя: 300​Используем логические функции Excel​
      • ​ цвета листа в​ со сводными таблицами​
   7. ​, если не равно.​ПОИСКПОЗ(I3;$E$2:$E$37;0)​
      • ​В Excel есть инструменты,​ клавиш: SHIFT+ПРОБЕЛ чтобы​
      • ​ фрагмент остается в​ «протянем» за маркер​
      • ​ ячейку. Это диапазон​ применяется несколько операторов,​
      • ​ се

   Создайте простую формулу в Excel

   Вы можете создать простую формулу для сложения, вычитания, умножения или деления значений на рабочем листе. Простые формулы всегда начинаются со знака равенства ( = ), за которым следуют константы, представляющие собой числовые значения, и операторы вычисления, такие как плюс ( + ), минус (–), звездочка ( * ) или косая черта. (/) знаков.

   Рассмотрим пример простой формулы.

   1. На листе щелкните ячейку, в которую вы хотите ввести формулу.

   2. Введите = (знак равенства), а затем константы и операторы (до 8192 символов), которые вы хотите использовать в вычислениях.

      В нашем примере введите = 1 + 1 .

      Примечания:

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

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

   3. Нажмите Введите (Windows) или Верните (Mac).

   Возьмем еще один вариант простой формулы. Введите = 5 + 2 * 3 в другую ячейку и нажмите . Введите или . Верните .Excel умножает последние два числа и добавляет к результату первое число.

   Использовать автосумму

   Автосумма можно использовать для быстрого суммирования столбца, строки или чисел. Выберите ячейку рядом с числами, которые вы хотите суммировать, щелкните AutoSum на вкладке Home , нажмите Введите (Windows) или Return (Mac), и все!

   При нажатии Автосумма Excel автоматически вводит формулу (которая использует функцию СУММ) для суммирования чисел.

   Примечание: Вы также можете ввести ALT + = (Windows) или ALT + + = (Mac) в ячейку, и Excel автоматически вставит функцию СУММ.

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

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

   Примечания:

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

   • Создав формулу, вы можете скопировать ее в другие ячейки вместо того, чтобы вводить ее снова и снова.Например, если вы скопируете формулу из ячейки B7 в ячейку C7, формула в C7 автоматически подстраивается под новое расположение и вычисляет числа в C3: C6.

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

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

   Примечание: Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Введите (Windows) или Return (Mac).

   Данные
   2
   5
   Формула	Описание	Результат
   = A2 + A3	Складывает значения в ячейки A1 и A2.	= A2 + A3
   = A2-A3	Вычитает значение в ячейке A2 из значения в A1	= A2-A3
   = A2 / A3	Делит значение в ячейке A1 на значение в A2	= A2 / A3
   = A2 * A3	Умножает значение в ячейке A1 на значение в A2. A3
   Формула	Описание	Результат
   = 5 + 2	Добавляет 5 и 2	= 5 + 2
   = 5-2	Вычитает 2 из 5	= 5-2
   = 5/2	Делит 5 на 2	= 5/2
   = 5 * 2	5 умножает на 2	= 5 * 2
   = 5 ^ 2	Возводит 5 в 2-ю степень	= 5 ^ 2

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

   Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

   См. Также

   Обзор формул в Excel

   СУММ (функция СУММ)

   Как отображать формулы в Excel

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

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

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

   Как отображать формулы в Excel

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

   1. Параметр «Показать формулы» на ленте Excel

   На листе Excel перейдите на вкладку Формулы > группа Аудит формул и нажмите кнопку Показать формулы .

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

   2. Параметр «Показывать формулы в ячейках вместо их результатов»

   В Excel 2010, Excel 2013 и Excel 2016 перейдите к Файл > Параметры .В Excel 2007 нажмите Кнопка Office > Параметры Excel .

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

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

   3. Ярлык Excel для отображения формул

   Самый быстрый способ просмотреть все формулы в электронной таблице Excel — это нажать следующую комбинацию клавиш: Ctrl + `

   Клавиша с надстрочным ударением (`) — это самая дальняя клавиша слева в ряду с цифровыми клавишами (рядом с клавишей с цифрой 1).

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

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

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

   Как распечатать формулы в Excel

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

   Почему Excel показывает формулу, а не результат?

   Случалось ли вам когда-нибудь вводить формулу в ячейку, нажимать клавишу Enter… а Excel по-прежнему показывает формулу вместо результата? Не волнуйтесь, с вашим Excel все в порядке, и мы немедленно исправим эту ошибку.

   Как правило, Microsoft Excel может отображать формулы вместо вычисленных значений по следующим причинам:

   1. Возможно, вы случайно активировали режим Показать формулы, щелкнув соответствующую кнопку на ленте или нажав сочетание клавиш CTRL + `.Чтобы вернуть рассчитанные результаты, просто выключите кнопку Показать формулы или снова нажмите CTRL + `.
   2. Возможно, вы случайно ввели пробел или одинарную кавычку (‘) перед знаком равенства в формуле:
      

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

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

   Чтобы исправить эту ошибку, выберите ячейку, перейдите на вкладку Home > Number group и установите форматирование ячейки на General и, находясь в ячейке, нажмите F2 и ENTER.

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

   Вас также может заинтересовать

   формул Excel для начинающих

   Математические игры

   • Математические игры
   • Значение места
   • Вероятность

   Обучение набору текста

   • Детские игры для набора текста
   • Учебник набора текста
   • Набор текста

   Орфография и словарь

   • Орфографические игры
   • Кроссворды
   • Поиск слов

   Обучение работе с MS Office

   • Учебник по Excel
   • Формулы Excel
   • Обучение работе с Microsoft Word

   Грамматика и письмо

   • Выучить грамматику английского языка
   • Курс творческого письма

   Жизненные навыки для детей

   • Хорошие учебные привычки
   • Постановка целей
   • Финансовая грамотность

   Расслабляющая внимательность

   • Красивые игры

   Информация о сайте

   • Около
   • Контакт
   • Политика конфиденциальности

   Скрыть

   Основы Excel Основы Excel
   • Форматировать ячейки
   • Задания
   • Редактировать ячейки
   • Строки и столбцы
   • Найти и заменить
   • Ручка заполнения
   • Сочетания клавиш в Excel
   Формулы и функции Формулы и функции
   • Функции суммирования, среднего и счетчика
   • Быстрые функции
   • Заявление IF
   • Функция VLOOKUP
   • Функция Sumif
   • Формулы Excel
   Особые возможности Особые возможности

   Как использовать функцию СЧЁТЕСЛИ

   Числовые критерии | Текстовые хитрости | Подсчет логических значений | Подсчитывать ошибки | И критерии | Или критерии | Подробнее о Countif | Граф Магия

   Мощная функция СЧЁТЕСЛИ в Excel подсчитывает ячейки на основе одного критерия.Эта страница содержит множество простых примеров COUNTIF.

   Числовые критерии

   Используйте функцию СЧЁТЕСЛИ в Excel для подсчета ячеек, которые равны значению, подсчета ячеек, которые больше или равны значению, и т. Д.

   1. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, равное 20.

   2. Следующая функция СЧЁТЕСЛИ дает точно такой же результат.

   3. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые больше или равны 10.

   4. Следующая функция СЧЁТЕСЛИ дает точно такой же результат.

   Объяснение: оператор & соединяет символ «больше или равно» и значение в ячейке C1.

   5. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые равны , а не равным 7.

   6. Функции СЧЁТЕСЛИ ниже подсчитывают количество ячеек, равное 3 или 7.

   Уловки с текстом

   Используйте функцию СЧЁТЕСЛИ в Excel и несколько приемов для подсчета ячеек, содержащих определенный текст. Всегда заключайте текст в двойные кавычки.

   1. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих ровно звезду.

   2. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих ровно звездочку + 1 символ. Вопросительный знак (?) Соответствует ровно одному символу.

   3. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые содержат ровно звездочку + последовательность из нуля или более символов. Звездочка (*) соответствует серии из нуля или более символов.

   4.Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые каким-либо образом содержат звезду.

   5. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих текст.

   Логические значения счета

   Используйте функцию СЧЁТЕСЛИ в Excel для подсчета логических значений (ИСТИНА или ЛОЖЬ).

   1. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих логическое значение ИСТИНА.

   2. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих логическое значение ЛОЖЬ.

   Ошибок подсчета

   Используйте функцию СЧЁТЕСЛИ в Excel для подсчета конкретных ошибок.

   1. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, содержащих # ИМЯ? ошибка.

   2. Формула массива ниже подсчитывает общее количество ошибок в диапазоне ячеек.

   Примечание: завершите формулу массива, нажав CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки {}. Посетите нашу страницу о подсчете ошибок, чтобы получить подробные инструкции о том, как создать эту формулу массива.

   и критерии

   Подсчет с критериями И в Excel очень прост. Функция СЧЁТЕСЛИМН (с буквой S в конце) в Excel подсчитывает ячейки на основе двух или более критериев.

   1.Например, чтобы подсчитать количество строк, содержащих Google и Stanford, просто используйте функцию СЧЁТЕСЛИМН.

   или критерии

   Подсчет с использованием критерия Или в Excel может быть непростым.

   1. Функции СЧЁТЕСЛИ ниже подсчитывают количество ячеек, содержащих Google или Facebook (один столбец). Пока нет ракетостроения.

   2. Однако, если вы хотите подсчитать количество строк, содержащих Google или Stanford (два столбца), вы не можете просто использовать функцию СЧЁТЕСЛИ дважды (см. Рисунок ниже).

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

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

   Примечание: завершите формулу массива, нажав CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки {}. Посетите нашу страницу о подсчете с использованием критериев или для получения подробных инструкций о том, как создать эту формулу массива.

   Подробнее о Countif

   Функция СЧЁТЕСЛИ — отличная функция. Давайте взглянем на еще несколько интересных примеров.

   1. Функция СЧЁТЕСЛИ ниже использует именованный диапазон. Именованный диапазон Возраст относится к диапазону A1: A6.

   2. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, которые меньше среднего возраста (32,2).

   3.Чтобы подсчитать ячейки между двумя числами, используйте функцию СЧЁТЕСЛИМН (с буквой S в конце).

   4. Используйте функцию СЧЁТЕСЛИ, чтобы подсчитать, сколько раз каждое значение встречается в названном диапазоне Ages.

   Примечание: ячейка B2 содержит формулу = СЧЁТЕСЛИ (возраст, A2), ячейка B3 = СЧЁТЕСЛИ (возраст, A3) и т. Д.

   5. Добавьте функцию ЕСЛИ, чтобы найти дубликаты.

   Совет: используйте СЧЁТЕСЛИ и условное форматирование для поиска и выделения дубликатов в Excel.

   Магия графов

   Функция СЧЁТЕСЛИ не может подсчитать, сколько раз встречается конкретное слово в ячейке или диапазоне ячеек. Все, что нам нужно, это немного волшебства!

   1. Приведенная ниже формула подсчитывает, сколько раз слово «собака» встречается в ячейке A1.

   2. Приведенная ниже формула подсчитывает, сколько раз слово «собака» встречается в диапазоне A1: A2.

   Примечание: посетите нашу страницу о подсчете слов, чтобы узнать больше об этих формулах.

   Как суммировать в Excel Примеры и видео

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

   Обзор

   : 7 способов подвести итоги в Excel

   Чтобы увидеть краткий обзор 7 способов суммирования в Excel, вы можете посмотреть это короткое слайд-шоу или посмотрите пошаговые инструкции в видео.

   Загрузите 7 способов Обобщите образец книги, чтобы вы могли следить за видео.На молнии Файл имеет формат xlsx и не содержит макросов.

   Суммирование диапазона ячеек — функция СУММ

   Самый быстрый способ суммировать диапазон ячеек — использовать кнопку «Автосумма». Он автоматически вводит функцию СУММ в выбранную ячейку.

   Функция СУММ суммирует одно или несколько чисел в диапазоне ячеек.

   1. Выберите пустую ячейку в строке под ячейками, которые вы хотите сумма, ячейка A5 в этом примере.

   2. Щелкните команду AutoSum на вкладке «Главная» ленты,
      или используйте сочетание клавиш: Alt + =

   3. В активной ячейке появится формула СУММ со ссылкой на ячейки выше. На снимке экрана ниже представлена ​​формула СУММ в ячейка A5: = СУММ (A1: A4)
      ПРИМЕЧАНИЕ. Если все ячейки не включены автоматически, вы можете расширить рамка, чтобы выбрать их.

   4. Нажмите клавишу Enter, чтобы завершить ввод.

   Настройка функции СУММ

   Вместо использования команды Автосумма для вставки функции СУММ вы можете введите функцию вручную.

   Настройка функции СУММ (синтаксис): СУММ (число1 , [число2], …).

   • Обязательный аргумент: число1
   • Он также имеет необязательные аргументы (заключены в квадрат скобки): [число2] ,..

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

   В приведенном выше примере ( = СУММ (A1: A4) ) есть один аргумент — ссылка на ячейки A1: A4.

   Исправьте числа, которые не складываются

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

   Для письменных инструкций и других способов решения проблемы перейдите на страницу «Числа не складываются».

   Всего диапазон ячеек

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

   Краткий общий итог для диапазона ячеек

   1. Выберите диапазон ячеек и пустую строку под диапазоном, и пустые ячейки в столбце справа (ячейки A1: D5 в примере ниже)
   2. Нажмите кнопку «Автосумма» на вкладке «Главная» ленты.Формула СУММ будет автоматически вводиться для каждой суммы.

   Итого

   Чтобы увидеть промежуточную сумму в каждой строке списка Excel, вы можете использовать СУММ, при этом начальная строка заблокирована как абсолютная ссылка. Ниже приведены несколько другие шаги,

   Промежуточная сумма в списке рабочих листов

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

   Промежуточный итог — Рабочий лист

   Для списка рабочего листа (не именованной таблицы Excel) выполните следующие действия, чтобы создать промежуточную сумму. На снимке экрана ниже суммы вводятся в столбце C, а текущий Итого рассчитывается в столбце D.

   • Введите эту формулу в ячейку D2.
   • Скопируйте формулу в ячейку D6

   Как работает формула

   В формуле используется абсолютная ссылка на строку 2 в качестве отправной точки. — C $ 2 — и относительная ссылка на конечную точку — C2

   Это гарантирует, что начальная точка не изменится при копировании формула до строк ниже.Вот формула в ячейке D6 — начальная точка осталась прежней, а конечная точка находится в текущем ряд — C6

   Промежуточная сумма в таблице Excel

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

   Проблема с промежуточным итогом

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

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

   До добавления строки в ячейке D6 была следующая формула:

   Как только в строке 7 была начата следующая запись, формула в D6 автоматически изменилась. Теперь у него неправильная конечная ссылка на C7 вместо C6:

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

   Формула промежуточной суммы для именованной таблицы

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

   На снимке экрана ниже суммы вводятся в столбце C, а текущая Итого рассчитывается в столбце D.

   • Введите эту формулу в ячейку D2.
   • Формула автоматически заполняется до ячейки D6
   • Во всех ячейках столбца D отображается одинаковая формула

   Как работает формула

   В формуле в качестве отправной точки используется абсолютная ссылка на ячейку заголовка. — C $ 1

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

   Для конечной точки есть ссылка на структурированную таблицу — [@Amt]

   • Это ссылка на ячейку Amt в текущей строке
   • С этой ссылкой на ячейку таблицы нет проблем при добавлении новых строк в таблицу Excel

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

   Суммировать диапазон ячеек — OFFSET

   Если вы вставите строку непосредственно над функцией СУММ в предыдущем примере, новая строка не может быть включена в СУММ.Он может продолжать суммировать ячейки A1: A4 и игнорируйте A5. Чтобы новые строки были включены в общую сумму, вы можете использовать функцию СМЕЩЕНИЕ с функцией СУММ.

   1. Выберите ячейку A5.
   2. Введите следующую формулу:
      = СУММ (A1: СМЕЩЕНИЕ (A5, -1,0))
   3. Нажмите клавишу Enter, чтобы завершить ввод.
   4. Вставить строку над строкой 5
   5. Введите число в ячейку A5, и оно будет включено в итог в ячейка A6

   Ячейки суммы, соответствующие критериям — СУММЕСЛИ

   Вот 3 способа суммирования ячеек, соответствующих критерию:

   — Точное соответствие критерию
   — Критерий соответствия в строка
   — критерий соответствия с использованием оператор

   Точное соответствие критерию

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

   1. Выберите ячейку, в которой вы хотите увидеть сумму
   2. Введите знак равенства (=), чтобы начать формулу
   3. Тип: СУММЕСЛИ (
   4. Выберите ячейки, содержащие значения для проверки критерия. В этом примере будут проверяться ячейки A2: A10
   5. Введите запятую для разделения аргументов
   6. Введите критерий.В этом примере вы проверяете текст, поэтому введите слово в двойных кавычках: «Pen»
      Примечание: верхний и нижний регистр обрабатываются одинаково
      
   7. Введите запятую для разделения аргументов
   8. Выберите ячейки, содержащие значения для суммирования. В этом примере ячейки B2: B10 будут суммированы
   9. Заполненная формула:
      = СУММЕСЛИ (A2: A10, «Pen», B2: B10)
      
   10. Нажмите клавишу Enter, чтобы завершить ввод

   Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на в камеру.Например, формула на шаге 9 выше может быть изменена на:
   = СУММЕСЛИ (A2: A10, B12, B2: B10)
   , если ячейка B12 содержит текст — pen .

   Критерий соответствия в строке

   Вы можете добавлять ячейки, содержащие критерий, как часть содержимого ячейки. В этом примере все заказы Pen, Gel Pen и Pencil будут суммированы, потому что они содержат строку «ручка».

   1. Выберите ячейку, в которой вы хотите увидеть итог (ячейка A12 в этом пример)
   2. Введите знак равенства (=), чтобы начать формулу
   3. Тип: СУММЕСЛИ (
   4. Выберите ячейки, содержащие значения для проверки критерия.В этом примере будут проверяться ячейки A2: A10
   5. Введите запятую для разделения аргументов
   6. Введите критерий. В этом примере вы проверяете текст, поэтому введите слово в двойные кавычки с одной или несколькими подстановочными знаками звездочки (*) символы: «* Pen *»
      Примечание: прописные и строчные буквы обрабатываются одинаково
      
   7. Введите запятую для разделения аргументов
   8. Выберите ячейки, содержащие значения для суммирования.В этом примере ячейки B2: B10 будут суммированы
   9. Введите закрывающую скобку. Заполненная формула: = СУММЕСЛИ (A2: A10, «* Pen *», B2: B10)
      
   10. Нажмите клавишу Enter для завершения ввода
   11. Результатом будет 53, всего строк, содержащих строку, «Ручка»

   Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на в камеру.Например, формулу на шаге 9 выше можно изменить на:
   = СУММЕСЛИ (A2: A10, «*» & B12 & «*», B2: B10)
   , если в ячейке B12 содержится текст — pen .

   Критерий и оператор

   Вы можете использовать оператор с критерием. В приведенных ниже примерах см. как объединить их в формуле СУММЕСЛИ.

   Пример 1

   Пример 2

   Пример 1 — Сумма строк больше установленной суммы

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

   1. Выберите ячейку, в которой вы хотите увидеть сумму
   2. Введите знак равенства (=), чтобы начать формулу
   3. Тип: СУММЕСЛИ (
   4. Выберите ячейки, содержащие значения для проверки критерия. В этом примере будут проверяться ячейки B2: B10
   5. Введите запятую для разделения аргументов
   6. Введите критерий. В этом примере вы проверяете строки, в которых количество посещений больше или равно 10.Оператор> = используется перед числом, а весь критерий заключен в двойные цитаты.
   7. Введите запятую для разделения аргументов
   8. Выберите ячейки, содержащие значения для суммирования
   9. Введите закрывающую скобку. Заполненная формула:
      = СУММЕСЛИ (B2: B10, «> = 10», C2: C10)
      
   10. Нажмите клавишу Enter для завершения ввода
   11. Результатом будет 183 строк с десятью или более торговыми представителями.

   Примечание : вместо того, чтобы вводить критерий в формуле, вы можете ссылаться на в камеру. Например, формула на шаге 9 выше может быть изменена на:
   = СУММЕСЛИ (B2: B10, «> =» & B12, C2: C10)
   , если ячейка B12 содержит число — 10 .

   Пример 2 — Прокатный итог за 12 месяцев

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

   Даты указаны в столбце A, и список должен быть отсортирован по дате. Ежемесячный суммы указаны в столбце B.

   1. Выберите первую ячейку, в которой вы хотите увидеть скользящую сумму — ячейка C2 в этом примере
   2. Введите следующую формулу и нажмите Enter:
      = СУММЕСЛИ (A $ 2: A2, «> =» & ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2)), B $ 2: B2).
   3. Скопируйте формулу до последней строки с данными.
   4. В каждой строке отображается скользящая сумма за последние 12 месяцев (если имеется)

   Как это работает

   Формула проверяет даты в столбце A, начиная со строки 2 (2 австралийских доллара), и до текущей строки (A2)

   = СУММЕСЛИ ( A $ 2: A2

   Функция ДАТА вычисляет дату, которая на 11 месяцев раньше даты. в текущей строке

   ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2))

   Оператор> = проверяет даты, которые больше или равны та дата,

   г.

   «> =» & ДАТА (ГОД (A2), МЕСЯЦ (A2) -11, ДЕНЬ (A2))

   Для строк, соответствующих критерию, формула суммирует суммы в столбце B, начиная со строки 2 (B $ 2) до текущей строки (B2)

   B $ 2: B2

   Сумма ячеек, соответствующих нескольким критериям — СУММЕСЛИМН

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

   Соответствие нескольким критериям

   В этом примере только строки со статусом «Активен» и количество посещения больше или равны десяти, будут включены в общую сумму.

   Пошаговое видео ниже написанного инструкции.

   1. Выберите ячейку, в которой вы хотите увидеть сумму
   2. Введите знак равенства (=), чтобы начать формулу
   3. Тип: СУММЕСЛИМН (
   4. Выберите ячейки, содержащие значения для суммирования.В этом примере ячейки C2: C6 будут суммированы
   5. Введите запятую, затем выберите ячейки, содержащие значения для проверки. по первому критерию. В этом примере будут проверяться ячейки A2: A6
   6. Введите запятую, а затем введите первый критерий, «Активный».
   7. Введите запятую, затем выберите ячейки, содержащие значения для проверки. по второму критерию. В этом примере будут проверяться ячейки B2: B6
   8. Введите запятую, а затем введите второй критерий: «> = 10»
   9. Отделка закрывающей скобкой 🙂
   10. Заполненная формула:
       = СУММЕСЛИМН (C2: C6, A2: A6, «Активно», B2: B6, «> = 10»)
       
   11. Нажмите клавишу Enter для завершения ввода

   Формулы со ссылками на таблицы

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

   Проблемы со ссылками на таблицы

   В этой сводке продаж в ячейке C5 есть формула СУММЕСЛИМН, которая показывает правильную сумму продаж слитков в Восточном регионе.

   = СУММЕСЛИМН (Sales_Data [количество], Sales_Data [регион], $ B5, Sales_Data [категория], C $ 4)

   Однако, если вы наведите указатель мыши на маркер заполнения в ячейке C5 и перетащите вправо, формула покажет неправильный итог в ячейке D5.

   Если вы проверите формулу в ячейке D5, все ссылки на таблицу смещены на один столбец вправо, потому что формула была перетащена на один столбец вправо.

   = СУММЕСЛИМН (Sales_Data [TotalCost], Sales_Data [Категория], $ B5, Sales_Data [Product], D $ 4)

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

   Ни один из этих критериев не найден, поэтому результат равен нулю.

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

   Вместо этого используйте один из следующих методов:

   Заполнение справа

   • Выберите ячейку с формулой и ячейки справа, куда вы хотите скопировать формулу
   • Нажмите Ctrl + R, чтобы заполнить формулу справа

   Копировать и вставить

   • Выберите ячейку с формулой и нажмите Ctrl + C, чтобы скопировать ее
   • Выберите все ячейки, в которые вы хотите скопировать формулу
   • Нажмите Ctrl + V, чтобы вставить формулу

   СУММЕСЛИМН с несколькими критериями И / ИЛИ

   В приведенном ниже списке вы можете использовать формулу СУММЕСЛИМН для суммирования строк, в которых город — Нью-Йорк, И категория — Бары.Формула в ячейке G9 будет:

   = СУММЕСЛИМН (D4: D15, B4: B15, G4, C4: C15, G6)

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

   • город Нью-Йорк И категория Cookies
   • город Нью-Йорк И категория Бары

   Две категории вводятся в ячейки G6: G7 на листе.

   Формула с вводом массива

   Чтобы вычислить общую сумму, мы обернем формулу СУММЕСЛИМН функцией СУММ, и введите формулу в виде массива.

   1. Выберите ячейку, в которой вы хотите увидеть общую сумму — G9 в этом примере
   2. Чтобы запустить формулу, введите: = СУММ (СУММЕСЛИМН (
   3. Выберите ячейки, содержащие значения для суммирования. В этом примере Ячейки D4: D15 будут суммированы
   4. Введите запятую , затем выберите ячейки, содержащие значения проверить по первому критерию.В этом примере ячейки B4: B15 будут проверены — они содержат названия городов
   5. Введите запятую , а затем щелкните ячейку с первым критерием — G4
   6. Введите запятую , затем выберите ячейки, содержащие значения проверить по второму критерию. В этом примере ячейки C4: C15 будут проверены — у них категория
   7. Введите запятую , а затем выберите ячейки со списком для второй критерий — G6: G7
   8. Отделка с 2 закрывающими скобами: ))
   9. Заполненная формула в ячейке G9:
      = СУММ (СУММЕСЛИМН (D4: D15, B4: B15, G4, C4: C15, G6: G7))
      
   10. Нажмите клавиши Ctrl + Shift + Enter для массива — введите формула — результат будет неверным, если просто нажать клавишу Enter ключ.
   11. Фигурные скобки будут автоматически добавлены в начало и конец формулы, чтобы показать, что она введена в массив. Не вводите скобки самим собой.

   Сумма ячеек, соответствующих нескольким критериям — СУММПРОИЗВ

   Соответствие нескольким критериям

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

   В этом примере только строки со статусом «Активен» и количество посещений больше или равно десяти будут включены в общем.

   1. Выберите ячейку, в которой вы хотите увидеть сумму
   2. Введите знак равенства (=), чтобы начать формулу
   3. Тип: SUMPRODUCT (- (
   4. Выберите ячейки, содержащие значения для проверки первого критерия.В этом примере будут проверяться ячейки A2: A6
   5. Введите первый критерий: = «Активно»
   6. Тип), — (
   7. Выберите ячейки, содержащие значения для проверки второго критерия. В этом примере будут проверяться ячейки B2: B6
   8. Введите второй критерий:> = 10
   9. Тип), — (
   10. Выберите ячейки, содержащие значения для суммирования. В этом примере ячейки C2: C6 будут суммированы
   11. Отделка с закрывающими скобками :))
   12. Заполненная формула:
       = СУММПРОИЗВ (- (A2: A6 = «Активный»), — (B2: B6> = 10), — (C2: C6))
       
   13. Нажмите клавишу Enter для завершения ввода

   Суммировать 5 лучших номеров в списке

   Используйте функции СУММ и НАИБОЛЬШИЙ вместе, чтобы сложить наибольший числа в списке.

   Версия 1 — несколько первых номеров

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

   = СУММ (НАИБОЛЬШИЙ (A1: A7, {1,2,3}))

   Результат: 70 + 60 + 50 = 180

   Примечание : Вторые 50 не включены в результат, хотя он разделен на 3-е место.

   Версия 2 — Многие первые числа

   Если необходимо суммировать много верхних чисел, вы можете включить функцию ДВССЫЛ. в формуле с функцией СУММ.В функции ДВССЫЛ используйте строку числа, которые представляют числа, которые вы хотите включить. В этом примере строки 1:10, поэтому первые 10 чисел в указанном диапазоне будут суммировать.

   1. Введите формулу:
      = СУММ (НАИБОЛЬШИЙ (A1: A50, СТРОКА (КОСВЕННО («1:10»))))
   2. Это формула массива, и ее необходимо вводить в массив. Для этого удерживайте клавиши Ctrl и Shift и нажмите Enter

   Версия 3 — переменные верхние числа

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

   1. В ячейке C1 введите количество верхних ячеек, например 10
   2. Введите формулу:
      = СУММ (НАИБОЛЬШИЙ (A1: A7, СТРОКА (КОСВЕННАЯ («1:» & C1))))
   3. Это формула массива, и ее необходимо вводить в массив. Для этого удерживайте клавиши Ctrl и Shift и нажмите Enter

   Сумма сумм в диапазоне дат

   Для суммирования сумм на основе диапазона дат можно использовать функцию СУММЕСЛИМН. в Excel 2007 или более поздних версиях.Посмотрите это видео, чтобы увидеть шаги, и письменные инструкции находятся под видео.

   Сумма сумм за диапазон дат

   Чтобы суммировать суммы в определенном диапазоне дат, используйте функцию СУММЕСЛИМН. (Excel 2007 и более поздние версии) или функция СУММЕСЛИ. Ниже приведены два примера:

   — Итого с СУММЕСЛИМН

   — Итого с СУММЕСЛИ

   В этом примере дата начала и дата окончания вводятся на листе.Даты указаны в столбце A, а проданные единицы — в столбце B.

   Использование СУММЕСЛИМН для вычисления итога для диапазона дат

   В Excel 2007 и более поздних версиях можно использовать функцию СУММЕСЛИМН, чтобы рассчитать общую сумму на основе нескольких критериев. Мы будем использовать формулу СУММЕСЛИМН для суммирования всех единиц, у которых дата продажи:

   • в дату начала или позже
   • не позднее даты окончания.

   Вот формула, которая вводится в ячейку D5:

   = СУММЕСЛИМН ($ B $ 2: $ B $ 9, $ A $ 2: $ A $ 9, «> =» & $ D $ 2, $ A $ 2: $ A $ 9, «<=" & $ 2)

   • Первый аргумент, $ B $ 2: $ B $ 9 , — это диапазон чисел, которые мы хотим просуммировать.
   • Второй аргумент, $ A $ 2: $ A $ 9 , равен диапазон для проверки по критерию 1.
   • Третий аргумент, «> =» & $ D $ 2 , — это диапазон со значением критерия 1 (дата начала), а оператор для использования с этим значением (больше или равно)
   • 4-й аргумент, $ 2 $: $ A $, диапазон для проверки на соответствие критерию 2.
   • Пятый аргумент, «<=" & $ E $ 2 , — это диапазон со значением критерия 2 (дата окончания), а оператор использовать с этим значением (меньше или равно)

   В этом примере результат для выбранного диапазона дат составляет всего Продано 494 единицы.Для проверки вы можете выбрать ячейки B3: B6 и посмотреть на итоговое значение отображается в строке состояния Excel.

   Чтобы получить общее количество единиц для другого диапазона дат, измените дату начала в ячейке D2 и / или Дата окончания в ячейке E2.

   Использование СУММЕСЛИ для вычисления итога для диапазона дат

   В Excel 2003 и более ранних версиях вы можете использовать функцию СУММЕСЛИ, чтобы рассчитать итоговую сумму по единственному критерию. Мы будем использовать одну формулу СУММЕСЛИ для суммирования всех единиц, у которых дата продажи:

   • не позднее Даты начала

   Затем мы воспользуемся другой формулой СУММЕСЛИ, чтобы вычесть любые значения, где есть дата

   Вот формула, которая вводится в ячейку D5:

   = СУММЕСЛИ ($ A $ 2: $ A $ 9, «> =» & $ D $ 2, $ B $ 2: $ B $ 9)
   — СУММЕСЛИ ($ A $ 2: $ A $ 9, «>» & $ E $ 2, $ B $ 2: $ B $ 9)

   • Диапазон, $ 2 австралийских доллара: 9000 австралийских долларов4, содержит числа, которые мы хотим просуммировать.
   • Критерий «> =» & $ D $ 2 : диапазон с датой начала и оператор для использования с этим значением (больше или равно)
   • Диапазон, $ B $ 2: $ 9 , это диапазон проверить дату
   • Критерий «>» & $ E $ 2 : диапазон с датой окончания и оператор для использования с этим значением (больше)

   В этом примере результат для выбранного диапазона дат составляет всего Продано 494 единицы.Для проверки вы можете выбрать ячейки B3: B6 и посмотреть на итоговое значение отображается в строке состояния Excel.

   Чтобы получить общее количество единиц для другого диапазона дат, измените дату начала в ячейке D2 и / или Дата окончания в ячейке E2.

   Суммируйте отфильтрованный список с ПРОМЕЖУТОЧНЫМ ИТОГОМ

   После фильтрации строк в списке вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, вместо функции СУММ для суммирования чисел в видимых строках.

   1. Примените к столу автофильтр.Инструкции здесь — Автофильтр Основы
   2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере первый столбец отфильтрован для связующих.
   3. Выберите ячейку непосредственно под столбцом, для которого нужно суммировать.
   4. Нажмите кнопку «Автосумма» на вкладке «Главная» ленты.
      • Если вы хотите, чтобы функция ПРОМЕЖУТОЧНЫЙ ИТОГ в другой ячейке непосредственно под отфильтрованным списком вы можете ввести формулу вместо использования кнопки Автосумма.
   5. Формула ПРОМЕЖУТОЧНОГО ИТОГА будет автоматически вставлена ​​с учетом видимого ячейки в столбце
   • Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции, который определяет, как следует рассчитывать числа. Есть 9 в этом примере, который сообщает Excel, что нужно СУММИРОВАТЬ числа.
   • Можно использовать другие номера функций, например 1 для СРЕДНЕГО и 3 для COUNTA.Полный список представлен в моем блоге отправить Всего отфильтрованный список.
3. Нажмите клавишу Enter, чтобы завершить ввод формулы.

Примечание : в Excel 2003 и более поздних версиях вы можете использовать формулу:
= ПРОМЕЖУТОЧНЫЙ ИТОГ (109, B2: B9)
для вычисления промежуточных итогов видимых ячеек в диапазоне, где строки были скрыты вручную, или фильтрованный.

Промежуточные номера функций

Первый аргумент функции ПРОМЕЖУТОЧНЫЙ ИТОГ — это номер функции, которая определяет, как следует рассчитывать числа.Есть 11 функций который можно использовать в качестве первого аргумента функции ПРОМЕЖУТОЧНЫЙ ИТОГ. Список находится в алфавитном порядке, чтобы вы могли запомнить некоторые числа, не заходя каждый раз в справку Excel.

Каждая функция указана дважды. Первая группа функций — это пронумерованы 1-11.

Каждая функция указана дважды. Вторая группа функций — это под номерами 101-111.

Суммируйте отфильтрованный список с помощью AGGREGATE

Функция АГРЕГАТ, представленная в Excel 2010, аналогична ПРОМЕЖУТОЧНОМУ ИТОГУ. функция, но она имеет больше функций и может игнорировать значения ошибок как скрытые строки в данных.

Посмотрите это видео, чтобы узнать, как настроить формулу ОБОГРЕВА, и письменные инструкции под видео.

Суммирование отфильтрованного списка с помощью AGGREGATE

После фильтрации строк в списке вы можете использовать функцию АГРЕГАТ, вместо функции СУММ для суммирования чисел в видимых строках. Этот функция была представлена ​​в Excel 2010.

Подобно функции ПРОМЕЖУТОЧНЫЙ ИТОГ, АГРЕГАТ игнорирует скрытые строки и предлагает несколько функций, например СУММ или СРЕДНЕЕ, для выбранных данных.Тем не менее, он имеет 19 функций по сравнению с SUBTOTAL. 11 функций.

В отличие от функции ПРОМЕЖУТОЧНЫЙ ИТОГ, АГРЕГАТ может игнорировать ошибки, а также скрытые строки и вложенные функции SUBTOTAL и AGGREGATE.

Чтобы суммировать значения в отфильтрованном списке и игнорировать скрытые строки и ошибки:

1. Выберите ячейку, в которой вы хотите получить сумму
2. Тип = АГРЕГАТ (
3. В списке функций дважды щелкните 9 — СУММ, чтобы добавить 9 в качестве первый аргумент.
4. Введите запятую и в списке параметров дважды щелкните параметр 3. или вариант 7. В этом примере 3 — второй аргумент, а результат будет игнорировать скрытые строки, ошибки и вложенные функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЙ ИТОГ.

5. Введите запятую и выберите диапазон ячеек, содержащих данные. — D2: D7 в этом примере.
6. Введите скобку, чтобы завершить формулу, и нажмите клавишу Enter.

Заполненная формула: = АГРЕГАТ (9,3, D2: D7)

Сумма отдельных элементов в отфильтрованном списке

Пример 1

Лоран Лонгре создал формулу, которая позволяет работать с видимыми строками. после фильтра. Дополнительные сведения см. В разделе «Техника формул мощности» в этой статье. на веб-сайте Джона Уокенбаха (больше не доступен).

Включая эту технику, СУММПРОИЗВ может использоваться для суммирования видимых элементов. в отфильтрованной таблице.В следующем примере столбец D отфильтрован. для сумм больше 100. Следующая формула суммирует Итого сумм в строках, содержащих «Pen» в столбце A.

• Фильтрующая колонна D для количеств более 100.
• В ячейке A12 введите: Pen
• В ячейке B12 введите следующую формулу:
  • = СУММПРОИЗВ (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; СМЕЩЕНИЕ (A1: A10; СТРОКА (A1: A10))
    -МИН (СТРОКА (A1: A10)) ,, 1)), — (A1: A10 = A12), D1 : D10)
• Нажмите клавишу Enter, чтобы завершить ввод формулы.

Пример 2

Другой пример использования SUMPRODUCT и SUBTOTAL вместе, см. В моем сообщении в блоге Subtotal и Sumproduct with Filter. Сэм поделился своей техникой выполнения дополнительных сумм или подсчетов на основе видимых данных в отфильтрованной таблице.

В книге Сэма есть список с полями Product, Region и Amount. Он создал динамические именованные диапазоны для записей в каждом поле, используя INDEX и COUNTA.

Вы можете получить книгу Сэма в разделе «Загрузки» ниже.

Загрузить файлы примеров

• Примеры SUM : Загрузите заархивированный образец книги по функциям Sum. Рабочая тетрадь содержит примеры СУММ, СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЙ И ОБЩИЙ ИТОГ. Заархивированный файл имеет формат xlsx, и не содержит макросов.
• 7 способов подвести итог: Загрузите 7 способов Образец рабочей тетради для подведения итогов, к которому следует обращаться вместе с видео «7 способов подведения итогов».Заархивированный файл имеет формат xlsx и не содержит макросов.
• Рабочая тетрадь Сэма : Чтобы просмотреть рабочую книгу Сэма и формулы для выполнения дополнительных сумм или подсчетов на основе видимых данных в отфильтрованной таблице, загрузите образец файла SUMPRODUCT SUBTOTAL .
• Ссылки на таблицы : Чтобы увидеть проблему с копированием формул со ссылками на таблицы, загрузите рабочую книгу Table Reference Problem. Заархивированный файл имеет формат xlsx и не содержит макросов

Дополнительные уроки по функциям

Список функций

Формулы, Начало работы

ПРОМЕЖУТОЧНЫЙ ИТОГ Функция

СРЕДНЕЕ

СЧЁТ / СЧЁТЕСЛИ

Как избавиться от формул в Excel | Small Business

Автор: Стивен Мелендез Обновлено 8 августа 2018 г.

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

Удалить формулу в Excel

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

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

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

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

Замена части формулы

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

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

Отключить автоматическое вычисление значений формул

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

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