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

Задача: Excel хранит дату как число дней, прошедших с 1 января 1900 года. Это означает, что все функции даты не работают для дат XVIII в. и более ранних. Это проблема для историков и генеалогов (людей, занимающихся генеалогией).

Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой – 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.

Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу

Подробнее »Excel. Даты до 1900 года

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

Задача: у вас есть числа в диапазоне D2:D11. Вам нужна формула, чтобы переставить числа по возрастанию или по убыванию.

Решение: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ (рис. 1). =НАИМЕНЬШИЙ($D$2:$D$11;1) возвращает наименьшее число в диапазоне, =НАИМЕНЬШИЙ($D$2:$D$11;2) возвращает второе минимальное число, и так далее.

Рис. 1. Сортировка с помощью формулы

Подробнее »Сортировка с помощью формулы

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

Задача: вы хотите сгенерировать случайные числа между 1 и 100, но… без дубликатов. Excel стандартно предлагает функции СЛЧИС() и СЛУЧМЕЖДУ(), но обе они, генерируют совершенно случайные числа с морем дубликатов.

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k). Как правило, она возвращает k-е наименьшее значение массива. Например, =НАИМЕНЬШИЙ({69;9;12;27;42;55};3) возвращает 27, так как, 1-е наименьшее число в списке – 9, 2-е – 12, а 3-е – 27. Нечисловые значения в массиве игнорируются. Например, =НАИМЕНЬШИЙ({69;9;12;ЛОЖЬ;42;55};3) вернет 42.

Решение: сейчас мы разработаем формулу, которая будет формировать массив из неиспользованных чисел диапазона, а затем выбирать из этих чисел (рис. 1).

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

Подробнее »Случайные числа без повторений

Заметка написана Андреем Макаренко

По крайней мере с версии Excel 2007 появилась давно ожидаемая возможность совместной работы нескольких пользователей в одной книге. Эта возможность позволяет совместно редактировать данные одной книги, на одном листе и даже в одной ячейке. Понятно, что в последнем случае разделение возможности совместной работы должно быть произведено административно. Впрочем, при попытке совместной работы в одной ячейке, Excel об этом честно предупреждает и записывает последнее из выполненных изменений.

Подробнее »Совместная работа нескольких пользователей в одном файле Excel

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

Задача: ссылки в стиле R1C1 полезны в нескольких ситуациях, в частности, при использовании VBA, функции ДВССЫЛ (см., например, последний раздел заметки Ссылка на другой лист с помощью ДВССЫЛ) и условного форматирования. Поэтому краткое знакомство с ними будет полезно.

До появления платформы IBM PC существовало несколько популярных приложений электронных таблиц. Это были и VisiCalc и Quattro Pro и Multiplan. Впервые стиль А1 именования ячеек был представлен в VisiCalc. Но этот продукт быстро проиграл конкурентную гонку. Multiplan выпускался компанией Microsoft до выхода Excel. В этом продукте для адресации ячеек использовался формат R1C1, который с тех пор доступен и в Excel. Но именно Лотус 1-2-3 вырвался на вершину славы сразу же после выхода в 1982 г. и стал доминировать на рынке приложений электронных таблиц для ПК. Не в последнюю очередь благодаря удобной системе именования ячеек А1. [1]

Во время войн электронных таблиц, Microsoft осознала, что большинство пользователей голосуют за стиль ссылок А1, и чтобы конкурировать, ей придется делать вид, что в Excel используются ссылки А1. Конечно, Excel только делает вид, что использует А1. В действительности «за кулисами» работают R1C1. Если вы не верите мне, пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и в области Работа с формулами поставьте галочку Стиль ссылок R1C1 (рис. 1).

Рис. 1. Вы всего в одном клике от стиля ссылок R1C1

Подробнее »Ссылки R1C1

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

Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.

Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.

Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк

Подробнее »Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В предыдущих главах вы познакомились с функцией ДВССЫЛ, узнали, как с ее помощью сослаться на другой лист, что делать, если имя листа содержит дату. Сегодня еще о двух интересных возможностях использования функции ДВССЫЛ.

Задача: как было показано ранее, ДВССЫЛ удобно использовать для получения значения ячейки, адрес которой вычисляется в другой ячейке. Можно использовать ДВССЫЛ для получения данных из диапазона ячеек, который затем использовался бы внутри функций ВПР или СУММЕСЛИ?

Решение: вы можете построить функцию ДВССЫЛ, которая ссылается на диапазон. Извлеченный диапазон далее может быть использован в качестве таблицы подстановки в ВПР или внутри СУММЕСЛИ, СЧЁТЕСЛИ.

Формула =СУММЕСЛИ(ДВССЫЛ(D$4&"!A2:A300");$A5;ДВССЫЛ(D$4&"!C2:C300")) в ячейке D5 (рис. 1) извлекает данные из рабочего листа Store3 (он указан в строке 4). Первый аргумент функции СУММЕСЛИ извлекает диапазон дат Store3!A2:A300. Второй аргумент – $A5 – осуществляет поиск записей, которые соответствуют определенной дате из столбца А. Третий аргумент – Store3!С2:С300 – находит количество в столбце С, соответствующее дате, и возвращает его.

Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе

Подробнее »Использование ДВССЫЛ для получения данных из диапазона ячеек

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. Предыдущая глава рассказывает о том, как сослаться на другой лист с помощью функции ДВССЫЛ. В ней говорится, что нужно применить некоторые ухищрения, когда ссылаешься на лист, в имени которого есть пробел. Здесь рассматривается второй сложный случай, когда имя листа содержит дату.

Задача: у вас 30 ежедневных рабочих листов в книге по одному за каждый день месяца (рис. 1). Ячейки (столбец А) на листе Сводная содержат даты, ссылки на которые вы хотите использовать внутри функции ДВССЫЛ, чтобы получить данные из соответствующего этому дню листа, но формула возвращает ошибку #ССЫЛКА!

Рис. 1. ДВССЫЛ не может напрямую использовать ссылки на листы, в имени которых содержится дата

Подробнее »ДВССЫЛ. Ссылка на лист, имя которого содержит дату