Работа с формулами в excel

Работа с формулами в Excel

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

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

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

  • = («знак равно») – равенство, равно. Обычно отвечает за начало формулы;
  • + («плюс») – сложение. Может складывать как числа, так и выражения;
  • — («минус») – вычитание;
  • * («звёздочка») – умножение;
  • / («наклонная вправо») – делит;
  • ^ («циркумфлекс») – возводит в степень.

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

Работа с простейшими формулами

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

  1. В готовой таблице выделите ту ячейку, куда хотите получить результат вашей формулы.
  2. Поставьте в этой ячейке знак «=». Он отвечает за начало формулы. Также вы можете его поставить не в самой ячейке, а в специальном поле, что расположено сверху. Здесь уже как вам будет самим удобно. Рассмотрим пример формулы a+b.
  3. После знака равно кликните левой кнопкой мыши по ячейки, значение из которой вам нужно взять в качестве первого.
  4. Автоматически установится наименование этой ячейки (латинская буква и номер) после знака равно. Здесь же вам требуется установить оператор, отвечающий за действие с этими ячейками – плюс, минус, равно, делить и т.д.
  5. Нажмите теперь левой кнопкой мыши по той ячейки, которая будет выполнять функции b в нашей формуле.

  • Чтобы получить результат расчётов, нажмите на клавишу Enter.
  • По такой инструкции можно делать формулы с несколькими переменными, а не только вида a+b. Например, вы можете сделать по аналогии формулу вида a+b+c, a+b+c+d и т.д.

    Примеры вычислений

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

    1. Задайте формулу вида a*b, чтобы рассчитать полученную сумму от продажи товара. Вид у формулы в Excel будет таким: =(название ячейки с количеством товара, например, B3)*(название ячейки с ценой, например, C3).
    2. Нажмите Enter, чтобы увидеть полученный результат в ячейки суммы у определённого товара. Учтите, что в формуле не должно быть пустых ячеек. В противном случае вместо результата вы получите просто сообщение об ошибке.

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

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

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

      В ячейку напротив столбца «Сумма» укажите формулу вида: =([название ячейки с количеством первой партии товара, например, B3]+[название ячейки с количеством второй партии товара, например, C3])*[название ячейки с ценой, например, D3].

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

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

    В табличном редакторе Excel есть встроенный по умолчанию калькулятор в редакторах формул. Хотя основное предназначение программы совсем другое. Использование встроенного калькулятора максимально простое:

    1. На листе выберите ячейку, в которой собираетесь происходить расчёты.
    2. Выделите эту ячейку и поставьте в ней знак равно.
    3. Пропишите нужные действия. Сюда можно прописать и сложный пример по типу: 444*89+((78-660)*9)/15).

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

    Источник: public-pc.com

    Работа с формулами в Excel

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

    Особенности расчетов в Excel

    Excel позволяет пользователю создавать формулы разными способами:

    • ввод вручную;
    • применение встроенных функций.

    Отличительной чертой формулы в Экселе независимо от способа создания заключается в том, что она начинается знаком равно (=). Если ячейка содержит формулу, в самой таблице пользователь видит результат вычисления. Выражение, по которому была рассчитана ячейка, отражает строка состояния.

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

    Ручное создание формул Excel

    Ввод вручную применим, когда необходимо осуществить простые расчеты (сложение, вычитание, умножение, деление) небольшого количества данных. Чтобы ввести ее следует выполнить следующие шаги:

    1. щелчком левой кнопки мыши выделяем ячейку, где будет отображаться результат;
    2. нажимаем знак равенства на клавиатуре;
    3. вводим выражение;
    4. нажимаем Enter.

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

    Между операндами ставят соответствующий знак: +, -, *, /. Легче всего их найти на дополнительной цифровой клавиатуре.

    Использование функций Майкрософт Эксель

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

    Для выбора требуемой функции нужно нажать на кнопку fx в строке состояния или (если вы работаете в 2007 excel) на треугольник, расположенный около значка автосуммы, выбрав пункт меню «Другие функции».

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

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

    Категории функций Эксель

    Функции, встроенные в Excel, сгруппированы в несколько категорий:

      1. Финансовые позволяют производить вычисления, используемые в экономических расчетах, связанных обычно с ценными бумагами, начислением процентов, амортизацией и другими показателями;
      2. Дата и время. Эти функции позволяют работать с временными данными, например, можно вычислить день недели для определенной даты;
      3. Математические позволяют произвести расчеты, имеющие отношения к различным областям математики;
      4. Статистические позволяют определить различные категории статистики – дисперсию, вероятность, доверительный интервал и другие;
    1. Для обработки ссылок и массивов;
    2. Для работы с базой данных;
    3. Текстовые используются для проведения действия над текстовой информацией;
    4. Логические позволяют установить условия, при которых следует выполнить то или иное действие;
    5. Функции проверки свойств и значений.

    Правила записи функций Excel

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

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

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

    Для разделения аргументов используется знак «;». Если для вычисления используется массив данных, начало и конец его разделяются двоеточием.

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

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

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

    • кликнуть в строке состояния;
    • нажать на клавиатуре F2;
    • либо два раза щелкнуть мышью по ячейке. (как вам удобнее)

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

    Ошибки в формулах Excel

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

    • ### – ширины столбца недостаточно для отображения результата;
    • #ЗНАЧ! – использован недопустимый аргумент;
    • #ДЕЛ/0 – попытка разделить на ноль;
    • #ИМЯ? – программе не удалось распознать имя, которое было применено в выражении;
    • #Н/Д – значение в процессе расчета было недоступно;
    • #ССЫЛКА! – неверно указана ссылка на ячейку;
    • #ЧИСЛО! – неверные числовые значения.

    Копирование формул Excel

    В случае необходимости формулу можно внести в несколько ячеек. Для этого можно использовать несколько способов:

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

    Абсолютные и относительные ссылки в Эксель

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

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

    • при вертикальном копировании в ссылке изменяется номер строки;
    • при горизонтальном перенесении изменяется номер столбца.

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

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

    Закрепить какую-либо ячейку можно, используя знак $ перед номером столбца и строки в выражении для расчета: $F$4. Если поступить таким образом, при копировании номер ячейки останется неизменным.

    Имена в формулах Эксель

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

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

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

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

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

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

    Источник: pclegko.ru

    Как в Excel создаются формулы и таблицы. Пошагово

    Работа в Excel c формулами и таблицами для чайников. Как же делать формулы и таблицы?

    Формула предписывает программе 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.

    Источник: programmydlyacompa.ru

    10 формул Excel, которые пригодятся каждому

    Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.

    Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

    С помощью формулы вы можете:

    • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
    • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
    • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

    Англоязычный вариант: =COUNT(A1:A10)

    Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

    СЧЁТЗ

    Англоязычный вариант: =COUNTA(A1:A10)

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

    ДЛСТР

    Англоязычный вариант: =LEN(A1)

    Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

    СЖПРОБЕЛЫ

    Англоязычный вариант: =TRIM(A1)

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

    Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

    ЛЕВСИМВ, ПСТР и ПРАВСИМВ

    =ЛЕВСИМВ(адрес_ячейки; количество знаков)

    =ПРАВСИМВ(адрес_ячейки; количество знаков)

    =ПСТР(адрес_ячейки; начальное число; число знаков)

    Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

    Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

    Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

    Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

    Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

    Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

    Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

    1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
    2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
    • B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
    • A1:B13 = таблица, в которой находится искомое значение.
    • 2 – номер столбца, где находится искомое значение.
    • ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

    Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

    Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

    Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

    Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».

    В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

    =ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”)

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

    СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

    Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

    =СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

    Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

    Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

    Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

    Используя функции на предыдущем примере, мы можем узнать:

    СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

    СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

    СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

    Конкатенация

    Формула: =(ячейка1&” “&ячейка2)

    За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&” “&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между ” “, чтобы между объединёнными данными появился пробел.

    Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;” “; B1) или в англоязычном варианте =concatenate(A1;” “; B1).

    Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

    Источник: blog.teachmeplease.ru

    Работа в 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. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» – выбираем формулу для автоматического расчета среднего значения.

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

    Источник: exceltable.com

    ITGuides.ru

    Вопросы и ответы в сфере it технологий и настройке ПК

    Как пользоваться формулами в Excel: основные принципы, особые формулы

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

    Для чего нужны?

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

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

    Для создания формулы необходимо:

    1. Нажать левой кнопкой мыши дважды по ячейке.
    2. Ввести «=».
    3. Ввести «2+2».
    4. Нажать на клавишу «Enter».

    После этого программа автоматически посчитает пример, результат приведён на рисунке ниже.

    Подобным образом можно использовать и иные элементарные вычисления (сложение, вычитание, деление (использовать / для выполнения операции), умножение (использовать * для выполнения операции)).

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

    Варианты работы с формулами

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

    1. Ввести данные в произвольные ячейки. Например, три числа: 15, 28, 35.
    2. В свободной ячейке указать =, адреса ячеек и требуемую операцию между ними. Например, сложение.
    3. Нажать на клавишу «Enter».

    Результат приведён на рисунке ниже.

    Копирование и вставка

    Любые формулы можно копировать и вставлять в разные ячейки. Для этого потребуется:

    1. Ячейку с формулой выделить при помощи мыши.
    2. Нажать сочетание клавиш Ctrl+C.
    3. Выбрать свободную ячейку.
    4. Нажать сочетание клавиш Ctrl+V.

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

    Авто заполнение

    Данная операция позволяет оперативно применить формулу к ряду других ячеек. Она применима для близко расположенных друг к другу ячеек. Для использования потребуется:

    1. Выбрать ячейку с формулой.
    2. Навести мышку на нижний правый угол ячейки. Форма курсора при этом изменится.
    3. Дважды нажать по нему левой кнопкой мыши.

    После этого авто заполнение сработает на ближайшие ячейки.

    Абсолютные и относительные ссылки

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

    1. Выбрать ячейку с формулой, в которой нужно зафиксировать значение.
    2. Добавить к ячейке $.
    3. Нажать на клавишу «Enter».

    После этого значение ячейки не будет изменяться.

    Особые формулы

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

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

    • Создать столбцы, в которые будет помещено новое значение.
    • В формулах выбрать «ВПР».

    • Указать диапазон ячеек, по которым будет выполняться расчёт. В поле «интервальный просмотр» указать значение «ЛОЖЬ», только тогда значения будут точные, а не приблизительные.

    • Нажать на «ОК». После этого столбец будет заполнен, при необходимости его следует растянуть.

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

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


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

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

    Источник: itguides.ru