Октябрь 2015

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

Задача: требуется построить формулу, которая возвращает сумму всех цифр в строке текста. Например, применяя формулу к текстовой строке «мне 24 года, а моему отцу – 43», хотелось бы получить 13 (2 + 4 + 4 + 3).

Решение. Вы знаете, что символы, которые участвуют в операции сложения, должны являться цифрами от 1 до 9. Поэтому алгоритм суммирования может быть следующим:

1 * количество единиц в строке +

2 * количество двоек в строке +

… +

9 * количество девяток в строке = результат

Чтобы узнать количество вхождений цифры (скажем, 4), можно воспользоваться функцией ПОДСТАВИТЬ с заменой цифры 4 на пустую строку. Например, ПОДСТАВИТЬ(текст;4;"") вернет текст без всех четверок. В нашем примере – «мне 2 года, а моему отцу – 3». Если подсчитать разность длин исходной и получившейся строк, то это и будет количество четверок в строке: =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;4;"")). Ответ = 2.

Подробнее »Сумма цифр содержимого ячейки

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

Задача: некоторые инженерные проблемы проектирования требуют использования таблиц для вычисления значений параметров. Поскольку таблицы являются дискретными, дизайнер использует линейную интерполяцию для получения промежуточного значения параметра. Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

Рис. 1. Высота над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр)

Подробнее »Определение промежуточного значения методом линейной интерполяции

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

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

Техника, описанная в этой заметке не совсем обычна. Предлагается формула, основанная на циклической ссылке. Как правило, циклические формулы – зло, но иногда они полезны (см., например, Использование циклических ссылок для решения уравнений итерационным способом).

Выполните следующие действия.

Пройдите по меню ФАЙЛ –> Параметры. Перейдите на вкладку Формулы, поставьте галку напротив Включить итеративные вычисления. Установите Предельное число итераций – 100 (рис. 1). Нажмите Ok.

Рис. 1. Включить итеративные вычисления

Подробнее »Запись текста в обратном порядке на основе формулы с циклической ссылкой

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

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

Решение: ВПР не может решить эту проблему. Комбинация двух функций СМЕЩ и ПОИСКПОЗ могла бы это сделать, при условии, что таблица будет отсортирована по ключевому столбцу (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). Но если вам по каким-то соображения не удобно делать сортировку, можете воспользоваться пользовательской функцией VLOOKUPNTH (рис. 1). Вам нужно добавить эту функцию в проект VBA (если вы никогда не делали этого, см. Сумма по цвету ячеек в Excel). Чтобы получить доступ к коду в приложенном Excel-файле можно, например, кликнуть правой кнопкой мыши на ярлык листа и выбрать Исходный текст. Функция работает, как и ВПР, но вместо использования ЛОЖЬ/ИСТИНА в качестве четвертого аргумента, вы указываете, какое по порядку вхождение вы хотите получить для искомого значения.

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах, начиная с G

Подробнее »ВПР для поиска N-го вхождения и возврата из левого столбца

Это глава из книги Билла Джелена Гуру 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. Сортировка с помощью формулы

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