Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: подсчитать, сколько рабочих дней попадает между двумя датами. Функция Excel ЧИСТРАБДНИ прекрасно справляется с задачей, если у вас 5-дневная рабочая неделя с субботой и воскресеньем в качестве выходных. Эта заметка покажет вам, как выполнить расчет рабочей недели любой продолжительности и любым набором выходных.

Прим. Багузина. Следует отметить, что, начиная с версии 2010 в Excel появилась более гибкая функция ЧИСТРАБДНИ.МЕЖД, которая дает возможность расчета для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней. Эта функция будет описана во второй части заметки. Тем не менее, подход Билла Джелена остается интересным и сам по себе.

ЧИСТРАБДНИ вычисляет количество рабочих дней между двумя датами, включая начальную и конечную даты. Следует указать более раннюю дату в качестве первого аргумента, более позднюю дату в качестве второго аргумента, и дополнительно определить массив праздничных дней в качестве третьего аргумента (рис. 1). Формула в ячейки C3: =ЧИСТРАБДНИ(A3;B3;$E$2:$E$11). 17.02.09 вычесть 10.02.09 дает 8 календарных дней, минус два выходных и один праздничный день. Итого, 5 рабочих дней. Однако, если вам «посчастливилось» работать с понедельника по субботу, стандартная формула не справится с задачей.

Рис. 1. ЧИСТРАБДНИ предполагает, что рабочая неделя длится с понедельника по пятницу

Подробнее »Рассчитать количество рабочих дней

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Подробнее »Найти первое непустое значение в строке

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

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

Рис. 1. Использование вспомогательного столбца

Подробнее »Функция СУММПРОИЗВ

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: в то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА и ЛОЖЬ в 1 и 0, СУММПРОИЗВ не делает этого (рис. 1).

Рис. 1. Странно, но внутри функции СУММПРОИЗВ Булевы значения не конвертируются в числа

Подробнее »Превращение логических значений ИСТИНА/ЛОЖЬ в числа с помощью операции минус минус

Билл Джелен – автор 24 книг по Excel (см., например, Сводные таблицы в Microsoft Excel 2013, Всё о ВПР: от первого применения до экспертного уровня), ведущий сайта http://mrexcel.com/. На самом деле эта книга была написана тысячами людей, участниками форума сайта MrExcel.com. Во многих случаях новые идеи возникали в процессе обсуждения – кто-то задавал вопрос, кто-то отвечал, затем решение шлифовалось, предлагались альтернативы. Сегодня на форуме более 100 000 зарегистрированных участников, сотни завсегдатаев, и шесть миллионов уникальных посетителей в год. Сообщество MrExcel.com дает ответы на 30 000 вопросов в год. А в архиве имеется около 365 000 ответов. Так что вполне вероятно, что там можно найти ответ на почти любой вопрос.

Bill Jelen. Excel Gurus Gone Wild: Do the IMPOSSIBLE with Microsoft Excel. – Holy Macro! Books, 2009 – 248р.

Подробнее »Билл Джелен. Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel

Если вам нужно преобразовать формулы в их текущие значения, можно пойти несколькими путями. Здесь представлены пять способов решения этой задачи. Одни методы покажутся более удобными тем, кто предпочитает использовать сочетания клавиш, другие – любителям применять мышь. [1]

Способ 1. Для любителей клавиатуры:

1. Выделите диапазон, содержащий формулы. Встаньте в любую ячейку диапазона и нажмите Ctrl+Ф (выделить всё).
2. Нажмите Ctrl+С, чтобы скопировать диапазон в буфер.
3. Нажмите Alt. Вы перейдете в режим вызова меню с клавиатуры. Не отпуская Alt продолжайте последовательно набирать Я (меню ГЛАВНАЯ), М (Вставка), З (буква З – вставить Значения). [2] Нажмите Enter. Обратите внимание, что в момент «хождения» по меню, Excel выводит на экран подсказки (рис. 1).

Рис. 1. Вызов команд меню с клавиатуры: Alt+Я+М – Вставить

Подробнее »Excel. Превращение формул в значения

Это седьмая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня.

Функции СМЕЩ и ДВССЫЛ являются весьма сложными для восприятия. Настоящая заметка и примеры, содержащиеся в ней, должны позволить вам освоить эти функции. См. также ссылки на дополнительный материал в конце заметки.

Суммирование по диапазону, размер которого вычисляется в ячейке. Проблема: мне нужно просуммировать значения в диапазоне, начинающемся в ячейке A5, а количество строк диапазона суммирования задано в ячейке С5 (рис. 7.1).

Стратегия: использовать подвижную (гибкую) функцию СМЕЩ, которая позволит вам:

• Начать отсчет из конкретной ячейки или диапазона ячеек;
• Сместить начало отсчета на заданное число строк и столбцов;
• Задать число строк и столбцов диапазона, считая от нового начала отсчета.

Рис. 7.1. Эта формула суммирует значения в диапазоне А5:А9

Подробнее »Excel. Использование функций СМЕЩ и ДВССЫЛ для поиска