Избранное

Сортировка листов книги Excel... В настоящей заметке описана разработка утилиты VBA для Excel. Показан процесс анализа задачи и последующего ее решения....

Далее

Пол Экман. Психология лжи. Обмани... Правда ли, что современный человек в среднем лжет трижды за десять минут разговора? Как реагировать на то, что...

Далее

Работа с процедурами VBA Процедура — это последовательность операторов VBA, расположенная в модуле VBA, доступ к которому можно получить...

Далее

Джон Теннент. Управление денежными... Это пособие имеет целью ознакомить вас с основными принципами управления денежными средствами и денежным потоком,...

Далее

Основы программирования на... Для начала в качестве примера рассмотрим простую процедуру VBA типа Sub. Она хранится в модуле VBA и вычисляет сумму...

Далее

Назначение макросу сочетания клавиш

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

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

Задача: вы записали макрос, но его запуск через меню занимает несколько дополнительных кликов. Нельзя ли этот процесс как-то ускорить?

В Excel каждому макросу может быть назначена комбинация клавиш быстрого вызова. В предыдущей заметке рассказывалось, как создать личную книгу макросов. Тогда мы не обратили внимание на определение макросу сочетания клавиш, оставив это поле пустым (рис. 1). Никогда не поздно вернуться назад. Код VBA (в комментариях в верхней части макроса) хранит сочетание клавиш, которое запускает этот макрос (если мы определили это сочетание в момент записи). Однако, изменения в комментариях макроса не влияют на фактическое сочетание клавиш, требуемое для запуска макроса.

Рис. 2. Диалоговое окно Запись макроса

Рис. 1. Сочетание клавиш для запуска макроса не определено

Далее

Создание личной книги макросов

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

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

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

Решение: в Excel 2007 и более поздних версиях выполните следующие действия:

Откройте любую рабочую книгу или создайте новую (Ctrl+N).

В нижнем левом углу окна Excel справа от слова ГОТОВО в строке состояния вы найдете значок записи макроса (рис. 1). Щелкните по нему.

Рис. 1. Пиктрограмма записи макроса является одной из немногих иконок вне ленты

Рис. 1. Пиктограмма записи макроса является одной из немногих иконок вне ленты

Далее

Задание координат объекта DataLabel (подписи данных на диаграмме)

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

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

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

Рис. 1. Вы хотите узнать высоту и ширину объекта DataLabel для точки (210; 414)

Рис. 1. Вы хотите узнать высоту и ширину объекта DataLabel для точки (210; 414)

Далее

Создание сводной таблицы на основе нескольких листов

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

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

Задача: у вас данных больше, чем может поместиться на одном листе, или данные по иным причинам распределены на нескольких листах (например, один месяц – один лист, рис. 1, или один отдел – один лист). Вы хотите создать сводную таблицу из данных, распределенных между несколькими листами. В старых версиях Excel функция консолидации работает, мягко выражаясь, странно (см., Сводная таблица на основе нескольких листов). Начиная с Excel 2013 функция консолидации значительно улучшена (см. Сводная таблица на основе нескольких листов или диапазонов консолидации). Книга Джелена вышла в 2009 г., так что метод, предложенный в настоящей заметке, возможно, и не представляет значительной практической ценности. Однако, как я уже упоминал ранее, материалы Джелена методически интересны сами по себе, поэтому я счел полезным перевести и эту главу.

Рис. 1. Исходные данные расположены на пяти листах

Рис. 1. Исходные данные расположены на пяти листах

Далее

Добавление формул в рисунки SmartArt

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

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

Задача: для поклонников Excel одним из разочарований является статичность текста диаграмм SmartArt. Штатными средствами нельзя сделать так, чтобы содержание текста диаграммы SmartArt определялось динамически на основе формул.

Решение: вы можете пойти в обход; используйте инструмент SmartArt для построения диаграммы, преобразуйте диаграмму в фигуру, и лишь после этого примените формулы к тексту фигур.

На рис. 1 запрос к базе данных продаж, расположенной в столбцах А:С, возвращает в диапазон G4:G6 суммарные продажи по каждому агенту. Для этого используется функция СУММЕСЛИ. Например, в ячейке G4 формула: =СУММЕСЛИ($A$4:$A$204;F4;$C$4). Любопытно, что в качестве третьего аргумента – диапазона суммирования – используется указание на одну ячейку $C$4, а не на диапазон $C$4: $С$204. В ячейках Е4:Е6 используется функция РАНГ. Например, в ячейке Е4 формула: =РАНГ(G4;$G$4:$G$6). В диапазоне Е8:G10 те же результаты представлены по рангу. Для этого использованы формулы на основе функции ВПР (подробнее см. Использование ВПР для решения общих проблем, параграф Сортировка с помощью формулы). Эти результаты являются полезными, но им не хватает визуального эффекта.

Рис. 1. Формулы дают полезную, но визуально бедную информацию

Рис. 1. Формулы дают полезную, но визуально бедную информацию

Далее

Быстрое создание большого числа имен диапазонов

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

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

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

Подготовительная работа: если ваши заголовки подходят в качестве названия диапазонов, вы можете использовать их для быстрого создания имен диапазонов.

Решение: выберите набор данных, включая заголовки строк и столбцов. В Excel 2007 перейдите на вкладку ФОРМУЛЫ в область Определенные имена. Выберите команду Создать из выделенного. В окне Создание имен из выделенного диапазона выберите в строке выше и в столбце слева, нажмите Оk (рис. 1).

Рис. 1. Команда Создать из выделенного позволяет за несколько кликов присвоить имена большому числу диапазонов

Рис. 1. Команда Создать из выделенного позволяет за несколько кликов присвоить имена большому числу диапазонов

Далее

Александр Ивин. Логика

Рубрика: 11. О разном

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

Александр Ивин. Логика. Учебное издание для бакалавров, 3-е изд. – М.: Юрайт, 2012. – 400 с.

Александр Ивин. Логика. Обложка

Далее

Быстрое создание меню гиперссылок

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

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

Задача: вы создаете набор отчетов для людей, которые не знакомы с Excel, и вы хотите добавить лист меню, чтобы помочь им ориентироваться в книге. Как правило, создание гиперссылок на другое место в документе вызывает затруднения. Нет ли простого быстрого способа?

Решение: убедитесь, что ваша книга сохранена и является единственной открытой книгой Excel. В версии 2007 и более поздней перейдите на вкладку ВИД и кликните на кнопку Новое окно. Появится второе окно, в котором будет открыта та же книга. Снова на вкладке ВИД кликните на кнопку Рядом. В левом окне будет отражаться лист Menu. В правом окне нажмите сочетание клавиш Ctrl+PgDn и вы перейдете к первой странице отчета – листу Abstract.

Цель – извлечь заголовок из каждого листа рабочей книги и создать гиперссылку на листе меню (рис. 1). Выберите название в А1 из правого окна. Щелкните правой кнопкой мыши на правой границе ячейки A1, перетащите в ячейку В4 левого окна на лист меню, отпустите кнопку мыши, выберите пункт Создать гиперссылку (подробнее см. Альтернативное меню перетаскивания).

Рис. 1. Это контекстное меню предоставляет быстрый способ для создания гиперссылки

Рис. 1. Это контекстное меню предоставляет быстрый способ для создания гиперссылки

Далее

Альтернативное меню перетаскивания (Alternate Drag and Drop menu)

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

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

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

Решение: в Excel существует дополнительное (альтернативное) меню с полезными опциями (рис. 1). Чтобы открыть его, выделите ячейку или диапазон ячеек. Перейдите к правому краю данных. Щелкните правой кнопкой мыши и перетащите границу вправо в новую область. Отпустить кнопку мыши. Появится новое «секретное» меню (так называемое альтернативное меню перетаскивания; Alternate Drag and Drop menu).

Рис. 6. «Секретное» меню

Рис. 1. Альтернативное «секретное» меню перетаскивания

Далее

Копирование точной формулы с помощью клавиатурного сокращения Ctrl+’

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

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

Задача: вы ввели формулу СУММ в строке 23 (рис. 1). В следующей строке вы бы хотели подсчитать СРЗНАЧ. Однако, если вы скопируете ячейку В23 в В24, диапазон, на который ссылается формула будет автоматически изменен.

Рис. 1. Вы хотите изменить СУММ в строке 23 на СРЗНАЧ в строке 24

Рис. 1. Вы хотите изменить функцию СУММ в строке 23 на СРЗНАЧ в строке 24

Далее