Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

class="p1">Для установки связи с ячейкой (Cell link) необходимо щелкнуть правой кнопкой по объекту и выбрать «Формат объекта» (Format Control).

Теперь при изменении флажка будет меняться и значение в ячейке J1.

Остается создать аналогичные флажки для каждого ряда данных (региона) в нашей таблице.

Теперь у нас есть четыре ячейки, в которых отражается выбор регионов.

Остается создать дополнительную таблицу, в которой будут не все данные (как в A1:E13), а только те, которые мы выбрали. Для этого можно, как мы делали в одном из предыдущих примеров, воспользоваться функциями ЕСЛИ / IF и НД / NA.

Теперь можно построить диаграмму по созданному диапазону A15:E27, в котором отображаются только выбранные данные.

При снятии и установке флажков на диаграмме теперь будут отображаться соответствующие ряды данных (регионы).

Значения ИСТИНА / ЛОЖЬ можно скрыть с помощью форматов или белого шрифта: они не нужны и не являются частью интерфейса, для пользователя здесь предназначены флажки, а значения в ячейках нужны для формул.

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

Power Map (3D-карты)

Файл с примером: Строим модель данных.xlsx

Power Map — это надстройка, которую можно включить вместе с Power Pivot, если они доступны в вашей версии Excel. С ее помощью можно визуализировать географические данные на объемной карте Земли. Данными для такой визуализации выступает все, что загружено (и связано) в модели данных книги Excel (Power Pivot).

3D Maps — отдельное окно, в котором есть аналог ленты инструментов Excel, список обзоров (слева) и настройки диаграммы — тип и данные, которые используются (слева).

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

Этот столбец нужно перенести в «Расположение» (Location).

Например, город.

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

Если вы хотите смотреть на структуру (например, на продажи в разных типах магазинов: в нашем примере есть просто книжные магазины и есть магазины с кафе), добавьте соответствующее поле в область «Категория» (Category).

Обратите внимание, что ниже есть категория «Время» (Time). Если у вас есть поле с датами и/или временем, их можно добавить туда — и появится возможность воспроизводить изменение данных в динамике, наблюдая, как меняется отображаемый показатель.

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

В книгах с обзорами (картами) будет появляться текстовый объект с уведомлением о наличии обзоров 3D Maps.

Функция ИЗОБРАЖЕНИЕ / IMAGE

Файл с примером: ИЗОБРАЖЕНИЕ.xlsx

Функция IMAGE (в Excel на русском — ИЗОБРАЖЕНИЕ) позволяет вставлять в ячейки Google Таблиц и Excel изображения. Первый и единственный обязательный аргумент функции — ссылка на изображение.

В Excel функция появилась только в 2022 году в рамках пакета Microsoft 365, то есть в Excel 2021 ее нет.

Чтобы получить ссылку на изображение, находящееся в Сети, нужно в браузере щелкнуть правой кнопкой мыши на нем и нажать «Получить ссылку на изображение».

В следующем примере функция ИЗОБРАЖЕНИЕ отображает обложку книги, ссылка на которую находится в ячейке A1 (а функция ссылается на нее в своем единственном обязательном аргументе).

Изображение можно выравнивать как текст, а также добавлять заливку.

В Excel у функции есть второй аргумент — «Замещающий текст»: можно заменить изображение текстовым значением, если будут проблемы с соединением или загрузкой изображения. Далее — аргументы, определяющие режим отображения (они есть и в функции IMAGE в Google Таблицах, о них ниже).

В Google Таблицах функция называется IMAGE при любом языке формул.

И там, в Google Таблицах, также можно получить все ссылки на изображения на сайте с помощью функции IMPORTXML и запроса XPath «//img/@src». А это значит, что можно сформировать ссылку на поиск изображения по тексту с помощью формулы.

В ней мы подставляем название товара или что угодно еще, что нужно найти, в поисковый запрос, потом получаем список всех изображений на этой странице с поисковой выдачей (IMPORTXML), затем берем одно из значений из этого списка (можно второе — первое обычно пустое или там может быть логотип, а далее могут быть менее релевантные варианты) с помощью функции ИНДЕКС / INDEX и превращаем ссылку в изображение (функцией IMAGE).

Функция КОДИР.URL / ENCODEURL кодирует текст, чтобы он был пригоден для веб-запроса:

=IMAGE(INDEX(IMPORTXML("https://www.google.com/search?tbm=isch&q="&ENCODEURL(текст для поиска); "//img/@src");2))

Для Яндекс. Картинок все будет аналогично, только ссылка на поиск другая:

=IMAGE(INDEX(IMPORTXML

("https://yandex.ru/images/search?text="&ENCODEURL(текст для поиска);"//img/@src");2))

Google Таблица с примером: Вытаскиваем изображения из Яндекс. Картинок и Google Images. https://mif.to/tB7mF

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

Функция IMAGE никак не может повлиять на ширину и высоту ячеек. Изображения лишь вписываются в текущие размеры ячейки с сохранением исходных пропорций.

У IMAGE также есть второй (в Excel третий) аргумент — режим отображения. У него четыре возможных значения — от единицы до четырех.

Первый режим — это вариант по умолчанию (то есть если вы вообще опустите второй аргумент с номером режима, будет использоваться он). В нем сохраняются исходные пропорции,