Для полного знакомства с электронными таблицами требуется понять, как сделать формулу в Excel. Это поможет делать подсчёты, не закрывая программу. Для работы с электронными таблицами чаще используют пакет Microsoft Word, в состав которого входит приложение «Эксель». Программа не вызывает трудностей при освоении, но не каждый пользователь знает, как сделать формулу в Excel. Однако, использование специальных знаков, выражений и операторов позволяет облегчить ряд задач.
Какие функции доступны для пользователя Эксель:
- арифметические вычисления;
- применение готовых функций;
- сравнения числовых значений в нескольких клетках: меньше, больше, меньше (или равно), больше (или равно). Результатом будет какое-то из двух значений: «Истина», «Ложь»;
- объединение текстовой информации из двух и более клеток в единое целое.
Но этим возможности Excel не ограничиваются. Программа позволяет работать с графиками математических функций. Благодаря этому бухгалтер или экономист могут создавать наглядные презентации.
Основы работы с формулами в Excel
Чтобы вникнуть в то, как вставить формулу в Эксель, следует понять базовые принципы работы с математическими выражениями в электронных табличках Microsoft:
- Каждая из них должна начинаться со значка равенства («=»).
- В вычислениях допускается использование значений из ячеек, а также функции.
- Для применения стандартных математических знаков операций следует вводить операторы.
- Если происходит вставка записи, то в ячейке (по умолчанию) появляются данные итоговых вычислений.
- Увидеть конструкцию пользователь может в строчке над таблицей.
Каждая из ячеек в Excel – неделимая единица с отдельным идентификатором (который называется её адресом), обозначается буквой (определяет номер столбика) и числом (указывает на номер строчки).
«Эксель» допускается использовать, как настольный калькулятор, при этом сложные формулы могут состоять из элементов:
- постоянных чисел-констант;
- операторов;
- ссылок на иные клетки-ячейки;
- математических функций;
- имён диапазонов;
- встроенных формул.
При необходимости пользователь может производить любые вычисления.
Как посчитать количество строк (с одним, двумя и более условием)
Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.
Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?
Формула:
«=СЧЁТЕСЛИ(A2:A7;A2)» — где:
- A2:A7 — диапазон, в котором будут проверяться и считаться строки;
- A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).
Результат показан в правой части на скрине ниже.
Количество строк с одним условием
Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).
Формула будет иметь вид:
=СЧЁТЕСЛИМН(A2:A7;A2;B2:B7;»6?) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня), где:
A2:A7;A2 — первый диапазон и условие для поиска (аналогично примеру выше);
B2:B7;»6? — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).
Счет строк с двумя и более условиями
*
Как фиксировать ячейку
Для фиксации значения в клетке применяют стандартный символ «$». Ставить знак допускается с помощью «быстрой» клавиши F4. Используют три варианта фиксации:
- полная;
- по вертикали;
- по горизонтали.
Чтобы предотвратить сдвиг ячейки по горизонтали и по вертикали используют обозначения вида: $A$1. Это может понадобиться, если в расчётах используется постоянное значение, например, расход топлива или курс валют.
Если требуется закрепление по вертикали, то обозначение будет таким: $A1.
Для фиксации по горизонтали понадобится указать адрес в виде: A$1.
Для чего нужны макросы?
Если пользователю приходится часто повторять одни и те же действия в программе, ему пригодятся знания о том, как работают макросы в Excel. Они запрограммированы выполнять действия в определенной последовательности. Использование макросов позволяет автоматизировать определенные операции и облегчить монотонную работу. Они могут быть записаны на различных языках программирования, но их суть от этого не меняется.
Для создания макроса в этом приложении необходимо войти в меню «Сервис», выбрать пункт «Макрос», а затем нажать «Начать запись». Далее нужно выполнить те действия, которые часто повторяются, и после окончания работы нажать «Остановить запись».
Все эти инструкции помогут новичку разобраться, как работать в Excel: вести учет, создавать отчеты и анализировать цифры.
Делаем таблицу в Excel с математическими формулами
Следует разобраться, с какого символа начинается формула в Excel. Здесь всё просто. Чтобы применить одну из математических формул, требуется:
- поставить значок «=» в клетку — здесь будут отображаться результаты вычислений;
- далее выделить клетки с исходными значениями и указание нужного оператора – это знаки: «+», «-», «*», «/»;
- повторяем то же с другими «клеточками», которые будут участвовать в вычислениях;
- нажать клавишу «равно».
Это самый доступный метод создания формулы.
Восстановление несохранённых файлов
Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.
На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» -> «Последние» (File -> Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).
В Excel 2013 путь немного другой: «Файл» -> «Сведения» -> «Управление версиями» -> «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).
В последующих версиях Excel следует открывать «Файл» -> «Сведения» -> «Управление книгой».
Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.
Пошаговый пример №1
Простейшая инструкция создания формулы заполнения таблиц в Excel на примере расчёта стоимости N числа товаров, исходя из цены за одну единицу и количества штук на складе.
Шаг 1
Перед названиями товаров требуется вставить дополнительный столбец.
Шаг 2
Выделить клетку в 1-ой графе и щелкнуть правой кнопкой мышки.
Шаг 3
Нажать «Вставить». Или использовать комбинацию «CTRL+ПРОБЕЛ» – чтобы выделить полный столбик листа. Затем кликнуть: «CTRL+SHIFT+»=»» – для вставки столбца.
Шаг 4
Новую графу рекомендуется назвать, например, – «No п/п».
Шаг 5
Ввод в 1-ю клеточку «1», а во 2-ю – «2».
Шаг 6
Теперь требуется выделить первые две клеточки – «зацепить» левой кнопкой мышки маркер автозаполнения, потянуть курсор вниз.
Шаг 7
Аналогичным способом допускается запись дат. Но промежутки между ними должны быть одинаковые, по принципу: день, месяц и год.
Шаг 8
Ввод в первую клетку информации: «окт.18», во вторую – «ноя.18».
Шаг 9
Выделение первых двух клеток и «протяжка» их за маркер к нижней части таблицы.
Шаг 10
Для поиска средней стоимости товаров, следует выделить столбик с указанными ранее ценами + еще одну клеточку. Затем открыть меню кнопки «Сумма», набрать формулу, которая будет использоваться для расчёта усреднённого значения.
Шаг 11
Для проверки корректности записи формулы, требуется дважды кликнуть по клетке с нашим результатом.
Способ 1: Кнопка «Вставка функции»
Вариант с использованием специально кнопки для вызова меню «Вставка функции» подойдет начинающим юзерам и тем, кто не хочет вручную записывать каждое условие, соблюдая специфику синтаксиса программы.
- При вставке формулы всегда в первую очередь выбирается ячейка, где в дальнейшем будет располагаться конечное значение. Сделайте это, нажав по подходящему блоку ЛКМ.
- Затем переходите к инструменту «Вставка функции» путем клика по отведенной для этого кнопке на верхней панели.
- Далее требуется отыскать подходящую функцию. Для этого можно ввести ее краткое описание или определиться с категорией.
- Посмотрите на список в блоке ниже, чтобы выбрать там ту самую функцию.
- При ее выделении внизу отобразится краткая информация о действии и принцип записи.
- Для получения развернутой информации от разработчиков понадобится нажать по выделенной надписи «Справка по этой функции».
- Как только произойдет выбор функции, отобразится отдельное окно, где заполняются ее аргументы. За пример мы взяли формулу МАКС, показывающую максимальное значение из всего списка аргументов. Поэтому в качестве числа здесь задается перечень ячеек, входящих в диапазон для подсчета.
- Вместо ручного заполнения можно нажать по таблице и выделить все ячейки, которые должны попасть в тот самый диапазон.
- МАКС, как и другие функции, например самая распространенная СУММ, может включать в себя несколько списков аргументов и вычислять значения из всех них. Для этого заполняйте в таком же порядке идущие следом блоки «Число2», «Число3» и т. д.
- После нажатия по кнопке «ОК» или клавише Enter формула вставится в выделенную ранее ячейку с уже отобразившимся результатом. При нажатии по ней на верхней панели вы увидите синтаксическую запись формулы и по необходимости сможете ее отредактировать.
Пошаговый пример №2
Можно подсчитать, какова доля каждого из товаров в итоговой сумме. Это потребует выполнения последовательности действий.
Шаг 1
Делим стоимость одного товара на суммарную стоимость, а результат требуется помножить на 100. Однако ссылку на клетку со значением полной стоимости предметов требуется обозначить как постоянную, это нужно, чтобы при копировании значение не изменялось.
Шаг 2
Чтобы сгенерировать результаты вычислений в процентах (в Эксель), не требуется умножать полученное ранее частное на 100. Следует выделить клетку с нашим результатом и нажать «Процентный формат». Также допускается использование комбинации горячих клавиш: «CTRL+SHIFT+5».
Шаг 3
Теперь необходимо скопировать формулу на столбец: будет изменяться первое значение в нашей формуле (относительная ссылка). А второе (абсолютная ссылка) останется прежним. Проверка правильности вычислений – поиск итога. Если равен 100%, то вычисления выполнены верно.
Копирование без нарушения форматов
Вы, скорее всего, знаете о волшебном маркере автозаполнения. Это тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, так как копируется не только формула, но и формат ячейки. Этого можно избежать. Сразу после того, как потянули за чёрный крест, нажмите на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.
Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.
