MS, Libreoffice & Google docs

Пост до UPD

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

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

1, 21 и т.д. пикабушник

2,3,4, 22, 23, 24 и т.д. пикабушника

5, 6, 7, 8, 9, 10 и т.д. пикабушников

Т.е для всех чисел, которые заканчиваются на 1 - окончание [] (кроме 11)

Для всех чисел, которые заканчиваются на 2,3 и 4 - окончание [а].

Для всех чисел, которые заканчиваются на 5, 6, 7, 8, 9, 0 - окончание [ов].

В книжках по программированию для большинства языков этот кейс решается функцией switch case. Но в экселе и ГТ нет такой функции. Предлагаю заменить ее ВПРом внутри функции.

Логика следующая - мы смотрим остаток от деления на 10 и в зависимости от результата возвращаем то или иное окончание.

Осталось только решить вопрос с 11. Здесь лучше всего использовать деление на 100 и, т.к. варианта тут всего 2 - 11 или все остальное - нам подойдет обычный if.

Итог:

Здесь видно, что результатом, который возвращает ВПР может быть и формула.

Покажу еще один пример. Это уже из боевого. Когда используются фильтры по датам - иногда удобно использовать понятные конструкции ("Текущая неделя", "Текущий месяц") и дать при этом возможность пользователю выбрать произвольную дату.

Мы ограничимся тремя вариантами - "Текущий месяц", "Прошлый месяц" и "Произвольная дата".

Если пользователь выбирает "Текущий месяц" или "Прошлый месяц", то ВПР внутри фильтра возвращает ему значение даты, которая задается формулой "КОНЕЦМЕСЯЦА(СЕГОДНЯ())". В случае, если он выбирает "Произвольная дата", то ВПР возвращает ошибку и срабатывает функция "ЕСЛИОШИБКА", которая возвращает значение даты, выбранной пользователь в ячейках F2 и F3.

Вот и все, дорогие чатлане. Надеюсь этот пост был полезен. Вопросы и предложения, а также критику пишите в комменты.

Ссылка на таблицу:

https://docs.google.com/spreadsheets/d/1Q6xXFxfSWytAlcfY823i...

Теперь попробуем с помощью D-функции dcounta  (БСЧЁТА в русской версии) получить кол-во продаж у второго сейла:

Первый аргумент - таблица, с которой мы будем работать. Может быть задана и как a1:c6.

Второй - столбец, с которым мы работаем.

Третий - пачка массивов с условиями. Сейчас подробнее расскажу как работают условия здесь.

Для этого попробую получить сумму всех продаж первого сейла после 10.06:

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

Можно записать это внутри формулы. Чтобы задать массив в формуле нужно вписать значения в фигурные скобки. Для добавления строки нужно использовать ; а для столбца , (\ в русской версии таблиц):

И сама формула будет выглядеть:

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

И теперь вариант с dsum:

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

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

Ссылка на таблицу:

https://docs.google.com/spreadsheets/d/1qjMNiv7vonopcRiKFJu4...

Ссылка на документацию:

https://support.google.com/docs/answer/173497

Первый способ: Специальная вставка

Копируйте данные;

Встаньте в необходимом месте и нажав сочетание клавиш CTRL+ALT+V, или правая кнопка мыши (пкм), в меню иконка Транспонировать или выберите Специальная вставка:

В открывшемся окне поставьте галку напротив Транспонировать:

Готово.

Свойства: при обновлении данных в исходной таблице, данные в новой таблице не обновляются, это обычное копирование.

Способ второй: функция ТРАНСП

Выделите область, в которую необходимо вставить таблицу (в размер будущей перевернутой таблицы);

Введите =ТРАНСП(массив), где массив — это диапазон исходной таблицы;

Нажмите CTRL+SHIFT+ENTER, т.к. это формула массива и просто ENTER не сработает;

Готово.

Свойства: при обновлении данных в исходной таблице, данные в новой таблице обновляются.

Способ третий: транспонирование с помощью Power Query

В зависимости от версии вашего Excel, путь для загрузки в редактор может отличаться, подробнее в статье Power Query: мощь и простота работы с данными в Excel

Загрузите таблицу в редактор: Данные ► Получить данные ► Из других источников ► Из таблицы/диапазона;

Последовательно выполните действия:

1. Главная ► Использовать первую строку в качестве заголовка ► Использовать заголовки как первую строку;

2. Преобразование ► Транспонировать;

3. Главная ► Использовать первую строку в качестве заголовка;

Загрузите запрос: Главная ►Закрыть и загрузить ► Закрыть и загрузить в... ►Только создать подключение;

В окне Запросы и подключение ► пкм ► Загрузить в... ► Таблица.

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

Надстройка Power Query имеет очень большие возможности использования и стоит времени на её изучение. Поверьте, все с лихвой окупится в будущем, если вы часто и много работаете в Excel.

Свойства: при добавлении или обновлении данных в исходной таблице, данные в новой таблице обновляются. Самый автоматизированный вариант, если вам нужны связанные данные. Связь может быть, как с таблицей в текущей книге, так и с другим файлом (-ами). Подробнее Excel Power Query: создание основных запросов

Алгоритм

Откройте файл:

Нажмите CTRL+P (откроется окно печати) или выберите Файл ► Печать, настройте Параметры печати:

Нажмите F12 или выберите Файл ► Сохранить, как, в открывшемся окне выберите место, куда сохранить будущий файл PDF, переименуйте при необходимости:

Нажмите Тип файла и выберите вариант PDF:

В Параметрах выберите вариант сохранения (все страницы или отдельные, книгу или диапазон), нажмите ОК:

Сохранить, Готово.

Как решить проблему и отобразить ярлычки??? Лучше один раз увидеть ⬇⬇⬇

Интересное по теме Excel:

ТОП-30 горячих клавиш в Excel

Мгновенное заполнение

Трюки с листами книги

Быстро удалить все картинки с листа

Быстрое перемещение строк и столбцов

"Умные" таблицы в Excel

Сводные таблицы в Excel: как создать?

Как построить график или диаграмму из Сводной таблицы

Суммирование в Excel

Поиск и удаление повторяющихся значений в Excel