7. Полезняшки Excel

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). В этой главе рассматриваются ссылки на таблицы в формулах, как внутренние (внутри таблицы), так и внешние (вне таблицы).

Предыдущая глава        Содержание    Следующая глава

Структурированные ссылки особенно удобны, если используются говорящие имена таблиц и столбцов. Если таблица называется "Таблица1", а столбцы – "Столбец1", "Столбец2" и т.д., то структурированная ссылка будет не лучше, чем традиционная ссылка A1.

Понимание того, как работают структурированные ссылки, начинается с понимания их синтаксиса:

Рис. 4.1. Синтаксис структурированной ссылки

Подробнее »Глава 4. Структурированные ссылки в таблицах Excel

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). В этой главе мы обсудим различные методы работы с таблицами, включая создание таблиц, изменение их размера, вставку и удаление строк и столбцов, перемещение столбцов.

Предыдущая глава        Содержание    Следующая глава

Рис. 3.1. Диалоговое окно Создание таблицы

Подробнее »Глава 3. Работа с таблицами Excel

Менее 1% всех пользователей Excel используют таблицы. И это странно, потому что таблицы очень удобны. Их дизайн выглядит профессионально. Они автоматически расширяются при добавлении новых данных, что делает их удобным источником для сводных таблиц, диаграмм и динамических диапазонов. Они содержат строку итогов с настраиваемыми вычислениями. Сортировка и фильтрация не требуют дополнительных манипуляций, так как область данных всегда известна. Формулы используют структурированные ссылки, что позволяет поддерживать однородные данные в столбцах. Книга будет полезна и новичку, который не знает, что такое таблица, и эксперту, который найдет примеры формул и кода VBA.

Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.

Подробнее »Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц

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

Рис. 1. Быстрый переход к диапазону, на который ссылается формула

Подробнее »Быстрый переход к диапазону, на который ссылается формула

Возможно вы замечали всплывающее поле в момент редактирования формулы:

Рис. 1. Всплывающая подсказка

Аргумент функции, которую вы редактируете в данный момент, будет выделен на экране жирным шрифтом. В нашем примере – это искомое_значение.

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

Рис. 2. В подсказке выбран аргумент таблица

Подробнее »Всплывающие подсказки при вводе формул в Excel

Возможно вы сталкивались с тем, что при редактировании формулы вы просто хотели переместить курсор влево/вправо с помощью клавиши со стрелкой, но вместо этого в формулу вставлялась новая ссылка на ячейку?

Рис. 1. Вместо перемещения курсора вставка ссылки на ячейку

Подробнее »Режимы ввода и редактирования в Excel

По мере освоения Excel вас всё больше будут интересовать фишечки, позволяющие выполнять работу быстрее. Профессионалы знают, что клавиатура в этом плане лучше мыши. При редактировании или аудите формул, написанных кем-то другим, очень полезна клавиша F2. На рис. 1 выберите ячейку В6 и нажмите F2 для редактирования формулы. Это позволяет редактировать формулу непосредственно в ячейке:

Рис. 1. Нажмите клавишу F2, чтобы изменить формулу выбранной ячейки

Подробнее »Клавиша F2 и редактирование формул в Excel

Microsoft постоянно добавляет в Excel новые возможности в части анализа и визуализации данных. Работу с информацией в Excel можно представить в виде относительно независимых трех слоев:

• «правильно» организованные исходные данные
• математика (логика) обработки данных
• представление данных

Рис. 1. Анализ данных в Excel: а) исходные данные, б) мера в Power Pivot, в) дашборд; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Аналитические функции в Excel (функции кубов)

В Excel довольно широко представлены функции поиска – ВПР, ГПР, ПОИСКПОЗ в связке с ИНДЕКС, ПРОСМОТР, и некоторые другие. Все они относятся к категории функций Ссылки и массивы. При этом штатно они ищут первое встретившееся в диапазоне значение, отвечающее условиям поиска. Как было бы замечательно, если бы функция ВПР имела пятый аргумент, позволяющий выбрать направление поиска: с начала диапазона или с конца! Но увы, такой возможности нет. Ранее я описал изящный трюк, позволяющий с помощью функции ПРОСМОТР решить такую задачу. А недавно обнаружил довольно простую пользовательскую функцию, которую немного доработал и назвал ВПР2.

Рис. 1. Окно Аргументы функции ВПР2

Подробнее »Поиск последнего вхождения на основе пользовательской функции ВПР2

В силу различных обстоятельств иногда возникает потребность заменить формулы в ячейках значениями. Если это связный прямоугольный диапазон, сделать такую замену весьма просто. Выделите диапазон, нажмите Ctrl+C. Не снимая выделения, кликните правой кнопкой мыши в любом месте выделенного диапазона, выберите Специальная вставка, и в открывшемся окне выберите значения. Нажмите Ok. Если вы любите клавиатурные сокращения, то для специальной вставки используйте Alt+Я+М+З (версия для Excel 2016 и позднее; все буквы русские).

Рис. 1. Вставить только значения

Подробнее »Замена некоторых формул значениями