Importrange excel как сделать

Обновлено: 22.01.2025

In Google Sheets, there is a wonderful function called IMPORTRANGE. It allows you to import and link a specific range of cells from one spreadsheet to another. Excel is older and provides more intense functional stuffing, so it should also have the IMPORTRANGE function available, right? Read on to discover the answer to this and many other Excel IMPORTRANGE related questions.

Is there IMPORTRANGE in Excel?

There are many functions in Excel, such as VLOOKUP Excel or SUMIF Excel. However, there is no IMPORTRANGE function.

At the same time, Excel provides a similar to IMPORTRANGE functionality for linking a data range from a separate spreadsheet. This can be done either by copying and pasting a data range or by using a formula bar.

What is the Excel version of IMPORTRANGE?

In Excel Online, the IMPORTRANGE functionality is called WorkBook links.

In Excel desktop, it has no explicit name, but is implemented via the Paste Link option.

The implementation of an Excel IMPORTRANGE equiv depends on the Excel app you use (365/Online or desktop app). Let’s discuss each way separately.

How to use IMPORTRANGE equivalent for Excel desktop

Open both a source and a destination Excel spreadsheet. Select a range of cells in the source spreadsheet and copy them: you can use either Ctrl+C or right click => Copy.

Go to your destination spreadsheet, select either one cell to import the entire range or a range of cells to only populate the selected cells. Then right-click and select Paste Link.

You can expand the imported range by simply dragging the cross as follows:

Now if you change some values of the imported range in the source file, the values in the destination spreadsheet will be updated as well.

This method works well if your source and destination spreadsheets are open. When you need to import a range from an Excel workbook that is not open, use the formula bar method.

Excel IMPORTRANGE formula using the formula bar in desktop app

First of all, an important note: If your source workbook only has one worksheet with the same name as the workbook, the imported range reference will look as follows in the formula bar:

However, if the names of the source workbook and worksheets differ, or the workbook has multiple worksheets, the imported range reference will look as follows in the formula bar:

When you close the source workbook, the imported range reference transforms in the following way:

So, it’s attached with a path to the file on your device. In our case, the path is to the OneDrive storage folder. However, you can also encounter the more regular paths like this one:

This is the boilerplate you can use to import data from any Excel file on your device:

Note: you can use either slash or backslash depending on which type is used in the path to your file.

For example, we need to import data from Sheet1 of a source file named Source2.xlsx stored in the OneDrive folder. In the example above, our reference to this folder looked as follows:

But it’s okay if you take a regular path, which you can find in the file explorer like this:

Insert this path string to the Excel IMPORTRANGE equivalent formula and add other variables: , , and . Here is what we’ve got:

Select a cell, insert this formula to the formula bar and press Ctrl+Shift+Enter.

Now you can expand the range by dragging the cell vertically or horizontally.

If you want to import an exact range at once, specify the range instead of the first cell in your formula. Then select the range in the destination sheet, insert the formula to the formula bar, and press Ctrl+Shift+Enter. Here is an example:

Workbook links – Excel IMPORTRANGE from different worksheet online

Now you have understanding of how IMPORTRANGE functionality on Microsoft Excel desktop works. However, Excel Online, as well as Excel 365, has some differences in use. Let’s explore how Workbook Links works.

Open the files you already know, Source.xlsx and Destination.xlsx, in Excel Online. Select and copy a range in the source file, go to the destination file, select a cell, right-click and choose Link as a paste option.

The cells will be populated with the values from the source spreadsheet.

The main difference here is that the imported data won’t be refreshed automatically until you enable this. To do this, go to the Data menu => Workbook Links.

On the pop-up panel on the right, click the three dots and check the Refresh automatically checkbox for the linked workbook.

Now the imported range will be refreshed automatically every 5 minutes to display any change in the source range.

Excel Workbook Links does not work

We tried to import a range from a file stored in another folder, and here is the result:

We referenced a thread on the MS Office forum regarding the same issue, but we’ve still had no luck in resolving it. The only method that worked was to put the source and destination workbooks in the same folder.

With this in mind, it’s good to have a failsafe alternative to Workbook Links. And there is one – Coupler.io.

IMPORTRANGE and Workbook Links alternative for Excel

Coupler.io is a tool for importing data from apps and sources to Excel, Google Sheets, or BigQuery. For example, you can export raw data from Pipedrive or HubSpot and load it to your workbook stored on OneDrive. The main feature is that you can schedule automatic data refresh for your data.

In our case, we need to synchronize two Excel spreadsheets, and this is easily doable. Check out the available Excel integrations.

Set up source
Set up destination
Set up schedule

Set up source

Note: Selecting multiple sheets can be used if you want to concatenate data from multiple sheets into one.

Set up destination

The flow is mostly the same:

Select Microsoft Excel as a destination app
Connect your Microsoft account or select an existing one
Select a destination workbook from your OneDrive folder and specify a worksheet.
Specify the first cell to load data to.
Select the import mode:
- Replace – to replace all data
- Append – to append newly imported records under the previously imported ones
Click “Save and Run” to import a range to your destination spreadsheet. Voila!

Wait, we’ve talked about three steps, right?

Set up schedule

Coupler.io allows you to automate import of data on a custom frequency – for example, every hour or every Tuesday. To do this, toggle on the Automatic data refresh and configure the schedule for your data import.

IMPORTRANGE from Excel to Google Sheets and vice versa

The real IMPORTRANGE function only connects Google Sheets documents. IMPORTRANGE in Excel, Workbook Links, only connects Excel files. With Coupler.io, you can mix those functionalities and synchronize data between an Excel workbook and a Google spreadsheet.

For example, to import a range from an Excel spreadsheet to Google Sheets, you need to choose Excel as a source app and select the file to get data from. As a destination app, choose Google Sheets, and select the file to load data to. Here is what it should look like:

Excel IMPORTRANGE FAQs

Two most popular spreadsheet apps, Google Sheets and Excel, have a large influence on each other’s progress. IMPORTRANGE is a case in point, because it’s a Google Sheets function that is not available in Excel. Nevertheless, many users keep using the term Excel IMPORTRANGE to name Workbook Links…or they just dream about a day when the Microsoft team releases this function. 🙂 Anyway, we collected a few questions associated with this and hope you’ll find it useful.

Excel IMPORTRANGE function for a whole column

Everything works as usual. To import an entire column from one workbook to another, just select the column (click the column index letter), copy it, and paste as link into the destination workbook.

How to use Excel IMPORTRANGE to get data with formatting?

IMPORTRANGE in Google Sheets, Workbook Links in Excel, as well as Coupler.io, allow you to only import raw data without formatting.

Can I use Excel IMPORTRANGE with CONCATENATE?

In Google Sheets, you can nest IMPORTRANGE with CONCATENATE, QUERY, and many other functions. For example, check out our IMPORTRANGE+QUERY tutorial.

However, in Excel, there is no IMPORTRANGE function, so it’s impossible to do.

Excel IMPORTRANGE to import cells with text only

Unfortunately, Workbook Links does not let you set conditions for the ranges you import. However, you can use the Excel Power Query to do the job. For example, our data set that we need to import contains empty rows:

We can set a condition to ignore them for our import.

IMPORTRANGE, Workbook Links, or Coupler.io

Who’s going to win this race? Only Coupler.io is designed for both Excel and Google Sheets users and provides them with a scheduled data refresh, support for multiple sources and destinations, and many other cool features.

IMPORTRANGE is only available for Google Sheets users. It’s a good function, although it is known for its errors, which we’ve covered in Why IMPORTRANGE Is Not Working: Errors and Fixes.

Workbook Links, looks a bit plain and straightforward compared to its competitors. The keyword “excel importrange” is highly requested in Google search results, which means that Excel users need this function instead of WL. Ugly truth.

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

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

Что делает функция IMPORTHTML в Google Таблицах?

Все таблицы в файле HTML заключены в теги <table> </table>, а все списки заключены в теги <ul> </ul> (для неупорядоченных списков) или теги <ol> </ol> для упорядоченных списков. Это позволяет функции IMPORTHTML очень легко обнаруживать таблицы и списки на веб-странице.

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

Синтаксис функции IMPORTHTML в Google Таблицах

Синтаксис функции IMPORTHTML в Google Таблицах следующий:

Как использовать функцию IMPORTHTML для импорта списка с веб-страницы

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

Чтобы получить этот список, нам сначала нужно знать индекс этого конкретного списка на всей веб-странице. Если вы посетите веб-сайт по этому URL-адресу, вы заметите, что на странице есть несколько разных списков. Итак, как мы узнаем, каков индекс этого конкретного списка?

Как получить индекс списка с веб-страницы

Чтобы получить индекс списка с веб-страницы, нам нужно написать небольшой фрагмент кода javascript в консоли браузера. Не волнуйтесь, вам не нужно много делать, просто скопируйте приведенный ниже код в консоль:

Вот шаги, чтобы получить индекс нашего необходимого списка с данной веб-страницы:
- Откройте инструменты разработчика вашего браузера. Если вы используете ПК с Windows, нажмите клавишу F12 на клавиатуре. Это работает в Firefox, Google Chrome или Microsoft Edge. Если вы используете Mac, нажмите Cmd + Opt + J, чтобы открыть Google Chrome, и Cmd + Opt + C, чтобы открыть Safari (сначала обязательно включите «Меню разработки»).
- Когда откроется ваш инструмент разработчика, перейдите на вкладку «Консоль».
- Вставьте или введите указанный выше код в область сценария консоли.
- В области вывода вы должны увидеть ряд индексов, которые выглядят следующим образом:
- Если вы наведете указатель мыши на каждый индекс, вы увидите список на своей веб-странице, который соответствует этому индексу выделенным. Это поможет вам понять, какой индекс соответствует нужному списку.
Как видно из приведенного выше снимка экрана, список, который мы хотим извлечь, находится в индексе 18 веб-страницы.

Использование функции IMPORTHTML для импорта списка с веб-страницы

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

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

Как использовать функцию IMPORTHTML для импорта таблицы с веб-страницы

Помимо списков, вы также можете импортировать таблицы с веб-страниц с помощью функции IMPORTHTML.

Вот как таблица выглядит на исходной веб-странице:

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

Как получить указатель таблицы с веб-страницы

Тот же код javascript можно использовать для поиска индекса нужной таблицы, но с небольшими изменениями. Вместо использования querySelectorAll для извлечения всех элементов «ul» и «ol» мы используем функцию getElementsByTagName для извлечения всех элементов «table» с веб-страницы. Вот код, который вы можете скопировать и вставить в консоль:

Вот как вывод вышеприведенного кода выглядит в консоли:

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

Использование функции IMPORTHTML для импорта таблицы с веб-страницы

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

Вот как таблица выглядит на вашем листе Google Таблиц:

Объединение функции IMPORTHTML с функциями QUERY для извлечения определенных частей таблицы

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

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

Извлечение определенных столбцов таблицы с веб-страницы

Здесь функция IMPORTHTML сначала получит вашу таблицу номер 3 с веб-страницы coinmarketcap, а затем функция QUERY возьмет эту таблицу и извлечет из нее только второй и пятый столбцы.

Извлечение определенных строк таблицы с веб-страницы

Давайте теперь еще больше уточнить наш поиск и сузить наше окно. Допустим, мы хотим видеть только те строки, в которых значение Price превышает 1000 долларов.

Мы можем указать это условие в функции QUERY, добавив предложение WHERE следующим образом:

Поскольку цена находится в столбце 5, мы указали, что хотим выбрать только те строки столбцов Col2 и Col5, где Col5> 1000.

Автоматическое обновление функции IMPORTHTML через заданный интервал

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

Однако, если вы хотите ускорить или замедлить интервал обновления, вы можете сделать это, добавив дополнительную строку обновления, как показано ниже:

Обратите внимание, что мы добавили условие «? Refresh-1» в конец строки url.

Приведенная выше формула заставляет функцию IMPORTHTML обновляться каждую минуту.

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

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

Оглавление:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FILTER

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

Одно условие

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IMPORTRANGE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оглавление:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FILTER

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

Одно условие

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IMPORTRANGE

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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