Как научиться формулам в excel


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

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

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

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

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

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

ОператорОперацияПример
+ (плюс)Сложение=В4+7
- (минус)Вычитание=А9-100
* (звездочка)Умножение=А3*2
/ (наклонная черта)Деление=А7/А8
^ (циркумфлекс)Степень=6^2
= (знак равенства)Равно
Меньше
>Больше
Меньше или равно
>=Больше или равно
Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (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

Введите формулу | Редактировать формулу | Приоритет операторов | Копировать / вставить формулу | Вставить функцию | 10 лучших функций

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

Например, ячейка A3 ниже содержит формулу, которая добавляет значение ячейки A2 к значению ячейки A1.

Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1: A2.

Введите формулу

Чтобы ввести формулу, выполните следующие действия.

1. Выберите ячейку.

2. Чтобы сообщить Excel, что вы хотите ввести формулу, введите знак равенства (=).

3.Например, введите формулу A1 + A2.

Совет: вместо ввода A1 и A2 просто выберите ячейки A1 и A2.

4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3. Это одна из самых мощных функций Excel!

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

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

1. Чтобы изменить формулу, щелкните строку формул и измените формулу.

2. Нажмите Enter.

Приоритет оператора

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

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Другой пример,

Сначала Excel вычисляет часть в круглых скобках (A2 + A3). Затем он умножает этот результат на значение ячейки A1.

Копировать / вставить формулу

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

1. Введите формулу, показанную ниже, в ячейку A4.

2а. Выделите ячейку A4, щелкните правой кнопкой мыши и затем нажмите Копировать (или нажмите CTRL + c) ...

... затем выберите ячейку B4, щелкните правой кнопкой мыши и затем щелкните Вставить в разделе «Параметры вставки:» (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4.Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите его в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ (A1: A4). Имя этой функции - СУММ. Часть в скобках (аргументы) означает, что мы передаем Excel диапазон A1: A4 в качестве входных данных.Эта функция складывает значения в ячейках A1, A2, A3 и A4. Непросто запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, в этом вам поможет функция «Вставить» в Excel.

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

1. Выберите ячейку.

2. Нажмите кнопку «Вставить функцию».

Откроется диалоговое окно «Вставить функцию».

3.Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ в категории «Статистика».

4. Щелкните OK.

Откроется диалоговое окно «Аргументы функции».

5. Щелкните в поле Диапазон и выберите диапазон A1: C2.

6. Щелкните поле Criteria и введите> 5.

7. Щелкните OK.

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

Примечание: вместо использования функции вставки просто введите = СЧЁТЕСЛИ (A1: C2, "> 5"). Когда вы придете к: = СЧЁТЕСЛИ (вместо ввода A1: C2 просто выберите диапазон A1: C2.

10 основных функций

Microsoft Excel - это все формулы и функции. Ниже вы можете найти обзор 10 наиболее часто используемых функций Excel (+ дополнительные приемы при создании формул в Excel).

1. СЧЕТ

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

Примечание: используйте СЧЕТЧИК и СЧЕТЧИК для подсчета пустых / непустых ячеек в Excel. COUNTA означает «подсчитать все».

2. СУММ

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

Примечание: = СУММ (5: 5) суммирует все значения в 5-й строке. Чтобы создать потрясающие формулы СУММ, объедините функцию СУММ с другими функциями Excel.

3. IF

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

Объяснение: если оценка больше или равна 60, функция ЕСЛИ возвращает Pass, иначе она возвращает Fail.Помните, чтобы быстро скопировать эту формулу в другие ячейки, щелкните в правом нижнем углу ячейки C2 и перетащите его в ячейку C6.

4. СРЕДНЕЕ

Чтобы вычислить среднее значение группы чисел, используйте функцию СРЕДНЕЕ (здесь нет ракетостроения). Приведенная ниже формула вычисляет среднее значение трех первых чисел в диапазоне A1: A6.

Объяснение: функция НАИБОЛЬШИЙ возвращает константу массива {20,15,10}.Эта константа массива используется в качестве аргумента функции СРЗНАЧ, давая результат 15.

5. СЧЕТЕСЛИ

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

Объяснение: звездочка (*) соответствует серии из нуля или более символов. Посетите нашу страницу о функции СЧЁТЕСЛИ для получения дополнительной информации и примеров.

6.СУММЕСЛИ

Функция СУММЕСЛИ ниже суммирует значения в диапазоне B1: B5, если соответствующие ячейки в диапазоне A1: A5 содержат ровно круг + 1 символ.

Объяснение: вопросительный знак (?) Соответствует ровно одному символу. Посетите нашу страницу о функции СУММЕСЛИ для получения дополнительной информации и примеров.

7. ВПР

Функция ВПР ниже ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент).Значение 4 (третий аргумент) указывает функции VLOOKUP вернуть значение в той же строке из четвертого столбца красной таблицы.

Примечание: посетите нашу страницу о функции ВПР, чтобы узнать больше об этой мощной функции Excel.

8. ИНДЕКС и СООТВЕТСТВИЕ

Используйте ИНДЕКС и ПОИСКПОЗ в Excel для выполнения расширенного поиска (если вы новичок в Excel, вы можете пропустить эту формулу).Например, используйте INDEX и MATCH для поиска по двум столбцам.

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита или Джеймса Андерсона. Узнайте, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и произвести впечатление на своего начальника.

9. МИН. И МАКС.

Чтобы найти минимальное значение, используйте функцию MIN. Чтобы найти максимальное значение, используйте функцию MAX. Это так просто, как кажется.

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

10. СУММПРОИЗВ

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

Объяснение: функция СУММПРОИЗВ выполняет это вычисление: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.Функция СУММПРОИЗВ является чрезвычайно универсальной функцией и может давать тот же результат, что и многие встроенные функции в Excel и даже формулы массива!

.

формул Excel: 10 формул, которые помогли мне сохранить работу

Udemy для бизнеса Просмотреть курсы Udemy
  • Развитие
  • ИТ и программное обеспечение
  • Наука о данных
  • Мягкие навыки
  • HR и L&D
  • Бизнес
  • Производительность офиса
  • Маркетинг
  • Проект
Категории статей
  • Развитие
  • ИТ и программное обеспечение
  • Наука о данных
  • Мягкие навыки
  • HR и L&D
  • Бизнес
  • Производительность офиса
.Формулы и функции

Excel: Изучите с помощью базовых ПРИМЕРОВ

  • Home
  • Testing

      • Back
      • Agile Testing
      • BugZilla
      • Cucumber
      • Database Testing
      • 9000 J2000 Тестирование базы данных
      • 9000
        • Назад
        • JUnit
        • LoadRunner
        • Ручное тестирование
        • Мобильное тестирование
        • Mantis
        • Почтальон
        • QTP
        • Назад
        • Центр качества
        • 0003
        • 000 RPM
        • 00030003 SoapUI
        • Управление тестированием
        • TestLink
    • SAP

        • Назад
        • 9000 3 ABAP
        • APO
        • Начинающий
        • Basis
        • BODS
        • BI
        • BPC
        • CO
        • Назад
        • CRM
        • Crystal Reports
        • Crystal Reports
        • FICO3
        • Заработная плата
        • Назад
        • PI / PO
        • PP
        • SD
        • SAPUI5
        • Безопасность
        • Менеджер решений
        • Successfactors
        • SAP Back Tutorials
        • 9007
            • Apache
            • AngularJS
            • ASP.Net
            • C
            • C #
            • C ++
            • CodeIgniter
            • СУБД
            • JavaScript
            • Назад
            • Java
            • JSP
            • Kotlin
            • Linux
            • Linux
            • Linux js
            • Perl
            • Назад
            • PHP
            • PL / SQL
            • PostgreSQL
            • Python
            • ReactJS
            • Ruby & Rails
            • Scala
            • SQL
            • 000
            • SQL
            • 000
            • SQL
            • 000 0003 SQL 000
            • UML
            • VB.Net
            • VBScript
            • Веб-службы
            • WPF
        • Обязательно учите!

            • Назад
            • Бухгалтерский учет
            • Алгоритмы
            • Android
            • Блокчейн
            • Business Analyst
            • Создание веб-сайта
            • CCNA
            • Облачные вычисления
            • 00030003 COBOL
                9000 Compiler
                  9000 Встроенный
                    9000 Compiler
                  • Ethical Hacking
                  • Учебные пособия по Excel
                  • Программирование на Go
                  • IoT
                  • ITIL
                  • Jenkins
                  • MIS
                  • Сети
                  • Операционная система
                  • Назад
                  • Управление проектами Обзоры
                  • Salesforce
                  • SEO
                  • Разработка программного обеспечения
                  • VB A
              • Big Data

                  • Назад
                  • AWS
                  • BigData
                  • Cassandra
                  • Cognos
                  • Хранилище данных
                  • 0003
                  • HBOps
                  • 0003
                  • HBOps
                  • 0003
                  • MicroStrategy
              .

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

              На первый взгляд это кажется более длинным путем, но вы можете найти его полезным, если хотите отобразить формулы на нескольких листах 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. Кусок пирога, не правда ли? С другой стороны, если вы планируете поделиться своим листом с другими пользователями, вы можете защитить свои формулы от перезаписи или редактирования и даже скрыть их от просмотра.Именно об этом мы и поговорим в следующей статье. Пожалуйста, не переключайтесь!

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

              .

              Смотрите также