» » Что такое формула Excel и для чего она нужна

Что такое формула Excel и для чего она нужна

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

Если произвести поиск по функциям подстановки, Google покажет, что ВПР намного популярнее функции ИНДЕКС. Оно и понятно, ведь чтобы придать функции ИНДЕКС тот же функционал, что и ВПР, необходимо воспользоваться еще одной формулой – ПОИСКПОЗ. Что касается меня, было всегда непросто попробовать и освоить две новые функции одновременно. Но они дают больше возможностей и гибкости в создании электронных таблиц. Но обо всем по порядку.

Функция ВПР()

Что такое Excel?

Математические операторы, к которым относится и рассчет суммы — наиболее часто используемые операторы Excel

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

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

  • Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
  • Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).
    Param4-False

Схемы работы формул

ВПР тип I

в английской версии =VLOOKUP(A3;$F$2:$H$22;3;0)

Функция ВПР в Excel с примером

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

Следующее поле «Номер_столбца» - это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй - цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Сейчас ставим 0 (или ЛОЖЬ).

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

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

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


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


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


Щелкните в любой ячейке второй строки правой кнопкой мыши и в открывшемся меню выберите команду Вставить…, а затем в окне - Добавить строку.

excel_2010


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

excel_2010


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


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


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


  • Формулы обновляют свой результат(вычисляются) как только ячейка, задействованная в формуле(влияющая ячейка) поменяет значение. Например, если в ячейке А1 записать такую формулу: =D1, то при изменении в ячейке D1 значения оно так же поменяется и в А1. Ссылка на ячейки может быть не только в таком виде, но и в составе более сложных формул и функций и правило пересчета будет действовать для них так же
  • Функции не могут изменять значения и форматы других ячеек, они могут вернуть результат только в ту ячейку, в которую записаны

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

В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.

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

   

4.
щелкнуть на F6 и т. д.


Ввод функций
.
Вместо того чтобы набирать функции «вручную», вы можете щелкнуть на кнопке со
значком xf предусмотрена специальная кнопка со значком
Стандартная. С его помощью можно ввести и отредактировать любую функцию.
Так как функция суммирования используется в электронных таблицах очень часто,
для нее в панели
Мастер функций
, на экране появится диалоговое окно Стандартная
в панели инструментов
S.
Например, если выделить ячейку D10 и щелкнуть на кнопке суммы, в строке формул и
ячейке появится заготовка формулы: =CУMM(D6:D9). Вы можете отредактировать эту
формулу (если она вас не устраивает) или зафиксировать результат (щелчком на
кнопке с галочкой в строке формул). Если же дважды щелкнуть на кнопке
S,
результат сразу фиксируется в ячейке.


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

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

        
·при
копировании влево (вправо) по горизонтали смещение на одну ячейку уменьшает
(увеличивает) каждый номер столбца в формуле на единицу;

        
·при
копировании вверх (вниз) по вертикали смещение на одну ячейку уменьшает
(увеличивает) каждый номер строки в формуле на единицу.

Этим же способом можно
копировать в смежные ячейки числа и тексты.


Проценты
.
Очень часто нам необходимо показать доли в процентах (т.е. просто умножить
каждую долю на 100). Excel позволяет сделать это одним щелчком мыши. Выделите
столбец с данными и щелкните мышью на кнопке панели Форматирование с
изображением %. Все доли будут умножены на 100 и помечены знаком «%». Если вы
хотите, чтобы значения в дробной части числа отображались с большим или меньшим
количеством знаков, щелкните в панели инструментов форматирования кнопку
«Увеличить разрядность» или кнопку «Уменьшить разрядность».


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


Форматирование текста
.
Вам предоставляется возможность изменить шрифт, размер шрифта и начертание
текста в любом участке таблицы (от части ячейки до всей таблицы) с помощью
кнопок панели Форматирование, точно так же, как вы это делали с участками текста
в процессоре MS Word. Кроме того, вы можете изменить расположение текста в
группе выделенных ячеек с помощью таких же кнопок выравнивания (влево, вправо,
по центру), как и в процессоре Word.

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


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


Автоформатирование
, просматривая вариант
оформления таблицы, выбрать подходящий и нажать «ОК». Несмотря на то, что список
шаблонов, предлагаемых в диалоговом окне автоформатирования, невелик,
возможности оформления таблицы значительно расширяются за счет «ручного»
оформления различных участков таблицы с помощью множества комбинаций линий и
рамок различной формы.Автоформат. В диалоговом окне
Автоформат
выбрать команду Формат.
При изучении программы Word вы уже познакомились с функцией автоформатирования
таблицы с помощью заранее заготовленных шаблонов. Эта функция имеется и в Excel,
правда, количество шаблонов здесь поменьше. Мы использовали эти шаблоны
форматирования при подготовке первой таблицы. Чтобы воспользоваться функцией
автоформатирования, необходимо выделить блок ячеек, который необходимо оформить
по тому или иному шаблону, затем в меню


Цвет фона и символов

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


Расчетные операции в Excel


Ранее мы уже описали, как формулы используются для расчетов в Excel по формулам.
Заранее определенные формулы, которые выполняют вычисления по заданным величинам
(аргументам) называются
функциями
.
Эти функции позволяют выполнять простые и сложные вычисления. Функция имеет имя
(например, SIN) и, как правило, аргументы, которые записываются в круглых
скобках следом за именем функции. Скобки - обязательная принадлежность функции,
даже если у нее нет аргументов. Если аргументов несколько, один аргумент
отделяется от другого точкой с запятой. В качестве аргументов функции могут
использоваться числа, адреса ячеек, диапазоны ячеек, арифметические выражения и
функции. Смысл и порядок следования аргументов однозначно определены описанием
функции, составленным ее автором. Например, если в ячейке F3 записана формула с
функцией возведения в степень =СТЕПЕНЬ(ВЗ;2,3), значением этой ячейки будет
значение ячейки ВЗ, возведенное в степень 2,3.


Работая с функциями, помните:

   

1.
функция, записанная в формуле, как правило, возвращает уникальное значение
(арифметическое или логическое);

   

2.
существуют функции, которые не возвращают значение, а выполняют некоторые
операции (например, объединяют текстовые строки);

   

3.
существуют функции без аргументов (например, функция ПИ() возвращает число к =
3,1416...).


Ниже будут рассмотрены функции И (AND) и ИЛИ (OR), которые принимают логические
значения (True или False).


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

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


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


Логические функции


При решении некоторых задач значение ячейки необходимо вычислять одним из
нескольких способов, в зависимости от выполнения или невыполнения одного или
нескольких условий. Для решения таких задач применяют условную функцию ЕСЛИ. Она
имеет формат:


ЕСЛИ(<логическое выражение>;<выражение1>;<выражение2>).


Чтобы пользоваться этой функцией, вам целесообразно познакомиться с основными
понятиями логической (булевой) алгебры. Первый аргумент функции ЕСЛИ -
логическое выражение (в частном случае - условное выражение), которое принимает
.одно из двух значений: «Истина» или «Ложь» (1 или 0). В первом случае ЕСЛИ
принимает значение выражения!, а во втором -значение выражения2. В качестве
выражения! или выражения2 можно записать вложенную функцию ЕСЛИ. Обратите
внимание, что число вложенных функций ЕСЛИ не должно превышать семи. Если
условий много, записывать вложенные функции ЕСЛИ становится неудобно. В этом
случае на месте логического выражения мы можем указать одну из двух логических
функций: H(AND) или ИЛИ (ОК).


Формат функций одинаков:

        

·
И (<логическое выражение 1>;<логическое выражение2>;...),

        

·
ИЛИ (<логическое выражение! >;<логическое выражение2>;...).


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


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


В МЕНЮ

Используются технологии

Наверх