Как в экселе пользоваться формулами: Функции Excel (по категориям) — Служба поддержки Майкрософт

Как написать и ввести формулу в Эксель онлайн


Формулы нужны в Excel® для обработки чисел и текстовых значений в ячейках таблицы. Рассмотрим порядок работы с формулами.

Самостоятельное создание формулы


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


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


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

  • Сумма,
  • Финансовые,

  • Логические,

  • Текстовые,

  • Дата и время,

  • Ссылки и массивы,

  • Математические и тригонометрические,

  • Другие функции.


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


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


Например, вычислм среднее. В ячейке появится запись =СРЗНАЧ() с курсором между скобками. Выберете диапазон ячеек для расчета среднего значения и нажмите Enter. В ячейке вы получите результат вычисления.


Рассмотрим более сложные формулы. Например, выберите формулу Если. В ячейке появится запись = ЕСЛИ(). Выберите ячейку для анализа и нажмите на любой арифметический знак, например =. Появится подсказка вида ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь). В ней есть 3 составляющие:

  • лог_выражение в ней – это собственно, запись сравнения.

  • значение_если_истина – это текст или число, которые надо сюда ввести или ссылка на конкретную ячейку в случае, когда логическое условие выполняется.

  • значение_если_ложь – то же самое в случае, когда логическое условие не выполняется.


При введении этих составляющих их надо разделять точкой с запятой. Первое из них должно быть введено обязательно. Если второе и третье составляющие не введены, результатом сравнения будут слова ИСТИНА или ЛОЖЬ соответственно.

Просмотр формулы


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


® — является зарегистрированной товарной маркой, все права на которую принадлежат корпорации Microsoft.

Полезные функции Google Таблиц, которых нет в Excel / Хабр

Cтатья написана в соавторстве с Ренатом Шагабутдиновым.

В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).


Оглавление:

— Если результат формулы несколько ячеек
— Объединение нескольких диапазонов данных для использования в формулах
— SORT
— Как в SORT добавить заголовки таблицы?
— FILTER
— FILTER, два условия и работа с датой
— Интерактивный график при помощи FILTER и SPARKLINE
— IMPORTRANGE
— Импорт форматирования из исходной таблицы
— IMPORTRANGE как аргумент другой функции
— IMAGE: добавляем изображения в ячейки
— GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

Если результат формулы занимает больше одной ячейки

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

SORT


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

Синтаксис функции:

=SORT(сортируемые данные; столбец_для_сортировки; по_возрастанию; [столбец_для_сортировки_2, по_возрастанию_2; …])


Пример на скриншоте ниже, мы ввели формулу только в ячейку D2 и сортируем данные по первому столбцу (вместо ИСТИНА/ЛОЖЬ можно вводить TRUE/FALSE).

(здесь и далее — примеры для российских региональных настроек таблицы, рег. настройки меняются в меню файл → настройки таблицы)

Как в SORT добавить заголовки таблицы?

С помощью фигурных скобок {} создаем массив из двух элементов, шапки таблицы A1:B1 и функции SORT, элементы отделяем друг-от-друга с помощью точки с запятой.

Как объединить несколько диапазонов данных и отсортировать (и не только)?

Давайте рассмотрим, как можно объединять диапазоны для использования в функциях. Это касается не только SORT, этим приемом можно пользоваться в любых функциях, где это возможно, например в ВПР или ПОИСКПОЗ.

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

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

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

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

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

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

Все разобранные примеры можно рассмотреть поближе в
Google Документе.

FILTER


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

Синтаксис функции:

FILTER(диапазон; условие_1; [условие_2; …])

Одно условие

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

Введем в ячейку E3 вот такую формулу:

=FILTER(A3:C7;B3:B7=«Наталья Чистякова»)


Обратите внимание, синтаксис немного отличается от привычных формул, вроде СУММЕСЛИН, там диапазон условия и само условие отделялось бы при помощи точки с запятой.

Введенная в одну ячейку формула возвращает нам массив из 9-ти ячеек с данными, но после примеров с функцией SORT мы этому уже не удивляемся.

Помимо знака равенства (=) в условиях можно использовать еще >, >=, <> (не равно), <, <=. Для текстовых условий подходят только = и <>, а для чисел или дат можно использовать все эти знаки.

Два условия и работа с датой

Давайте усложним формулу и добавим в нее еще одно условие, по дате продаж, оставим все продажи начиная с 01.02.17

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

=FILTER(A3:C7;B3:B7=«Наталья Чистякова»;A3:A7>=ДАТАЗНАЧ(«01. 02.17»))


Или вот так, если ссылаться на ячейки с аргументами:

=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)

Интерактивный график при помощи FILTER и SPARKLINE

А знаете, как еще можно использовать функцию FILTER? Мы можем не выводить результат функции на рабочий лист, а использовать его как данные для другой функции, например, спарклайна. Спарклайн — это функция, которая строит график в ячейке на основе наших данных, у спарклайна существует много настроек, таких, как вид графика, цвет элементов, но сейчас мы не будем на них останавливаться и воспользуемся функцией без дополнительных настроек. Перейдем к примеру.

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

  • выделяем ячейку Е2;

  • меню Данные → Проверка данных;

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

Нажимаем «Сохранить» и получаем выпадающий список в выбранной ячейке:

Ячейка с выпадающим список станет условием для формулы FILTER, напишем ее.

=FILTER(C3:C7;B3:B7=E2)


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

=sparkline(FILTER(C3:C7;B3:B7=E2))

Так это выглядит в динамике:

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

IMPORTRANGE


Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.

В каких случаях она может пригодиться?

  • Вам нужны актуальные данные из файла ваших коллег.

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

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


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

Синтаксис формулы следующий:

IMPORTRANGE(spreadsheet key; range string)

IMPORTRANGE(ключ; диапазон)


spreadsheet_key (ключ) — последовательность символов атрибута «key=» (ключа) в ссылке на таблицу (после «spreadsheets/…/»).

Пример формулы с ключом:

=IMPORTRANGE(«abcd123abcd123»; «sheet1!A1:C10»)


Вместо ключа таблицы вы можете использовать полную ссылку на документ:

=IMPORTRANGE(«docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc»; «Лист1!A1:CM500»)


В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.

Если в исходном файле может меняться количество столбцов или строк, вводите во втором аргументе функции открытый диапазон (см. также подраздел «Диапазоны вида A2:A»), например:

Лист1!A1:CM (если будут добавляться строки)

Лист1!A1:1000 (если будут добавляться столбцы)

! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные). Они как бы “зарезервируются” под весь открытый диапазон — ведь его размерность неизвестна заранее.

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

Так, если в ячейку A1 вы введете ссылку на документ (без кавычек), из которого нужно загрузить данные, а в ячейку B1 — ссылку на лист и диапазон (тоже без кавычек), то импортировать данные можно будет с помощью следующей формулы:

=IMPORTRANGE(A1;B1)

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

Импорт форматирования из исходной таблицы


Как мы уже заметили, IMPORTRANGE загружает только данные, но не форматирование исходной таблицы. Как с этим быть? Заранее «подготовить почву», скопировав форматирование из исходного листа. Для этого зайдите на исходный лист и скопируйте его в вашу книгу:

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

После копирования листа выделите все данные (нажав на левый верхний угол):

И нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:

IMPORTRANGE как аргумент другой функции

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

Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.

Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз).

Сначала импортируем этот диапазон:

IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»)


А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):

=СРЗНАЧ(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))

=AVERAGE(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))


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

IMAGE: добавляем изображения в ячейки


Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения.

У функции следующий синтаксис:

IMAGE(URL, [mode], [height], [width])


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

=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

Или же поставить ссылку на ячейку, в которой ссылка хранится:

= IMAGE(B1)

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

На практике бывает, что ссылки на изображения хранятся на отдельном листе, и вы достаете их с помощью функции ВПР (VLOOKUP) или как-то иначе.

Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):

  1. изображение растягивается до размеров ячейки с сохранением соотношения сторон;

  2. изображение растягивается без сохранения соотношения сторон, целиком заполняя

  3. изображение вставляется с оригинальным размером;

  4. вы указываете размеры изображения в третьем и четвертом аргументам функции [height] и [width]. [height], [width], соответственно, нужны только при значении аргумента mode = 4. Они задаются в пикселях.


Посмотрим, как на практике выглядят изображения с четыремя разными значениями аргумента mode:

Четвертый режим может быть удобен, если вам нужно подбирать точный размер изображения в пикселях, меняя параметры height (высота) и width (ширина). Картинка будет сразу обновляться.

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

GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках


В Google Таблицах есть занятная функция GOOGLETRANSLATE, позволяющая переводить текст прямо в ячейках:

Синтаксис функции следующий:

GOOGLETRANSLATE (text,[source_language], [target_language])

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

[source_language] – язык, с которого мы переводим;

[target_language] – язык, на который мы переводим.

Второй и третий аргументы задаются двухзначным кодом: es, fr, en, ru. Их тоже можно указать в самой функции, но можно брать из ячейки, а язык исходного текста и вовсе можно автоматически определять.

А как быть, если мы хотим переводить на разные языки? И при этом не хотим каждый раз указывать язык исходника вручную?

Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:

Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на 10 языков и так далее. Конечно, мы понимаем, что это текст онлайн-переводчика – качество будет соответствующим.

Евгений Намоконов и Ренат Шагабутдинов, а еще мы ведем канал в телеграмме, где разбираем разные кейсы с Google Таблицами, если вам интересно — заглядывайте в гости, ссылку можно найти в моем профиле.

Как использовать формулы Excel (Руководство для начинающих)

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

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

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

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

Для начала откройте документ Excel.

Выберите одну из ячеек в документе Excel. Затем введите символ =, чтобы Excel мог определить, что вы собираетесь ввести в документ новую формулу. В этом случае ячейки A1, A2 и A3 будут используется в качестве примера формулы. Выберите ячейку A3 и введите формулу = A1 + A2, это приведет к тому, что обе ячейки будут чередоваться, таким образом показывая результат в ячейке A3.

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

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

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

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

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

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

Как использовать формулы Excel для Mac с помощью копирования и вставки?

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

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

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

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

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

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

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

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

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

Using Basic Formulas and Functions in Microsoft Excel

Browse

Chevron Right

Business

Chevron Right

Business Essentials

Filled StarFilled StarFilled StarFilled StarHalf Faded Star

4.7

stars

414 ratings

Offered By

24,191 уже зачислен

В этом проекте с бесплатными руководствами вы:

2 часа

Подходит для начинающих

Загрузка не требуется

Видео с разделенным экраном

Английский

Только рабочий стол

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

Требования

Навыки. Вы разработаете

  • Анализ данных

  • Числа (таблица)

  • Расчеты в таблицах

В видео, которая играет в разделе «Работа с вашим

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

Paula del Rey

Эксперт по вопросам

Freedom Learning Group

86 820 Учащиеся

24 КУРКСЫ

Обзоры

4. 7

. 5 зв.0003

0.72%

  • 1 star

    1.69%

    • TOP REVIEWS FROM USING BASIC FORMULAS AND FUNCTIONS IN MICROSOFT EXCEL

    Filled StarFilled StarFilled StarFilled StarFilled Star

    by TG

    May 21, 2022

    This Guided project было Замечательно. Проект был очень полезен для понимания всех основных формул и функций, используемых в Microsoft Excel для расчетов и анализа.

    Заполненная звездаЗаполненная звездаЗаполненная звездаЗаполненная звездаЗвезда

    by MA

    28 ноября 2021 г.

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

    заполненные звезды StarFiled StarFiled Starfiled Star

    от RA

    14 сентября 2022

    Это было легко понять и работать вместе с инструктором

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

    Читайте также: