Меню

Использование IMPORTRANGE внутри FILTER

Использование IMPORTRANGE внутри FILTER

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

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

Я пробовал варианты этого и получил #N/A

FILTER имеет несоответствующие размеры диапазона. Ожидается, что количество строк: 122. число столбцов: 1. Фактическое число строк: 1, число столбцов: 1.

Я понятия не имею, как сделать эту работу.

1 ответ

Я использовал скрипт, который позволяет мне подставлять имена столбцов для формата Col# в QUERY листа в той же книге Google, но когда я вызываю QUERY из другой таблицы Google Sheets с IMPORTRANGE, я получаю эту ошибку: Диапазон не найден (строка 11, файл SQLfunction) Мой вызывающий сотовый.

У меня есть лист, который вытягивает числа из нескольких разных листов, чтобы объединить числа. Каждую неделю в исходные файлы добавляется новый лист с одинаковым именем. Я хотел бы обновить объединенный лист, изменив одну ячейку вместо многих. Когда вкладка находится в том же листе Google, это.

0

Похожие вопросы:

В отдельной электронной таблице у меня есть названия проектов в столбце 2 (B2:B). В колонке 10 (J2:J) той же таблицы у меня есть время (продолжительность), потраченное на этот проект. Эти проекты.

Я пытаюсь импортировать данные из нескольких электронных таблиц, используя IMPORTRANGE(), однако наличие каждого встроенного ключа spreadsheet_key сделало бы его больным для чтения, болью для.

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

Я использовал скрипт, который позволяет мне подставлять имена столбцов для формата Col# в QUERY листа в той же книге Google, но когда я вызываю QUERY из другой таблицы Google Sheets с IMPORTRANGE, я.

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

У меня есть один лист с большим количеством рабочих книг внутри, и каждая рабочая книга включает в себя множество функций IMPORTRANGE . Пример такой.

У меня есть URL в одной ячейке, которая является критерием для IMPORTRANGE. например: =IMPORTRANGE(B2,sheet1!$A$1) Я нашел формулу для сбора его в качестве критерия, но не в версии массива. Образец.

Используя Google Sheets, я хочу в том же документе импортировать данные с одного листа на другой, используя IMPORTRANGE с условиями. Я пытался безуспешно: =IF(IMPORTRANGE(https:URL,Inc.

Согласно разделу справки Google , я должен иметь возможность ссылаться на столбец в a QUERY, используя идентификаторы столбцов . одна или две символьные буквы столбца (A, B, C. ) — или в данном.

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

Источник

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

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

image

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

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

Оглавление:

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

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

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

image

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

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

image

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

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

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

image

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

image

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

image

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

image

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

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

FILTER

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

Одно условие

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

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

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

image

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

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

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

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

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

image

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

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

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

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

image

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

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

image

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

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

IMPORTRANGE

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

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

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

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

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

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

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

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

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

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

Читайте также:  Калорийность фруктовых и овощных соков

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

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

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

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

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

image

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

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

image

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

image

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

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

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

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

image

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

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

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

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

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

image

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

image

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

image

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

image

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

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

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

image

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

image

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

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

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

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

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

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

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

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

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

Источник



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

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

2017-06-21 в 17:00 , admin , рубрики: big data, data mining, Google API, Google Docs, Google Sheets, ms excel, Алгоритмы, формулы

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

image

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

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

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

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

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

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

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

image

image

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

image

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

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

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

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

image

image

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

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

FILTER

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

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

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

image

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

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

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

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

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

image

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

Читайте также:  Важность выбора правильных габаритов аккумуляторных батарей

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

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

image

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

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

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

image

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

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

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

IMPORTRANGE

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

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

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

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

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

IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; диапазон)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IMPORTRANGE как аргумент другой функции
IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)
image

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

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

image

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

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

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

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

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

image

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

image

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А как быть, если мы хотим переводить на разные языки? И при этом не хотим каждый раз указывать язык исходника вручную?
Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:

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

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

Источник

LiveInternetLiveInternet

Поиск по дневнику

Подписка по e-mail

Постоянные читатели

Статистика

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

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

image

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

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

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

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

image

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

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

Читайте также:  HTML тег изменения цвета заднего фона текста атрибут style background color

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

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

image

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

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

FILTER

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

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

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

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

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

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

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

image

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

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

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

image

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

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

image

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

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

IMPORTRANGE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IMPORTRANGE как аргумент другой функции
IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз)
image

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

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

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

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

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

image

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

image

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

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

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

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

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

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

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

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

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

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

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

А как быть, если мы хотим переводить на разные языки? И при этом не хотим каждый раз указывать язык исходника вручную?
Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить:

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

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

Источник