Google таблицы не работают формулы

Google Sheets формулы

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

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

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

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

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

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

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

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

Относительные ссылки выглядят примерно так: =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 на вашей клавиатуре.

Скопировать формулу можно двумя способами:

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

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

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

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

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

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

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

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

Например, у нас есть диапазон данных 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 .
Наслаждаемся результатом:

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

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

Источник

Cоветы и руководства по Google Таблицам

Мастер Google Таблиц с полезными советами и руководствами

Как показывать формулы вместо значений в Google Таблицах

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

К счастью, в Google Таблицах есть несколько способов заставить ячейку отображать информацию о формуле вместо значения.

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

Показать формулы на всем листе

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

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

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

В этой таблице для добавления значений ячеек используется только формула = sum ().

Мы используем формулы в ячейках E2-E4, B5-D5 и E7.

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

• Откройте «Просмотр» в строке меню.
• В списке параметров выберите «Показать формулы». Этот параметр будет отображаться для любой электронной таблицы, в которой используется хотя бы одна формула.

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

Вы можете снова переключить представление на «отображение значений», снова выбрав тот же параметр в меню.

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

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

Показать горячие клавиши в представлении формул

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

Активируйте этот ярлык, одновременно нажав «Control», «Plus» и «Accent» (Ctrl + `).

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

Щелчок по ячейкам

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

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

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

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

• Выберите ячейку, которую хотите исследовать.
• Дважды щелкните ячейку, чтобы включить подробный просмотр
• Щелкните значок «?» рядом с ячейкой или нажмите клавишу «F9», чтобы активировать всплывающее окно предварительного просмотра значений.

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

Показать формулу, преобразовав ее в текст (метод апострофа)

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

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

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

В нашем примере мы меняем формулу в ячейке E7 с «= sum (E2: E4)» на «’= sum (E2: E4)», чтобы форматирование отображало формулу вместо значения.

Google Таблицы скроют апостроф с дисплея и просто покажут формулу.

После добавления апострофа в ячейке появится следующая формула:

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

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

Источник