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

Рубрика: 7. Полезняшки Excel

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

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

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

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

Читать полностью

Функция СУММПРОИЗВ

Рубрика: 7. Полезняшки Excel

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

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

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

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

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

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

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

Билл Джелен – автор 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. Обложка

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

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

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

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

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

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

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

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

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

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

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

Читать полностью

Excel. Продвинутый поиск с помощью ВПР и не только

Рубрика: 7. Полезняшки Excel

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

Многоуровневый комиссионный план: функция ЕСЛИ или ВПР. Проблема: я рассчитываю комиссию на основе скользящей шкалы. Процент зависит от объема продаж (рис. 6.1).

Рис. 6.1. Комиссионный процент в зависимости от объема продаж

Рис. 6.1. Комиссионный процент в зависимости от объема продаж

Читать полностью

Excel. Функции ИНДЕКС и ПОИСПОЗ

Рубрика: 7. Полезняшки Excel

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

Для извлечения данных вместо ВПР можно использовать связку функций ИНДЕКС и ПОИСКПОЗ. Формулы с ними работают быстрее и намного гибче. Лично я использую ВПР 90% времени, но иногда переключаюсь на ИНДЕКС + ПОИСКПОЗ, например, когда «ключевой» столбец не является первым слева в таблице.

Для меня функция ИНДЕКС совершенно непонятна. Проблема: я недавно прочитал справку Excel по этой функции. Кто в здравом уме будет использовать =ИНДЕКС(В4:G22;2;4), чтобы указать на ячейку F6 (рис. 5.1)?

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Рис. 5.1. Функция ИНДЕКС кажется бесполезной

Читать полностью

Excel. Использование ВПР для решения общих проблем

Рубрика: 7. Полезняшки Excel

Это четвертая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. В главе рассматривается базовое использование ВПР. Более сложные примеры рассматриваются в главах 6 и 7.

Есть ли этот товар в основном списке? Проблема: у меня есть список товаров, заказанных сегодня. Мне нужно сравнить его со списком позиций, которые есть в наличии на складе.

Рис. 4.1. Какие элементы из столбца С есть в наличии на складе (в столбце D)

Рис. 4.1. Какие элементы из столбца С есть в наличии на складе (в столбце D)?

Читать полностью

Excel. Облегчая использование функции ВПР

Рубрика: 7. Полезняшки Excel

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

Присвойте имя вашей таблице подстановки. Проблема: моя таблица подстановки расположена на другом листе. Формула ВПР может сбивать с толку:

=ВПР(A2;’Таблица подстановки’!$A$1:$B$30;2;ЛОЖЬ)

Стратегия: присвойте имя диапазону, включающему таблицу подстановки. Для этого выделите ячейки А2:В30. Щелкните в поле имя слева от строки формул. Введите простое имя, например, Описание и нажмите Enter. Теперь формула ВПР принимает вид: =ВПР(A2;Описание;2;ЛОЖЬ). Учтите, что имя диапазона не должно содержать пробелов и начинаться с цифры (подробнее см. Excel. Имена диапазонов).

Рис. 3.1. Введите имя таблицы подстановки в поле слева от строки формул

Рис. 3.1. Введите имя таблицы подстановки в поле слева от строки формул

Читать полностью