Как сделать условия в гугл таблицах

Обновлено: 04.07.2024

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

Какие бывают типы формул в Гугл таблицах

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

Элементы формул в Гугл таблицах

Давайте поближе познакомимся с элементами формул в гугл таблицах.

Константы – это какие-то фиксированные данные, чаще всего цифры.

Операторы – это символы вычитания, сложения, деления и умножения, символом деления служит так называемый слеш /.

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

Относительные ссылки выглядят примерно так: =A2-A4 .

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

Абсолютные ссылки выглядят таким образом: =$A$2-$A$4

Теперь куда бы мы не скопировали нашу простую формулу, то ссылки по прежнему будут указывать на ячейки A2 и A4: =$A$2-$A$4

Смешанные ссылки имеют такой вид: A$2-A$4

Скопировав данную формулу в нашу новую ячейку C1, мы получим следующий результат: =С$2-С$4

Как видим, столбец сменился на C, потому что ссылка столбца относительная, а строки остались по прежнему 2 и 4, поскольку на строки мы указали абсолютные ссылки.

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

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

Функции – так же как и в excel, гугл таблицы имеют функции. Например, СУММ (SUM), СЧЕТ(COUNT) и т.д. Все это элементы формул, которые могут творить чудеса в умелых руках, ниже мы рассмотрим несколько примеров простых и комплексных формул.

Как в Гугл таблицах вставить формулу, отредактировать ее и скопировать

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

Или нажмите F2 на вашей клавиатуре.

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

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

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

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

После того как мы вставили данные в новую ячейку появится вот такая подсказка:

По нажатию на нее откроется небольшое контекстное меню:

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

Как создать формулу в Гугл таблицах с данными расположенными на разных листах

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

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

Например, у нас есть диапазон данных B4:B8 на первом листе:

И у нас есть потребность посчитать эти данные, но на другом листе. Пусть это будет лист2.
Для этого переходим на лист2, активируем ячейку где нам необходимо вывести результат и вводим следующую формулу: =СУММ('Лист1'!B4:B8) .

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

Но что, если нам необходимо посчитать значения с нескольких листов в нужных нам диапазонах?
Например, посчитать данные из вышеуказанного диапазона B4:B8 на первом листе и данные диапазона B4:B6 на втором листе. И все это суммировать в одной ячейке.

В таком случае формула будет выглядеть следующим образом: =СУММ('Лист1'!B4:B8;'Лист2'!B4:B6)
Как вы уже поняли, мы просто добавили еще один лист и вписали его в общую формулу, разделив листы с диапазонами символом точка с запятой ; .

Формулы в Гугл таблицах – примеры

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

Простая формула в Гугл таблицах

Простая формула, как правило это формула содержащая пару констант и операторы:

Данная формула выполнит умножение содержимого в ячейках A2 и B4 в нашей Гугл таблице.

А вот пример формулы в которой есть константа и ссылка на ячейку:

Такая формула сложит цифру 25 и содержимое ячейки C3.

Формулы с именованными диапазонами в Гугл таблицах

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

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

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

Либо пройти путь в меню панели управления Данные -> Именованные диапазоны..

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

Ниже указан будет наш выделенный диапазон на данном листе. Его можно поменять и в том числе сам Лист. Жмем Готово ниже.

Теперь этот диапазон можно использовать при расчетах в формулах. Например, мы хотим суммировать весь доход за Январь. Для этого добавим ячейку Январь и рядом с ней в ячейку введем следующую формулу: =СУММ(Январь) — по умолчанию Гугл таблицы все формулы переводят на английский вариант, в данном случае СУММ превратилось в SUM:

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

Давайте теперь попробуем произвести расчет формулы с нашим диапазоном, но перенесем расчет на другой лист:

Как видите, все тот же диапазон, то же имя, но другой лист. И никаких затруднений с ячейками и номерами листов! Точно так же мы можем переместить (не скопировать!) наш диапазон в другое место или даже лист. Для этого просто нажимаем сочетание клавиш Ctrl + X для того, чтобы вырезать диапазон и Ctrl + V для того, чтобы вставить в другом месте.

Формулы массива в Гугл таблицах

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

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

Используем для этого следующую формулу массива: =MAX(IF(($A$3:$A$9="Максим") * ($B$3:$B$9="Груши"), $C$3:$C$9,""))
После ввода формулы массива необходимо жать не Enter , а Ctrl + Shift + Enter .
Наслаждаемся результатом:

Мы получили значение максимальной продажи Груш менеджером по продажам Максимом.

Вот и подошла к концу данная статья, надеюсь она была полезна. Если я что-то пропустил или у вас есть дополнительные вопросы, то задавайте, я постараюсь ответить на них.

Google Sheets — онлайн-аналог Microsoft Excel. С ним можно работать и в офлайне, правда только из браузера Chrome. В этой статье мы научим вас пользоваться Гугл Таблицами и расскажем об их функционале.

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

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

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

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

Четвертый и пятый блоки позволяют выбирать шрифт и его размер.

Шестой блок содержит инструменты для форматирования шрифтовых начертаний и цветов:

Полужирный.
Курсив.
Зачеркнутый.
Цвет текста.

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

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

Девятый блок содержит 5 полезных опций:

Вставить ссылку.
Добавить комментарий.
Сформировать диаграмму.
Фильтровать.
Применить формулу.

В десятом блоке находится кнопка для активации экранной клавиатуры и ввода текста в рукописном режиме.

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

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

Что можно делать с отдельными элементами в Google Таблицах

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

Сортировка и фильтрация данных

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

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

Фильтрация от А до Я и наоборот работает аналогично сортировке. Здесь же можно выбрать различные условия для фильтра. Можно создавать несколько фильтров и накладывать их один на другой.

История изменений документов в Google Таблицах

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

В Google Таблицах есть еще немало опций, которые пригодятся любому. Это и формулы, и вставка диаграмм, и различные дополнения. Изучайте все возможности сервиса и делитесь своими впечатлениями с сообществом 1C-UMI в социальных сетях ― ВКонтакте и Facebook!

Гугл таблицы — что это и как пользоваться? Как создать Google таблицу? Как закрепить строку или столбец? Как сделать гугл таблицу доступной для всех? Как работать с фильтрами? Как сохранить в Excel? Ответим на эти и многие другие вопросы в нашей инструкции для новичков.

Изучив наш материал, вы станете продвинутым пользователем Google-таблиц. Для удобства освоения, материал состоит из текста со скриншотами, а также снабжен дополнительно видеоуроками. Приступим.

Общие вопросы работы с Google-таблицами

Как создать Google таблицу

Чтобы работать с Google таблицами, вам нужен аккаунт в Google. Создайте почту на gmail и авторизуйтесь.

Выберите Личный тариф и откройте таблицы

Кликните на Пустой файл

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

Новая таблица создана

Чтобы открыть таблицу, созданную на компьютере в программе Microsoft Excel, войдите в гугл-таблицы, кликните “Открыть окно выбора файлов”.

Откройте окно выбора файлов

Перейдите во вкладку “Загрузка” и выберите файл на компьютере.

Выберите файл с компьютера

Если вы уже создали новую таблицу, кликните “Файл” — “Открыть”. Или воспользуйтесь горячими клавишами Ctrl+O.

Кликните Файл — Открыть

Выберите нужный файл — вы увидите, как данные сразу появятся на листе.

Листы: как создать, удалить, скрыть

Для любых действий с листом в таблице кликайте на него правой кнопкой мыши или нажмите на “стрелочку”.

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

На скриншоте во втором столбце результат действия формулы, а в третьем – текст самой формулы.

Функция TODAY (СЕГОДНЯ)

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

Функция NOW (ТДАТА)

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

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

Сверху неотформатированная ячейка, снизу – ячейка с временным форматом.

Функция NETWORKDAYS (ЧИСТРАБДНИ)

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

Между первым октября 2015 года и 18 февраля 2016 года – 101 рабочий день.

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

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

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

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

Рабочих дней стало 95.

Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL)

И рабочих дней стало 77.

Функция WORKDAY (РАБДЕНЬ)

возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):

В данном примере мы ссылаемся на ячейку B7, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.

Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД)

возвращают соответствующий параметр из даты, которая является единственным аргументом этой функции:

В примере аргумент – сегодняшняя дата, заданная функцией TODAY.

Функция WEEKNUM (НОМНЕДЕЛИ)

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

Функция DATEDIF (РАЗНДАТ)

вычисляет количество дней, месяцев и лет между двумя датами.

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

M – полные месяцы;

Y – полные годы. В примере находим разницу между 1 октября 2015 и 18 февраля 2016:

Видимо, что полного года еще не прошло – но можно вычислить десятичную долю года, воспользовавшись функцией YEARFRAC (ДОЛЯГОДА)

У DATEDIF есть еще три варианта последнего аргумента:

MD – количество дней после вычитания полных месяцев ( в примере 17 дней между 01.01.2013 и 18.02.2016);

YM – количество месяцев после вычитания полных лет ( в примере 1 месяц между 01.01.2013 и 18.02.2016);

YD – количество дней

после вычитания полных лет ( в примере 48 дней между 01.01.2013 и 18.02.2016).

Функция EOMONTH (КОНМЕСЯЦА)

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

Вернет 30.04.2016 в феврале (апрель = два месяца от февраля):

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

Функция WEEKDAY (НОМНЕДЕЛИ)

возвращает порядковый номер дня недели указанной даты (первый аргумент). Второй аргумент – тип, как у функции WEEKNUM. Для недели, начинающейся с понедельника, тип = 2:

Если же аргумент тип = 1, то четверг становится пятым днем недели, как видно на скриншоте:

Наконец, несколько функций для работы со временем. TIME (ВРЕМЯ)

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

Ну а SECOND (СЕКУНДЫ), MINUTE (МИНУТЫ) и HOUR (ЧАС)

возвращают одно соответствующее значение из даты. На скриншоте выше видно, что заснял я его в 22 часа, 22 минуты и 27 секунд – ведь во всех трех функциях в качестве аргумента задано текущее время – NOW().

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