MS, Libreoffice & Google docs

В этой статье мы рассмотрим пример использования функции ВПР для подсчёта оставшегося количества дней до заданной даты.

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

=-ВПР(;B2-ДАТА(ГОД(B2)+{1:0};МЕСЯЦ(A2);ДЕНЬ(A2));1)

=-VLOOKUP(;B2-DATE(YEAR(B2)+{1:0};MONTH(A2);DAY(A2));1)

Рассмотрим формулу подробнее:

- Искомое значение в ВПР() ноль, а массив в котором происходит поиск состоит из двух значений. Последний аргумент функции опущен.

Описание из справки формулы ВПР: «Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение».

- Найденное значение это и есть количество дней до контрольного дня, но со знаком минус. Знак минус перед ВПР() меняет знак результата.

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

Также есть другой вариант этой же формулы:

=-ВПР(;B1-(МЕСЯЦ(A1)&{-1:0}-ГОД(B1))-ДЕНЬ(A1)+1;1)

=-VLOOKUP(;B1-(MONTH(A1)&{-1:0}-YEAR(B1))-DAY(A1)+1;1)

Кроме того, данный приём можно использовать для подсчёта оставшихся дней до дня рождения, применив в столбце «Текущая дата» формулу =СЕГОДНЯ()

Формулы взяты отсюда, пост оформлен мною.

"Ручной привод" в работе с данными, частое явление. Многие пользователи Excel, обрабатывают данные "привычным" для себя способом, с минимальной автоматизацией, тратя кучу времени. Мало, кто слышал и использует волшебный инструмент — Power Query.

Почему Power Query?

Power Query — технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять, преобразовывать и уточнять данные из различных источников для последующего анализа. Функции Power Query доступны в Excel и Power BI.

Аргументы ЗА изучение надстройки:

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

2. Быстрый способ, вы можете много сделать с данными, в несколько кликов мыши;

3. Разовая настройка, сформируйте запрос один раз и обновляйте его, когда происходит изменение данных в источнике, или настройте автоматическое обновление.

Возможности Power Query

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

1. Загружать данные из широкого спектра источников и подключаться к ним:

2. Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML...), по одному или несколько за раз, например из всех файлов указанной папки или непосредственно с листа(-ов) книги;

3. Выполнять слияние источников данных для дальнейшего анализа и моделирования с помощью Power Pivot и PowerView;

4. Выполнять очистку данных от мусора;

5. Причёсывать данные: исправлять регистр, числа-как-текст, разбирать текст на столбцы и склеивать обратно, делить дату на составляющие (год, квартал, месяц, день недели...) и т.д.;

6. Настраивать представление таблиц: используя фильтры, сортировку, изменение порядка столбцов, транспонирование, добавление итогов, разворачивая кросс-таблицы в плоские и сворачивать обратно;

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

Power Query: где искать, как установить?

Для Excel 2016, 2019 или Office 365: надстройка уже находится на вкладке Данные ► Получить и преобразовать:

Для версий 2013 и 2010: загрузите надстройку (официальный сайт Microsoft) выбрав версию, подходящую для вашего устройства. Как только вы загрузите файл, откройте его и следуйте инструкциям.

После этого автоматически откроется вкладка POWER QUERY на ленте:

Если вкладка не появилась, вам нужно ее отобразить:

1. Перейдите на вкладку Файл ► Параметры ► Надстройки;

2. В опциях Надстройки выберите Надстройки COM, нажмите Перейти;

3. Отметьте галочкой Microsoft Power Query for Excel ► ОК, вкладка появится на ленте.

Редактор запросов

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

1. Лента редактора запросов: Файл, Главная, Преобразование, Добавление столбца, Просмотр;

2. Запросы — окно с перечнем созданных запросов, можно свернуть / развернуть;

3. Строка формул, можно отобразить или скрыть в меню Просмотр ► Панель формул;

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

5. Меню для редактирования данных, открывается при нажатии на шапку столбца правой кнопкой мыши;

Панель параметры запроса:

6. Свойства — редактируемое поле названия запроса;

7. Примененные шаги запроса, включающая каждый шаг запроса. Шаги можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.

Power Query — запросы, которые может создавать любой, указывая системе, куда обратиться и какие действия выполнить. Команды записываются на языке М. Язык не требует знаний и навыков программиста: код генерируется автоматически. При помощи мыши вы можете решать почти все задачи, стоящие перед вами. Но иногда запрос нужно все-таки поправить, еще реже – написать полностью вручную.

Далее, выйдет серия статей о работе в Power Query, подписывайтесь, чтобы быть в курсе.

Статья для новичков и интересующихся Excel.

Содержание:

1. Быстрое копирование листа;

2. Копирование листа в другую книгу;

3. Как убрать линии сетки;

4. Изменение цвета ярлычка;

5. Быстрый подбор ширины столбца (высоты строки);

6. Скрытие и отображение листов;

7. Защита от изменения структуры книги;

8. Увеличение отображения данных (масштабирование);

9. Сравнение данных листов двух книг;

10. Закрепление областей листа книги.

Короткое видео ⬇⬇⬇

Быстрое копирование листа

Выделите ярлык, зажмите CTRL и зажав левую кнопку мыши, перетащите его в сторону.

Копирование листа в другую книгу

Выделите ярлык, правая кнопка мыши (пкм) ► Переместить или скопировать:

в открывшемся окне, выберите лист для копирования, поставьте галку Создать копию, выберите книгу для перемещения (уже открытая или новая) ► ОК:

Как убрать линии сетки

В строке меню, на вкладке Вид панели инструментов, в разделе (от версии Excel: Отображение, Показать, Показать/скрыть), уберите галку Сетка:

Изменение цвета ярлычка

Выделите ярлык ► пкм, в открывшемся окне ► Цвет ярлычка:

Быстрый подбор ширины столбца (высоты строки)

Выделите все столбцы (строки), наведите курсор на область между столбцами (строками), двойной щелчок левой кнопки мыши.

Скрытие и отображение листов

1. Скрытие: Выделите ярлык, пкм ► Скрыть:

2. Отображение: Выделите ярлык, пкм ► Показать (в открывшемся окне, выберите нужный лист):

Защита от изменения структуры книги

Защита от удаления, перемещения, копирования, переименования, скрытия и отображения листов, изменения цвета ярлычков.

Панель инструментов Рецензирование ► (Защита) Защитить книгу (пароль не обязателен):

Увеличение отображения данных (масштабирование)

Зажмите CTRL, вращением колеса мыши, от себя или на себя, меняйте размер отображения данных.

Быстро вернуть размер 100%: панель инструментов Вид ► (Масштаб) 100%:

Сравнение данных листов двух книг

Панель инструментов Вид ► (Окно) Рядом:

Изменить расположение окон: Вид ► (Окно) Упорядочить все:

Варианты расположения окон:

Синхронная прокрутка листов: Вид ► (Окно) Синхронная прокрутка:

Закрепление областей листа книги

В строке меню, на вкладке Вид панели инструментов, в разделе (Окно) Закрепить области, выбрать один из трёх вариантов закрепления:

Для снятия закрепления, выберите Вид (Окно) ► Закрепить области ► Снять закрепление областей.

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

Как определить количество дней между датами?

Для определения количества дней между датами (от и до), в Excel необходимо вычесть большую дату из меньшей. Формат итоговой ячейки должен быть числовой. Ограничением являются операции с датами до 1 января 1900 г., так повелось исторически. Почему? Microsoft их знает.

Допустим человек родился 04.07.1984, сегодня 07.06.2020, сколько дней человек прожил?

=A2-B2, где A1 конечная дата, B2 начальная дата, ответ 13 122 дня.

Чтобы посчитать количество дней между датами:

=A2-B2-1

Как определить возраст человека?

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

= ДОЛЯГОДА(нач_дата;кон_дата;[базис]), где:

Нач_дата — начальная дата.

Кон_дата — конечная дата.
Базис — используйте 1, Excel делит фактическое количество дней в месяце на фактическое количество дней в году.

Модернизируем формулу, чтобы на выходе было целое число:

=ЦЕЛОЕ(ДОЛЯГОДА(B2;СЕГОДНЯ();1)), где B2 — день рождения человека.

Выполнить подобную задачу может и функция РАЗНДАТ, которая вычисляет количество дней, месяцев или лет между двумя датами.

=РАЗНДАТ(нач_дата,кон_дата,единица)

функция возвращает разницу в годах, месяцах и днях, в зависимости от параметра, который вы задаете в аргументе (единица):

Y - возвращает количество лет.

M - количество месяцев.
D - количество дней.
YM - возвращает месяцы, игнорируя дни и годы.
MD - разница в днях, игнорируя месяцы и годы.
YD - разница в днях, игнорируя годы.

=РАЗНДАТ(B2;СЕГОДНЯ();"Y"), где B2 — день рождения человека.

Расчёт в днях, месяцах и годах, немного усложним формулу:

=РАЗНДАТ(B2;СЕГОДНЯ();"Y")&" л. "&РАЗНДАТ(B2;СЕГОДНЯ();"YM")&" мес. "&РАЗНДАТ(B2;СЕГОДНЯ();"MD")&" д."

Сделаем совсем красиво:

=ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"Y");РАЗНДАТ(B2;СЕГОДНЯ();"Y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"Y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"YM");РАЗНДАТ(B2;СЕГОДНЯ();"YM")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"YM")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;СЕГОДНЯ();"MD");РАЗНДАТ(B2;СЕГОДНЯ();"MD")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;СЕГОДНЯ();"MD")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)

Сколько вам будет лет в определенный год?

Эта формула полезна в ситуациях, когда полная дата для расчёта не определена, вы знаете только год.

=РАЗНДАТ(B2;ДАТА(A2;1;1);"Y"), где B2 — день рождения человека, А2 год на который производится расчет.

Сколько лет вам будет на определённую дату?

И в этом случае нам поможет функция РАЗНДАТ:

=ЕСЛИ(РАЗНДАТ(B2;A2;"Y");РАЗНДАТ(B2;A2;"Y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;A2;"Y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;A2;"YM");РАЗНДАТ(B2;A2 ;"YM")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;A2;"YM")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;A2;"MD");РАЗНДАТ(B2;A2 ;"MD")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;A2;"MD")-11;100);9); 10);"[<1]ень;[<4]ня;ней");), где B2 — день рождения человека, А2 дата на которую производится расчет.

Узнаем дату, когда человек достигнет N лет

Предположим, сотрудник родился 15.07.1988 года. Необходимо определить, когда ему исполнится 65 лет? Нам поможет функция ДАТА, которая возвращает порядковый номер определенной даты. В формулу необходимо ввести последовательно функции ГОД, МЕСЯЦ и ДЕНЬ.

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

= ДАТА(ГОД(B2)+65; МЕСЯЦ(B2); ДЕНЬ(B2)), где B2 — день рождения человека, ответ 15.07.2053

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

Так можно посчитать дату окончания испытательного срока, прибавив дни, а не годы к ДЕНЬ.

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

Может быть есть кто разбирается?

Необходимо сделать сумму ячеек по цвету.

На просторах интернета нашел функцию для VBA

"Public Function SumByColorNumber(DataRange As Range, ColorSample As Long) As Double Dim Sum As Double

Application.Volatile True

For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColor = Sum

End Function"

,но тут идет сумма по цвету ячейки-образца, что для меня неудобно.

Кроме того, нашел еще функцию, что позволяет получать индекс цвета заливки ячейки

Public Function ЦВЕТЗАЛИВКИ(ЯЧЕЙКА As Range) As Double

ЦВЕТЗАЛИВКИ = ЯЧЕЙКА.Interior.Color

End Function

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

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

P.S. в VBA - чайник. Спасибо!

UPD рабочий вариант ответа на вопрос:

Public Function SumByColorNumber(DataRange As Range, Color As Long) As Double

Dim Sum As Double

Application.Volatile True

For Each cell In DataRange

If cell.Interior.Color = Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColorNumber = Sum

End Function

Считает. Всем спасибо!

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

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

Для отображения нужного порядка дней рождений сотрудников, нам необходимо сделать маленький приём.

В соседнем столбце используем функцию ТЕКСТ (TEXT), которая представляет числа и даты в заданном формате: =ТЕКСТ(B4;"ММ ДД")

Формат «ММ ДД» означает, что нужно из всей даты отобразить только номер месяца и день.

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

Вспомогательный столбец удаляем. Для большей наглядности можно задать разделительные линии между месяцами.

Выделяем весь список (кроме заголовка) и на вкладке Главная выбираем - Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule). В появившемся окне выбираем - Использовать формулу для определения форматируемых ячеек и вводим формулу: =МЕСЯЦ($B2)<>МЕСЯЦ($B3)

В разделе Формат на вкладке Границы (Borders) выбираем нижнюю границу ячейки, задаём понравившийся цвет линии и убираем лишние знаки доллара в формуле, чтобы закрепить в ней только столбцы.

Также можно на вкладке Вид выбрать Закрепить области – Зафиксировать верхнюю строку и ввести формулу =СЕГОДНЯ() для ежедневного обновления даты и визуального удобства.

P.S. При создании таблицы ни один шрифт не пострадал, только глаза)))

Шрифты для друзей _Arabian, a_Algerius, WienLight, Benguiat Rus, Romic

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

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

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

2. Выберите в строке меню Вставка ► Сводная диаграмма:

или на вкладке Анализ ► Сводная диаграмма.

3. В открывшемся окне, выберите один из вариантов представления, нажмите OK:

Совет: изначально выбирайте верный тип диаграммы и не мудрите с дизайном.

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

Для перемещения, выделите диаграмму, выберите на вкладке Конструктор (Анализировать) ► Переместить диаграмму:

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

Можно задать название нового листа с диаграммой, написав его в поле правее варианта расположения.

Плюсом сводных диаграмм, является динамичность и интерактивность, при совместном использование с Временными шкалами и Срезами. Такие диаграммы можно использовать для создания Дашбордов (Dashboard) в Excel.