Привіт, друзі! Ви знаходитесь на блозі « » і в цій статті створимо Google документи та таблиці онлайн. У попередній статті ми розглянули, як створити Google документи онлайн.
Робимо звіт у Google Docs онлайн - посібник новачкам
У попередній статті ми детально розглянули, що являє собою онлайн сервіс Google Docs, навіщо він потрібен, і як у ньому створювати документи. Для тих, хто не читав попередню статтю, нагадаю, що онлайн сервіс Google Docs призначений для онлайн роботи з різними документами одночасно сотням людей.
В режимі онлайн люди можуть виправляти, коментувати та просто читати Ваші документи, Вам достатньо дати посилання на цей документ. Онлайн сервіс Google Docs користуються як професіонали, так і новачки. Цей інструмент незамінний при проведенні навчання та підготовці різних звітів. Нагадаю, що 95-97% новачків не вміють працювати в онлайн Google Docs.
Для висвітлення всіх можливостей онлайн сервісу Google Docs потрібно створювати великий повноцінний відео-курс, який є в Інтернеті. Початківцям для роботи в даному сервісі достатньо працювати оналайн з Google документами та таблицями, що дозволить виконувати звіти та працювати з документами своїх колег. У попередній ми детально розглянули, як отримати доступ до Google Docs та порядок роботи з Google документами в онлайн режимі.
У цій статті докладно буде розглянуто порядок роботи з таблицями Google онлайн (Google Docs). Отже, для роботи в онлайн з Google таблицями заходимо на сторінку «Диск Google». Далі натискаємо кнопку «Створити», виринає меню, де вибираємо функцію «Таблиця» та натискаємо на неї (дивіться скрін).
У новому вікні відкривається таблиця схожа на Excel, із цією офісною програмою, сподіваюся, всі знайомі.
Робота в Excel це окрема тема, і її тут розглядати не будемо. Розглянемо лише створення простої таблиці для подальшої демонстрації можливостей онлайн-сервісу Google документи і зокрема функції таблиці.
Для створення таблиці, виділяємо мишкою її межі (дивіться скрін),
вибираємо функцію «Кордони» і вибираємо, наприклад, «Всі кордони». Вийшла таблиця, яку тепер можна заповнювати (дивіться скрін).
Як видно, панель керування візуально зрозуміла. Можна вибрати будь-яку потрібну функцію, шрифт, розмір та колір шрифту. За допомогою функції «Вигляд» можна закріпити рядки та стовпці. За допомогою функції «Вставка» можна додати рядки нижче та вище, а також додати стовпці праворуч або ліворуч.
За допомогою функції «Вирівнювання по горизонталі» (дивіться скрін 3, виділено помаранчевим кольором), можна розмістити запис по центру, ліворуч, праворуч. Можна вирівняти по вертикалі. Якщо Ви не знаєте Excel, треба трохи потренуватися і все буде зрозуміло. До речі, тут, як і з Google документом онлайн нічого зберігати не треба, просто, закриваєте сторінку і все. До будь-якої раніше версії завжди можна повернутися.
Тепер для створеної таблиці потрібно дати назву, для цього у верхньому лівому куті натискаємо на напис «Нова таблиця» (див. скрін 3). У новому вікні, в полі прописуємо назву таблиці, наприклад, «Статистика роботи» і натискаємо «Ок».
Тепер можна заповнювати таблицю. Як приклад, я покажу вам мою заповнену таблицю – статистику моєї роботи на рекламних майданчиках Ротабан, дивіться скрін. За бажання таблицю можна налаштувати для виконання розрахунків.
Тепер, коли таблицю створено, потрібно налаштувати доступ документа. Це робиться так само, як і для налаштування Google документа онлайн (дивіться ). У верхньому правому куті натискаємо кнопку «Налаштування доступу», вибираємо режим, наприклад, «Редагувати можуть всі, хто має посилання» і натискаємо «Готово». Якщо щось не ясно, подивіться скрини попередньої . Тепер після наведення мишкою на кнопку «Налаштування доступу» Ви можете бачити, який режим доступності для користувачів у Вас виставлений. Копіюємо посилання таблиці Google онлайн та відправляємо її користувачам.
Як бачите, нічого складного нема, все дуже просто. Ми навчилися створювати Google документи та таблиці онлайн у сервісі Google Docs. Весь процес роботи з таблицями в Google docs Ви можете переглянути в цьому відео:
У міру появи документів, головна сторінка онлайн сервісу ними заповнюватиметься, захаращуючи її. Для того щоб упорядкувати Google онлайн документи та таблиці їх краще помістити в окремі папки. Для створення папки натискаємо кнопку «Створити» і вибираємо функцію «Папка» (дивіться скрін 1). Далі Google документи та таблиці онлайн складаємо в папки за приналежністю простим перетягуванням.
І ще кілька слів про сумісність онлайн сервісу Google Docs з різними браузерами – він чудово працює з Google Chrome, Firefox, Safari починаючи з 4 версії та вище, Internet Explorer починаючи з 8 версії та вище. Коректна робота з іншими браузерами розробником не гарантована.
В онлайн сервісі Google Docs можна створювати не тільки документи та таблиці Google онлайн, а можна створювати презентації та працювати з зображеннями - підтримується png і jpeg розширення. Сервіс дозволяє працювати з документами у форматі PDF. Ви можете також встановити Google Диск на свій комп'ютер і працювати автономно в автономному режимі, а коли потрібно буде перейти в онлайн режим. Можна також використовувати різні програми від сервісу, їх там багато.
У першій і другій частині цієї статті ми розглянули процес, як створити Google документи та таблиці онлайн, як зробити ці документи доступні іншим користувачам. Тепер у новачків не повинно бути питань щодо підготовки своїх звітів у процесі навчання. Цим інструментом зручно працювати і з партнерами з Інфобізнесу. Наприклад, Ви ведете спільний проект із заробітку через Інтернет – через Google Docs легко можна відредагувати документи спільного бізнесу. На цій хвилі закінчуватиму, удачі Вам!
Отримуйте нові статті блогу прямо на пошту. Заповніть форму, натисніть кнопку "Підписатися"
- Tutorial
У цій статті йдеться про кілька дуже корисних функцій Google Таблиць, яких немає в Excel (SORT, об'єднання масивів, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)
Дуже багато літер, але є розбір цікавих кейсів, всі приклади, до речі, можна розглянути ближче в Google Документі goo.gl/cOQAd9 (файл-> створити копію, щоб скопіювати файл собі на Google Диск і мати можливість редагування).
Зміст:
Якщо результат формули займає більше одного осередку
Спочатку про важливу особливість відображення результатів формул у Таблицях 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, напишемо її.
І вставимо цю формулу у функцію SPARKLINE, яка на основі отриманих даних малюватиме в осередку графік.
= sparkline (FILTER (C3: C7; B3: B7 = E2))
Так це виглядає у динаміці:
А от як ошатно може виглядати SPARKLINE з додатковими налаштуваннями, в реальній роботі, діаграма виводить результати діяльності за один день, зелені стовпці – позитивні значення, рожеві – негативні.
IMPORTRANGE
Для перенесення даних з одного файлу до іншого в Google Таблицях використовується функція IMPORTRANGE.У яких випадках вона може стати в нагоді?
- Вам потрібні актуальні дані із файлу ваших колег.
- Ви хочете обробляти дані з файлу, до якого ви маєте доступ «Тільки для перегляду».
- Ви бажаєте зібрати в одному місці таблиці з декількох документів, щоб обробляти або переглядати їх.
Синтаксис формули наступний:
IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; діапазон)
spreadsheet_key (ключ) - послідовність символів атрибуту «key=» (ключа) у посиланні таблицю (після «spreadsheets/…/»).
Приклад формули з ключем:
=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")
Замість ключа таблиці можна використовувати повне посилання на документ:
У файлі буде відображатися діапазон A1:CM500 з Листа1 з файлу, який знаходиться за відповідним посиланням.
Якщо у вихідному файлі може змінюватися кількість стовпців або рядків, введіть у другому аргументі функції відкритий діапазон (див. також підрозділ «Діапазони виду A2:A»), наприклад:
Аркуш1!A1:CM (якщо будуть додаватися рядки)
Аркуш1!A1:1000 (якщо будуть додаватися стовпці)
! Майте на увазі, що якщо ви завантажуєте відкритий діапазон (наприклад, A1:D), то ви не зможете вставити жодні дані вручну в стовпці A:D у файлі, де знаходиться формула IMPORTRANGE (тобто зрештою, куди завантажуються дані). Вони хіба що “зарезервуються” під весь відкритий діапазон - його розмірність невідома заздалегідь.
Імпорт форматування з вихідної таблиці
Як ми вже помітили, IMPORTRANGE завантажує лише дані, але не форматування вихідної таблиці. Як із цим бути? Заздалегідь «підготувати ґрунт», скопіювавши форматування з вихідного листа. Для цього зайдіть на вихідний лист і скопіюйте його до вашої книги:
Після натискання кнопки Копіювати в...виберіть книгу, до якої імпортуватимете дані. Зазвичай потрібна таблиця є на вкладці Недавні(якщо ви справді нещодавно працювали з нею).
Після копіювання аркуша виділіть усі дані (натиснувши на лівий верхній кут):
І натисніть Delete. Усі дані зникнуть, а форматування залишиться. Тепер можна ввести функцію IMPORTRANGE та отримати повну відповідність вихідного аркуша – як у частині даних, так і в частині формату:
IMPORTRANGE як аргумент іншої функції
IMPORTRANGE може бути аргументом іншої функції, якщо діапазон, який ви імпортуєте, підходить для цієї ролі.
Розглянемо простий приклад - середнє значення з продажу з діапазону, що у іншому документі.
Це вихідний документ. Нехай дані будуть додаватися і нам потрібно середнє з продажу 2016 року (тобто від осередку D2 і до упору вниз).
Спочатку імпортуємо цей діапазон:
А потім використовуємо це як аргумент функції СРЗНАЧ (AVERAGE):
Отримуємо результат, який оновлюватиметься при додаванні нових рядків у вихідному файлі в стовпці D.
IMAGE: додаємо зображення до осередків
Функція IMAGE дозволяє додавати до осередків Google Таблиць зображення.У функції наступний синтаксис:
IMAGE(URL, , , )
URL – єдиний обов'язковий аргумент. Це посилання зображення. Посилання можна вказати безпосередньо у формулі, взявши в лапки:
=IMAGE("http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg")
Останній варіант зручніший у більшості випадків. Так, якщо у вас є список книг і посилання на обкладинки, достатньо однієї формули, щоб відобразити їх усі:
Аргумент mode може набувати чотирьох значень (якщо його пропустити, за замовчуванням буде перше):
- зображення розтягується до розмірів осередку із збереженням співвідношення сторін;
- зображення розтягується без збереження співвідношення сторін, повністю заповнюючи
- зображення вставляється з оригінальним розміром;
- ви вказуєте розміри зображення в третьому та четвертому аргументах функції та . , , відповідно, потрібні лише за значення аргументу mode = 4. Вони задаються в пікселях.
Четвертий режим може бути зручним, якщо потрібно підбирати точний розмір зображення в пікселях, змінюючи параметри height (висота) і width (ширина). Картинка одразу оновлюватиметься.
Зверніть увагу, що при всіх режимах, крім другого, можуть залишатися незаповнені області в комірці, і їх можна залити кольором:
GOOGLETRANSLATE та DETECTLANGUAGE: перекладаємо текст у осередках
У Google Таблицях є цікава функція GOOGLETRANSLATE, що дозволяє перекладати текст прямо в осередках:
Синтаксис функції наступний:
GOOGLETRANSLATE (text,, )
text – це текст, який потрібно перекладати. Можна взяти текст у лапки і записати прямо у формулу, але зручніше послатися на комірку, в якій текст записаний.
- мова, з якої ми перекладаємо;
– мова, якою ми перекладаємо.
Другий і третій аргументи задаються двоцифровим кодом: es, fr, en, ru. Їх теж можна вказати в самій функції, але можна брати з комірки, а мову вихідного тексту взагалі можна автоматично визначати.
А як бути, якщо ми хочемо перекладати різними мовами? І при цьому не хочемо щоразу вказувати мову вихідника вручну?
формули
- Tutorial
У цій статті йдеться про кілька дуже корисних функцій Google Таблиць, яких немає в Excel (SORT, об'єднання масивів, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)
Дуже багато літер, але є розбір цікавих кейсів, всі приклади, до речі, можна розглянути ближче в Google Документі goo.gl/cOQAd9 (файл-> створити копію, щоб скопіювати файл собі на Google Диск і мати можливість редагування).
Зміст:
Якщо результат формули займає більше одного осередку
Спочатку про важливу особливість відображення результатів формул у Таблицях 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, напишемо її.
І вставимо цю формулу у функцію SPARKLINE, яка на основі отриманих даних малюватиме в осередку графік.
= sparkline (FILTER (C3: C7; B3: B7 = E2))
Так це виглядає у динаміці:
А от як ошатно може виглядати SPARKLINE з додатковими налаштуваннями, в реальній роботі, діаграма виводить результати діяльності за один день, зелені стовпці – позитивні значення, рожеві – негативні.
IMPORTRANGE
Для перенесення даних з одного файлу до іншого в Google Таблицях використовується функція IMPORTRANGE.У яких випадках вона може стати в нагоді?
- Вам потрібні актуальні дані із файлу ваших колег.
- Ви хочете обробляти дані з файлу, до якого ви маєте доступ «Тільки для перегляду».
- Ви бажаєте зібрати в одному місці таблиці з декількох документів, щоб обробляти або переглядати їх.
Синтаксис формули наступний:
IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; діапазон)
spreadsheet_key (ключ) - послідовність символів атрибуту «key=» (ключа) у посиланні таблицю (після «spreadsheets/…/»).
Приклад формули з ключем:
=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")
Замість ключа таблиці можна використовувати повне посилання на документ:
У файлі буде відображатися діапазон A1:CM500 з Листа1 з файлу, який знаходиться за відповідним посиланням.
Якщо у вихідному файлі може змінюватися кількість стовпців або рядків, введіть у другому аргументі функції відкритий діапазон (див. також підрозділ «Діапазони виду A2:A»), наприклад:
Аркуш1!A1:CM (якщо будуть додаватися рядки)
Аркуш1!A1:1000 (якщо будуть додаватися стовпці)
! Майте на увазі, що якщо ви завантажуєте відкритий діапазон (наприклад, A1:D), то ви не зможете вставити жодні дані вручну в стовпці A:D у файлі, де знаходиться формула IMPORTRANGE (тобто зрештою, куди завантажуються дані). Вони хіба що “зарезервуються” під весь відкритий діапазон - його розмірність невідома заздалегідь.
Імпорт форматування з вихідної таблиці
Як ми вже помітили, IMPORTRANGE завантажує лише дані, але не форматування вихідної таблиці. Як із цим бути? Заздалегідь «підготувати ґрунт», скопіювавши форматування з вихідного листа. Для цього зайдіть на вихідний лист і скопіюйте його до вашої книги:
Після натискання кнопки Копіювати в...виберіть книгу, до якої імпортуватимете дані. Зазвичай потрібна таблиця є на вкладці Недавні(якщо ви справді нещодавно працювали з нею).
Після копіювання аркуша виділіть усі дані (натиснувши на лівий верхній кут):
І натисніть Delete. Усі дані зникнуть, а форматування залишиться. Тепер можна ввести функцію IMPORTRANGE та отримати повну відповідність вихідного аркуша – як у частині даних, так і в частині формату:
IMPORTRANGE як аргумент іншої функції
IMPORTRANGE може бути аргументом іншої функції, якщо діапазон, який ви імпортуєте, підходить для цієї ролі.
Розглянемо простий приклад - середнє значення з продажу з діапазону, що у іншому документі.
Це вихідний документ. Нехай дані будуть додаватися і нам потрібно середнє з продажу 2016 року (тобто від осередку D2 і до упору вниз).
Спочатку імпортуємо цей діапазон:
А потім використовуємо це як аргумент функції СРЗНАЧ (AVERAGE):
Отримуємо результат, який оновлюватиметься при додаванні нових рядків у вихідному файлі в стовпці D.
IMAGE: додаємо зображення до осередків
Функція IMAGE дозволяє додавати до осередків Google Таблиць зображення.У функції наступний синтаксис:
IMAGE(URL, , , )
URL – єдиний обов'язковий аргумент. Це посилання зображення. Посилання можна вказати безпосередньо у формулі, взявши в лапки:
=IMAGE("http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg")
Останній варіант зручніший у більшості випадків. Так, якщо у вас є список книг і посилання на обкладинки, достатньо однієї формули, щоб відобразити їх усі:
Аргумент mode може набувати чотирьох значень (якщо його пропустити, за замовчуванням буде перше):
- зображення розтягується до розмірів осередку із збереженням співвідношення сторін;
- зображення розтягується без збереження співвідношення сторін, повністю заповнюючи
- зображення вставляється з оригінальним розміром;
- ви вказуєте розміри зображення в третьому та четвертому аргументах функції та . , , відповідно, потрібні лише за значення аргументу mode = 4. Вони задаються в пікселях.
Четвертий режим може бути зручним, якщо потрібно підбирати точний розмір зображення в пікселях, змінюючи параметри height (висота) і width (ширина). Картинка одразу оновлюватиметься.
Зверніть увагу, що при всіх режимах, крім другого, можуть залишатися незаповнені області в комірці, і їх можна залити кольором:
GOOGLETRANSLATE та DETECTLANGUAGE: перекладаємо текст у осередках
У Google Таблицях є цікава функція GOOGLETRANSLATE, що дозволяє перекладати текст прямо в осередках:
Синтаксис функції наступний:
GOOGLETRANSLATE (text,, )
text – це текст, який потрібно перекладати. Можна взяти текст у лапки і записати прямо у формулу, але зручніше послатися на комірку, в якій текст записаний.
- мова, з якої ми перекладаємо;
– мова, якою ми перекладаємо.
Другий і третій аргументи задаються двоцифровим кодом: es, fr, en, ru. Їх теж можна вказати в самій функції, але можна брати з комірки, а мову вихідного тексту взагалі можна автоматично визначати.
А як бути, якщо ми хочемо перекладати різними мовами? І при цьому не хочемо щоразу вказувати мову вихідника вручну?
формули Додати теги
Здрастуйте, шановні читачі! Практично у всіх нас, під час роботи з електронними документами, виникає потреба, щоб їх побачив хтось ще: відредагував, додрукував чи просто ознайомився. Можна переслати потрібний файл поштою, але це займе час (користувач повинен отримати, внести редагування, відправити вам), можна віддати під час особистої зустрічі, яка не завжди може відбутися, а можна скористатися сервісом від компанії Google – Google Таблиці.
Ось з ними ми і почнемо знайомитись у цій статті. На вигляд і функцій вони схожі на продукт компанії Microsoft - Excel, з яким звикли працювати багато хто, і якщо ви серед них, то розібратися з тим, як працювати в Google таблицях не складе труднощів. З їхньою допомогою можна створювати великі таблиці, використовуючи формули в розрахунках, будувати графіки, на отриманих з таблиць даних, форматувати осередки, виділяючи їх потрібним кольором, залежно від даних та багато іншого.
Але основна перевага таких таблиць така: ви можете дати доступ до будь-якої створеної іншим користувачам і вже разом працювати над одним проектом. При цьому є можливість спілкуватися з усіма, хто теж працює з нею зараз, наприклад, додаючи коментарі до осередків.
Всі нюанси роботи я поступово описуватиму у всіх наступних статтях, а зараз почнемо з найпростішого і розберемося, як створити Google таблицю з нуля, і що для цього потрібно.
Для того щоб зробити Google таблицю з нуля, вам необхідно виконати вхід до свого Google облікового запису. Якщо не знаєте, що це таке, поясню. Маючи профіль у цьому обліковому записі, ви можете користуватися всіма сервісами від компанії Google: пошта Gmail, Play Market на телефонах з ОС Android, Google+, Google Диск та інше. Відповідно, якщо ви десь із перерахованого зареєстровані – то необхідні дані для доступу до облікового запису у вас є. Знайдіть, де вони записані: логін повинен закінчуватися на @ gmail.com, а пароль, що ви самі вигадували.
Для входу в обліковий запис необхідно відкрити стартову сторінку Google. Для цього відкрийте браузер, яким користуєтеся, у верхній частині, де відкриті вкладки, натисніть на плюсик, щоб створити нову, і в рядок пошуку пишіть «гугл пошукова система». Коли почнуть з'являтися підказки до того, що ви набираєте, натисніть на ту, де буде прописана адреса: google.com.
Якщо у вас там показана аватарка або буква в кольоровому гуртку, то ви вже увійшли в аккаунт. Натисніть на мініатюру, подивіться з-під якої пошти виконано вхід і можете пропустити наступний крок, а можна змінити користувача та зайти в інший профіль.
Введіть спочатку логін (електронну адресу), а в наступному вікні пароль і натисніть "Далі".
Тепер праворуч відобразиться перша літера вашого імені у кольоровому кружку. Ліворуч від неї буде кнопка у вигляді 9 квадратів, тисніть по ній, перегортайте список вниз і вибирайте «Інші сервіси Google».
Листуйте сторінку, що відкрилася вниз, поки не побачите пункт з потрібною нам назвою. Під ним натисніть кнопку «Початок роботи».
Для роботи з таблицями можна встановити програму на пристрій з операційною системою iOS, Android або комп'ютер. Ми скористаємося четвертим варіантом – робота з ними через браузер. Тому, щоб увійти онлайн на потрібну нам сторінку, натисніть «Відкрити сайт».
Після цього у браузері відкриється стартова сторінка Google Таблиць. Вгорі будуть варіанти шаблонів, нижче у вигляді списку показані документи, які ви створювали самі, або доступ до яких ви отримали. Щоб додати таблицю, із шаблонів потрібно вибрати перший варіант: «Чистий».
Потім перейменуйте документ: натисніть ліворуч угорі на полі з написом «Нова таблиця».
Напишіть відповідну назву і клацніть або по будь-якій вільній області на аркуші, або по якомусь осередку. Щоб зберегти його.
Тепер, якщо повернутися на стартову сторінку Google Таблиць, ви побачите щойно створений документ.
Крім того, всі створені таблиці автоматично зберігаються на вашому Гугл Диску. Натисніть зображення папки поруч із назвою. Тут буде показано, в якій папці вони збереглися. Щоб вибрати іншу папку для поточного документа, виділіть її і натисніть кнопку «Перемістити». Якщо потрібної папки ще немає, її можна створити прямо з цього віконця, клікнувши по відповідному зображенню внизу справа з плюсиком.
Отак усе просто. Створюйте необхідні Google таблиці онлайн, і тоді у вас буде можливість відкрити їх на будь-якому пристрої (телефоні або планшеті), де встановлено необхідну програму, і ви зможете працювати з ними разом з іншими користувачами, надіславши їм посилання на документ, але про це ми поговоримо у наступних статтях.
У вас так багато даних, що від багатьох рядків і колонок з даними розбігаються очі? Ви тонете у морі цифр і не можете зрозуміти, що відбувається? Припустимо, ви працюєте в компанії, що займається продажем шоколаду в декількох регіонах і працює з багатьма покупцями.
Ваш бос поставив вам завдання – вам необхідно на основі даних про продаж визначити кращого покупця, найпопулярніший продукт і найбільш вигідний регіон збуту.
Не поспішайте впадати в паніку та згадувати, як правильно використовувати такі традиційні функції як РАХУНКИ, СУМІСЛІ та подібні до них. Розслабтеся, зробіть глибокий вдих. Це завдання дуже просто вирішується за допомогою зведених таблиць Google. Зведена таблиця допоможе представити ваші дані у зручному та презентабельному вигляді.
Головна зручність зведених таблиць полягає в тому, що поля в них можна інтерактивно переміщати, а дані в них можна фільтрувати, групувати, сортувати, розраховувати підсумки та середні значення. Ви можете змінювати рядки та стовпці місцями, змінювати рівні деталізації уявлення. Це дозволяє не лише змінювати зовнішній вигляд таблиці, а й поглянути на ваші дані під зовсім іншим кутом.
Важливо також і те, що при цьому ваші вихідні дані не змінюються - щоб ви не робили з вашою зведеною таблицею. Ви просто вибираєте такий спосіб їхнього відображення, який дозволить вам побачити нові закономірності та зв'язки. Ваші дані у зведеній таблиці будуть поділені на групи, а величезний обсяг інформації буде представлений у зрозумілій та доступній для аналізу формі.
Короткий посібник зі зведених таблиць в Google таблицях.
Ось як, наприклад, виглядають дані про ваші продажі, що зберігаються в Google таблиці.
Відкриємо таблицю, в якій містяться наші вихідні дані з продажу. Важливо, щоб дані, які ви будете використовувати, були розташовані на стовпчиках. Кожен стовпець є певним набором даних. І кожен стовпець повинен мати заголовок. Крім того, у ваших вихідних даних не повинно бути об'єднаних осередків.
Виділіть мишкою всі ваші дані, які ви використовуватимете для створення зведеної таблиці.
У нашому прикладі ви можете виділити стовпці A: G.
Потім у меню вибираємо Дані -> Зведена таблиця. Відкривається новий аркуш, на якому ми будемо створювати та редагувати зведену Google таблицю. Вам необхідно налаштувати зміст та зовнішній вигляд вашої таблиці.
Перейдіть до нового створеного аркуша зі зведеною таблицею. Поки немає даних, але справа з'явилося меню «Редактор звітів». За допомогою цього меню можна додати поля в «Рядки», «Стовпці», «Значення» та «Фільтр».
Тепер просто натисніть на «Додати поле» і виберіть дані, які необхідні для рядків і стовпців зведеної таблиці, а також для її значень.
Наприклад, розрахуємо продаж різних видів шоколаду в різних містах.
У полі «Значення» можна налаштувати тип результату, що розраховується. Це може бути загальна сума, мінімальна чи максимальна сума, середня вартість, кількість значень та інші варіанти. Ми використовуємо для прикладу загальну суму.
Зведена таблиця дозволяє показати і складніші угруповання даних. Для цього до поля «Рядки» або «Стовпці» потрібно додати кілька полів. Ось, наприклад, як можна розрахувати продаж і по містах, і по регіонах одночасно.
Важливо: на відміну від Excel, зведені таблиці Google оновлюються автоматично. Але ми радимо регулярно перевіряти, чи посилається зведена таблиця весь діапазон вихідних даних.
Як створити зведену таблицю Google із кількох діапазонів даних.
Часто трапляється, що необхідні аналізу дані перебувають у різних таблицях. А зведену таблицю можна побудувати лише з одному діапазону даних. Дані з кількох таблиць при побудові зведеної таблиці використовувати не можна. Який вихід?
Для того, щоб використовувати кілька аркушів в одній зведеній таблиці, спочатку їх потрібно об'єднати в одну загальну таблицю.
Для такого поєднання аркушів існують різні рішення. Але, враховуючи простоту та доступність, ми хотіли б запропонувати вам використовувати наступне доповнення до Google таблиць, яке допоможе вам.
Отже, наша зведена таблиця готова.
Використовуємо зведену таблицю для аналізу даних.
Як бачите, навіть на найпростішому рівні зведена таблиця дозволяє відповісти на досить важливі питання.
Однак, повернемося до питань, які цікавлять вашого боса: Хто мій найкращий покупець?
Діючи старим та перевіреним способом, змінюємо рядки та стовпці нашої таблиці та отримуємо шуканий результат. Не забудьте відсортувати споживачів з використанням поля «Сума».
Аналогічним чином дамо відповіді на інші цікаві питання. Наприклад, який товар найкраще продається?
Де мої товари найкраще продаються?
Де який товар краще продається?
Отже, протягом 5 хвилин ви отримали за допомогою Google таблиць відповіді на питання, що вас цікавлять. Ваш бос задоволений!
Зверніть увагу, що загальний обсяг продажів однаковий у всіх наших зведених таблицях. Це тому, що кожна з них просто представляє ті самі дані різними способами.
Як створити діаграму у зведеній Google таблиці.
Ще наочнішими стають наші дані при використанні графіків та діаграм. Додати діаграму до зведеної таблиці можна двома способами.
Встановіть курсор на будь-яку комірку вашої зведеної таблиці, щоб показати, що саме ці дані ви бажаєте використати. У головному меню натисніть Вставка -> Діаграма. Далі можна вибрати тип діаграми та змінити її оформлення. Потім натисніть Вставити, і діаграма буде розміщена на аркуш, де знаходиться зведена таблиця.
Ось що можна отримати за замовчуванням з даних нашої останньої таблиці, де ми аналізували, який товар, де краще продається.
Є й другий спосіб створити діаграму.
У таблицях Google є додатковий сервіс, якого раніше не було в жодній із програм роботи з таблицями. У нижньому правому кутку є кнопка «Аналіз даних». Натискаючи на неї, ви можете отримати різні підказки та пропозиції щодо покращення представлення ваших даних, їх аналізу, у тому числі й створення діаграм.
Натисніть значок у правому нижньому кутку, і ви зможете не тільки вибрати найкращу діаграму з кількох запропонованих, але й змінити оформлення вашої зведеної таблиці.
Повернувшись до тих питань, які ми раніше ставили, отримуємо діаграму, яка показує не тільки обсяг покупок кожним з покупців, але й дає уявлення про те, який саме шоколад віддає перевагу покупцю.
Зверніть увагу, що система аналізує ваші дані і навіть наголошує на окремих закономірностях, на які ви самі, можливо, не звернули б і уваги.
Тепер залишилося тільки відредагувати нашу діаграму, надати їй найбільш наочний і популярний вигляд, оформити підписи осей та даних. Як це робити – про це ми поговоримо в окремому матеріалі трохи пізніше.
Як опублікувати зведену Google таблицю чи діаграму в Інтернеті
Вашу діаграму можна опублікувати в інтернеті. Для цього в головному меню клацніть Файл -> Опублікувати в Інтернеті. Потім виберіть об'єкти, які ви хочете опублікувати. У нашому випадку ми можемо опублікувати весь файл, або окремий лист, або тільки діаграму. Якщо ми виберемо аркуш нашого файлу Google таблиць, ми можемо представити його як веб-сторінку, або як статичний документ у форматі PDF. Якщо ми хочемо опублікувати лише діаграму, то варіанти також два. Або це буде інтерактивна діаграма, яка буде змінюватися разом зі зміною вихідних даних, або можна просто обмежитися картинкою, що показує знімок діаграми в певний момент часу.
Звичайно, будь-який варіант публікації передбачає лише режим перегляду, ні про яку спільну роботу над діаграмою або внесення до неї змін вашими колегами не йдеться. Для цього використовується спільний доступ до файлу, про який ми докладно розповідали.
За допомогою цього ж меню можна скасувати публікацію.
Сподіваюся, цей невеликий огляд можливостей зведених таблиць Google надихне вас на їхнє використання з вашими власними даними. Повірте, ви відразу зрозумієте, як це легко та зручно. Зведені таблиці допоможуть вам заощадити сили та час. Важливим для вас буде те, що створений сьогодні звіт можна легко оновити і використовувати в майбутньому з новими даними.