У цій статті ми поговоримо про те, що таке формула в Google таблицях і як ними користуватися – як вставляти і видаляти, переносити і копіювати, розглянемо формули масивів в таблицях, навчимося вважати використовуючи формули.
Які бувають типи формул у Google таблицях
Формули в Google таблицях бувають двох видів: прості та комплексні. Прості зазвичай містять константи, посилання на комірки всередині робочого листа та оператори. Комплексні формули можуть містити набагато більше даних – посилання на комірки, одночасно кілька функцій, константи, оператори, імена діапазонів.
Елементи формул у Google таблицях
Давайте ближче ознайомимося з елементами формул у гугл таблицях.
Константи – це якісь фіксовані дані, найчастіше цифри.
Оператори – це символи віднімання, складання, поділу та множення, символом поділу служить так званий сліш /.
Посилання на осередки – дуже часто формули містять посилання на комірки всередині робочого листа або посилання на комірки на інших аркушах робочої книги. Посилання бувають трьох типів – відносні, абсолютні та змішані.
Відносні посилання виглядають приблизно так: = 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) і т.д. Все це елементи формул, які можуть творити дива в умілих руках, нижче ми розглянемо кілька прикладів простих та комплексних формул.
Як в Google таблицях вставити формулу, відредагувати її і скопіювати
Усі формули в Google таблицях починаються зі знака рівності = .
Для того, щоб додати нову формулу, виділіть необхідну комірку і поставте курсор у рядок формул:
Або натисніть F2 на клавіатурі.
Для редагування формули необхідно також виділити комірку з формулою, яку ви хочете відредагувати і натисніть F2 на клавіатурі або поставте курсор у рядок формул.
Щоб видалити формулу просто виділіть комірку з формулою та натисніть Delete на вашій клавіатурі.
Копіювати формулу можна двома способами:
- Поставте курсор в комірку з формулою, натисніть клавіші Ctrl + C для копіювання і Ctrl + V для вставки вмісту комірки разом з формулою.
- Виділіть комірку з формулою, клацніть правою кнопкою миші і виберіть у контекстному меню пункт Копіювати, для вставки вмісту комірки разом з формулою виділіть потрібну комірку куди хочемо вставити, клікніть правою кнопкою миші і в контекстному меню виберіть пункт Вставити.
Але що, якщо нам формула у новому осередку не потрібна, а лише значення?
Після того як ми вставили дані в новий осередок з'явиться така підказка:
Після натискання на неї відкриється невелике контекстне меню:
У ньому нам необхідно вибрати пункт Вставити лише значення.
Як створити формулу в Google таблицях з даними розташованими на різних аркушах
Трапляються випадки, коли необхідно порахувати суму значень діапазонів, які розташовані на різних аркушах вашої Google таблиці.
Для того, щоб порахувати в Google таблицях значення, які знаходяться на іншому листі, необхідно використовувати формулу, до складу якої входить посилання на цей лист.
Наприклад, у нас є діапазон даних B4:B8 на першому аркуші:
І ми маємо потребу порахувати ці дані, але на іншому аркуші.
Для цього переходимо на лист2, активуємо комірку, де нам необхідно вивести результат і вводимо наступну формулу: = СУМ( «Аркуш1»! B4: B8).
Зверніть увагу на те, що посилання на Лист1 необхідно взяти в одинарні лапки, інакше формула не буде працювати.
Весь діапазон ми закриваємо в дужки і перед ними підставляємо функцію СУМ, яка сумуватиме всі значення в даному діапазоні.
Але що, якщо нам необхідно порахувати значення з кількох аркушів у потрібних діапазонах?
Наприклад, порахувати дані з вищевказаного діапазону B4:B8 на першому аркуші і дані діапазону B4:B6 на другому аркуші.
У такому разі формула буде виглядати наступним чином: = СУМ('Лист1'!B4:B8;'Лист2'!B4:B6)
Як ви вже зрозуміли, ми просто додали ще один аркуш і вписали його в загальну формулу, розділивши аркуші із діапазонами символом крапка з комою ;
Формули в Google таблицях – приклади
У попередніх розділах статті ми вже розглянули кілька прикладів формул, що містять посилання, у тому числі й посилання на інші аркуші.
У цьому розділі статті ми розглянемо найпростіші формули, формули містять іменовані діапазони і формули масиву в таблицях Google.
Проста формула в Google таблицях
Проста формула, як правило, це формула, що містить пару констант і оператори:
Ця формула виконає множення вмісту в осередках A2 і B4 у нашій Google таблиці.
А ось приклад формули в якій є константа та посилання на комірку:
Така формула складе цифру 25 та вміст комірки C3.
Формули з іменованими діапазонами в Google таблицях
Іменовані діапазони спрощують життя людині, що працює з великим масивом даних у таблицях, у тому числі і Google таблицях.
Для початку нам необхідно дати діапазонам імена, а потім вже ми їх будемо використовувати у формулі.
Отже, для того, щоб діапазону в Google таблицях дати ім'я, необхідно виділити діапазон, якому ми хочемо дати ім'я, потім клікнути правою кнопкою миші і в контекстному меню вибрати пункт Визначити іменований діапазон.
Або пройти шлях у меню панелі керування Дані -> Іменовані діапазони.
Після цього відкриється сайдбар справа, де буде запропоновано ввести ім'я діапазону.За промовчанням у полі для імені буде написано Іменований Діапазон1 (якщо, звичайно, це перший діапазон, якому ми даємо ім'я, в рамках даної таблиці):
Давайте дамо йому назву "Січень". Припустимо, що це розрахунок доходів за січень.
Нижче вказаний буде наш виділений діапазон на даному аркуші. Готово нижче.
Тепер цей діапазон можна використовувати при розрахунках у формулах. Наприклад, ми хочемо підсумовувати весь дохід за січень. варіант, у разі СУММ перетворилося на SUM:
Тепер куди б ми не переносили наш діапазон Січень, він завжди міститиме саме ті дані, які в ньому вказані і ми можемо робити розрахунки, не замислюючись про те, в яких осередках і аркушах він зараз знаходиться.
Давайте спробуємо зробити розрахунок формули з нашим діапазоном, але перенесемо розрахунок на інший лист:
Як бачите, все той же діапазон, те ж ім'я, але інший лист. І ніяких труднощів з осередками і номерами листів! клавіш Ctrl+X для того, щоб вирізати діапазон та Ctrl+V для того, щоб вставити в іншому місці.
Формули масиву в Google таблицях
Масиви зазвичай включають декілька функцій і використовуються масиви для більш складних обчислень.
Наприклад, у нас є таблиця з даними з продажу кількох менеджерів і хочемо дізнатися максимальні продажу конкретного менеджера, конкретного продукту:
Використовуємо для цього таку формулу масиву: =MAX(IF(($A$3:$A$9="Максим")) * ($B$3:$B$9="Груші"), $C$3:$C$9,»» ))
Після введення формули масиву необхідно тиснути не Enter, а Ctrl+Shift+Enter.
Насолоджуємося результатом:
Ми отримали значення максимального продажу Груш менеджером із продажу Максимом.
Ось і добігла кінця ця стаття, сподіваюся вона була корисна. Якщо я щось пропустив або у вас є додаткові питання, то ставте, я намагатимусь відповісти на них.
Як застосувати формулу до всього стовпця в Google Таблицях на комп'ютері
Співавтор(и): Travis Boylls. Тревіс Бойллз – автор і редактор, що пише про технології для wikiHow. Має досвід написання статей на технічні теми, надання комп'ютерної технічної підтримки та графічного дизайну. Спеціалізується на Windows, MacOS, Android, iOS та Linux. Вивчав графічний дизайн у Муніципальному коледжі Пайкс-Пік.
Кількість переглядів цієї статті: 32 398.
У цій статті ми розповімо вам, як на комп'ютері з Windows та Mac OS X застосувати формулу до всього стовпця в Таблицях Google.
Як додавати формули та функції
У таблицях Google можна виконувати різні види обчислень, використовуючи функції для створення формул. Повний список доступних функцій можна знайти тут.
Натисніть кнопку «Створити копію» нижче, щоб отримати зразок таблиці та тренуватися під час перегляду відео.
Як використовувати формули
- Відкрийте таблицю.
- Введіть у комірку знак рівності (=) та назва функції. Примітка. Система може автоматично пропонувати формули та діапазони на основі наявних даних.
- Вікно з назвою і синтаксисом формули, що вводиться, а також прикладами її використання буде відкрито протягом усього редагування.Щоб побачити статтю повністю, натисніть «Докладніше» у нижній частині вікна підказок.
Порада. Вам доступні підказки відповідних функцій формул, які ви вводите. Наслідувати підказки не обов'язково.
Щоб увімкнути або вимкнути підказки, нагорі сторінки натисніть ІнструментиВключити підказки формул.
Додаткові можливості для створення формул
Режим вибору діапазону
- При редагуванні формули поруч із курсором з'явиться значок вибору діапазону у вигляді сірої дужки. Після цього ви можете встановити потрібний діапазон за допомогою стрілок на клавіатурі.
- Щоб увімкнути або вимкнути цей режим, використовуйте швидкі клавіші F2 або Ctrl+E. Коли режим вимкнено, можна перемістити курсор у поле введення, натискаючи стрілки на клавіатурі.
- Щоб вибрати діапазон, можна також виділити потрібні комірки в таблиці.
Редагування діапазону
- Якщо у формулі виділено текст діапазону, натисніть F2 або Ctrl+E, щоб перейти в режим вибору діапазону та внести необхідні редагування.
- Щоб змінити будь-яку частину діапазону всередині формули, натисніть Shift+F2 або Shift+Ctrl+E під час редагування тексту.
Примітка. Діапазон, який використовується у формулі, може містити несуміжні осередки. Щоб вибрати кілька осередків, виділяйте їх по черзі, утримуючи клавішу Ctrl (Cmd у macOS).
Функція, яка використовується в одній і тій же клітинці з іншою функцією, називається вкладеною. Розрахунок починається з функції найнижчого рівня. Вкладена функція полягає в дужках і стає одним із компонентів функції рівнем вище.
Припустимо, необхідно обчислити абсолютне значення суми чисел у діапазоні осередків А1:А7.Щоб підрахувати цю суму, введіть у комірку формулу «=СУМ(A1:A7)».
Щоб отримати абсолютне значення, потрібно об'єднати формулу суми з формулою абсолютного значення. Введіть обидві формули в одну комірку. ()», а її результат буде використаний як аргумент функції «=ABS()».
Кольорове кодування спрощує введення та редагування формул. Коли ви додаєте до формули комірки, вони виділяються різними кольорами.
Щоб зменшити або збільшити панель формул, натисніть її нижню частину і перетягніть вгору або вниз.
Примітка. Щоб змінити розмір панелі, можна також натиснути на неї і використовувати швидкі клавіші:
- Ctrl + стрілка вгору або Ctrl+стрілка вниз (Windows);
- Ctrl + Option + Стрілка вгору або Ctrl + Option + Стрілка вниз (MacOS).
Викликає бібліотеку або програмний ресурс, що динамічно підключається. Таблиці не підтримують цю функцію, оскільки ресурс може бути недоступний на всіх пристроях.
Порада. Замість цієї функції можна використовувати макроси або скрипти програм Google Apps.
Дозволяють працювати з кубами даних у Excel.
Порада. Якщо ви хочете застосовувати схожі функції, то можете скористатися конекторами даних.
Повертає інформацію про файл документа, наприклад, шлях до нього.
Примітка. У Таблицях Google зроблено акцент на спільну роботу в Інтернеті, і значна частина інформації, отримана цим способом, може бути недоступна або незрозуміла більшості користувачів.
Отримує реєстраційний ідентифікатор Windows.
Примітка. Ця функція не підтримується, оскільки Google Таблиці не пов'язані з жодною операційною системою.
Отримує дані з сервера автоматизації COM (Component Object Model).
Порада. Не всі мають доступ до сервера COM, тому ви можете використовувати макроси або скрипти програм Google Apps.
Цілком залежить від Windows.
Примітка. Ця функція не підтримується, оскільки Google Таблиці не пов'язані з жодною операційною системою.
Що таке функція
Формула – це будь-яка послідовність символів, яка вводиться у комірці і починається зі знака рівності (=). Приклад:
Найчастіше формула містить функції. У прикладі нижче показано формулу, що дозволяє за допомогою функцій ЯКЩО та СУМ визначити, чи перевищують витрати встановлений бюджет.
=ЯКЩО(СУМ(A2:A)>C2;"Перевищують бюджет";"Не перевищують бюджет")
Витрати не перевищують встановлений бюджет, оскільки 350 дол. США більше ніж 300 дол. США.
Примітки щодо наведеного вище прикладу
- У діапазон A2:A входять усі значення у стовпці A від рядка 2 до кінця стовпця.
- Формула виходить із того, що витрати вказані в стовпці A, а бюджет – у комірці C2.
- У міру введення значення ви можете бачити прогнозовані вихідні дані у спливаючому вікні.
- Якщо замість будь-якого значення в стовпці A або комірці C2 вказати будь-яке інше, вихідні дані можуть змінитися.
Початок роботи з функціями
- Більшість функцій виконує обчислення, виходячи із зазначених користувачами значень.
- Значення також називаються аргументами чи вхідними даними.
- Для деяких функцій необхідно вказати один або кілька елементів вхідних даних, для інших нічого не потрібно.
- Як елементи вхідних даних можна вказувати значення, а також комірки та їх діапазони.
- Для деяких функцій вказувати вхідні дані необов'язково (якщо цього не зробити, буде встановлено значення за замовчуванням).
Особливості використання функцій з різними типами вхідних даних:
Відсутність вхідних даних
Для функції ТДАТА вхідні дані не вказуються, оскільки для показу поточної дати та часу їй не потрібні жодні відомості.
Щоб скористатися цією функцією, у будь-якій комірці введіть =ТДАТА() і натисніть Ввод.
Один елемент вхідних даних
Для функції ABS необхідно вказати один елемент вхідних даних – значення чи комірку. Приклад:
Декілька елементів вхідних даних
Для функцій SUM та CONCAT можна вказати будь-яке поєднання значень чи адрес осередків. Наприклад, за вхідними даними =SUM(B1, 17, D1:D3) буде розрахована сума значення в комірці B1, числа 17, а також значень у комірках D1, D2 та D3.
Необов'язкові вхідні дані
- Для функції ОКРУГЛ можна вказати один або два елементи вхідних даних. Перший елемент буде заокруглений.
- Якщо ви хочете, щоб перший елемент був округлений до певної кількості знаків після коми, вкажіть таке число у другому елементі.
- Якщо не вказати другий елемент вхідних даних, округлення виконуватиметься до найближчого цілого. Цей необов'язковий елемент полягає у квадратних дужках:
=ОКРУГЛ(123,456; 2) . Число округляється до двох знаків після коми.
=ОКРУГЛ(123,456) . Число округляється до найближчого цілого.
Як дізнатися, які елементи вхідних даних потрібні для певної функції
Дізнатися, які елементи вхідних даних приймаються тією чи іншою функцією, можна наступними способами:
- Скористайтеся списком функцій Таблиць Google. У ньому вказано назву та опис кожного аргументу. Приклад:
- Щоб дізнатися, які вхідні дані приймаються, або вивчити докладну інформацію про функцію, натисніть стрілку вниз.
- Коли ви вводите той чи інший елемент вхідних даних, приклад такого елемента у підказці виділяється зеленим.
Про вихідні дані
Під вхідними даними розуміється результат використання функції. ТДАТА, ABS, СУМ та ОКРУГЛ, як результат надають одне значення.
Однак вихідні дані деяких функцій з'являються у кількох осередках. Наприклад, функція. UNIQUE повертає список унікальних значень діапазону, вказаного у вхідних даних.
У прикладі нижче вхідні дані є діапазоном з семи осередків, а у вихідних даних представлені лише чотири з них.
Related posts:
Як вставити формулу в гугл таблиці - Druzhba.v.ua У нашому детальному гайді по Google Таблицям для новачків ми торкнулися роботи з формулами (функціями), але не зупинилися на ній докладніше, тому що це тема для окремої статті. підрахунок різних показників, структурувати дані, і навіть перекладати текст з однієї мови іншою, об'єднувати між собою різні масиви даних і так Далі. При роботі з великими обсягами даних працювати без формул просто неможливо.
Як вводити формули
- Виберіть комірку, в якій хочете зробити формулу.
Сервіс сам запропонує варіанти формул
- Після того, як ви ввели формулу, заповніть її параметри. У різних формул вони, відповідно, різні:
У деяких функцій потрібно буде заповнити параметри та умови
- Далі потрібно вибрати діапазон (комірку, стовпець, рядок), який формула використовуватиме, щоб перетворювати дані:
Діапазон можна вибрати, відкривши дужки та клацнувши на потрібну комірку, рядок або стовпець. А якщо потрібно вибрати кілька осередків/стовпців/рядків поспіль, потрібно затиснути Shift і натискати на них
Якщо все зроблено правильно, формула почне працювати. Якщо ні, то з'явиться піктограма помилки, при натисканні на яку можна отримати розшифровку та інструкцію, як виправити помилку:
Є десятки можливих помилок, все можна виправити, прочитавши розшифровку
Є ще один спосіб ввести формулу – перейти до «Вставки» → «Функції» та вибрати формулу зі списку:
Тут представлені всі можливі формули Google-таблиць
Огляд важливих функцій для digital-фахівця
Формул та функцій тут дуже багато. Не будемо зупинятись на простих (наприклад, формула для підсумовування), але розповімо про складніші, але не менш потрібні.
Список доступних функцій – у довідці Google.
Підрахунок символів
= ДЛСТР (осередок)
Ця формула вважає кількість символів у комірці. Багато хто оформляє контент-плани в Google-таблицях. Враховуючи те, що більшість соцмереж є обмеження за кількістю символів у пості, ця формула дуже доречна.
Робота формули = ДЛСТР
Переведення тексту з верхнього регістру до нижнього (і навпаки)
= РЯДКОВИЙ (діапазон)
Формула переводить текст, написаний із використанням Caps Lock, у нижній регістр.
Робота формули = РЯДКОВИЙ (діапазон)
=ПРОПИСН(діапазон)
Ця формула, навпаки, переводить текст у верхній регістр.
Робота формули = ПРОПИСН (діапазон)
Ці формули можуть стати в нагоді під час, наприклад, вивчення аудиторії конкурентів – якщо ви скопіювали список імен та прізвищ із сервісу, який парсить акаунти, а він у верхньому регістрі (що буває), за допомогою формули можна буде легко перевести текст у нижній регістр.
Pepper Ninja, Popsters, Adinblog, Spywords. цими сервісами варто користуватись кожному підприємцю
Імпорт даних із інших джерел
=IMPORTHTML("посилання"; "table"; 0; "rus_RU")
За допомогою цієї формули можна перенести будь-яку таблицю або список до своєї Google Таблиці. Для цього потрібно вставити у формулу посилання, яке веде на сторінку, де міститься контент, який потрібно імпортувати.
Таким чином, вдасться імпортувати той контент, який спочатку зроблено у вигляді таблиці або списку – наприклад, рейтинг спортивних команд
Щоб імпортувати список та зберегти його формат у вигляді списку, потрібно у параметрах вибрати не "table", а "list". Всі інші параметри потрібно залишити як є.
Вашу заявку прийнято.
Ми зв'яжемося з вами найближчим часом.Переклад тексту
=GOOGLETRANSLATE(осередок; "вихідна_мова" або "auto"; "цільова_мова")
Формула перекладає з будь-якої мови. І будь-якою мовою. Більшість мов навіть не потрібно вказувати як вихідну – сервіс автоматично визначає мову оригіналу, для чого потрібно вписати команду «auto».
Трюк не спрацював із чуваською мовою, якої немає у сервісі Google Перекладач. Ймовірно, з іншими не дуже популярними мовами буде схожа історія
Об'єднання осередків
=CЧЕПИТИ(діапазон;діапазон)
Формула дозволяє об'єднати текст із двох осередків в одному.
Щоб текст не «склеився», поставте між діапазонами, що об'єднуються, аргумент “ “
Ця формула може стати в нагоді в різних ситуаціях. Наприклад, коли у вас є список імен та номерів, які потрібно об'єднати в один осередок.
Ще ця функція стане в нагоді для комбінування різних ключових фраз.
Поділ фраз
=SPLIT(осередок; "розділювач"; [тип_розділювача]; [виконувати_чи_видалення_порожніх_осередків])
Те саме, що об'єднання даних із осередків, тільки навпаки. Якщо ви спарили список контактів звідкись, то в одному рядку може бути кілька параметрів: ім'я, місце проживання, телефон. Формула допоможе все розділити і позбутися непотрібних символів (коми, цифри, прогалини).
Потрібно вказати тип роздільника, який видалятиметься, у прикладі це “,”
Видалення зайвих прогалин
=СЖПРОБІЛИ(осередок)
Ця формула видаляє лише зайві прогалини, залишаючи потрібні. Стане в нагоді, тим, хто набирає великі обсяги тексту вручну.
На скріншоті можна помітити, що в деяких місцях тексту було по 2-3 пробіли, які формула успішно видалила, залишивши лише один