Excel. диапазон ячеек в формуле

Как в Excel автоматически заполнить диапазон набором значений

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

Например, на рис. 35.1 показан ряд последовательных чисел в столбце А. Ячейка А1 содержит значение 1, а ячейка А2 содержит формулу, которая была скопирована вниз по столбцу: =А1+1

Рис. 35.1. Excel предлагает простой способ сгенерировать ряд значений

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

1. Введите 1 в ячейку А1.
2. Введите 2 в ячейку А2.
3. Выберите А1:А2.
4. Переместите указатель мыши в правый нижний угол ячейки А2 (так называемый маркер заполнения ячейки) и, когда указатель мыши превратится в черный знак «плюс», перетащите его вниз по столбцу, чтобы заполнить ячейки.

Вы можете включать и отключать это поведение. Если у ячеек нет маркера заполнения, выберите Файл ► Параметры, перейдите в раздел Дополнительно диалогового окна Параметры Excel. Здесь в области Параметры правки установите флажок Разрешить маркеры заполнения и перетаскивание ячеек.

Данные, введенные в шагах 1 и 2, обеспечивают Excel необходимой информацией для определения типа серии, которую надо использовать. Если бы вы ввели 3 в ячейку А2, то серия бы состояла из нечетных чисел: 1,3, 5, 7 и т. д.

Вот еще один трюк автозаполнения: если данные, с которых вы начинаете, являются беспорядочными, Excel завершает автозаполнение, выполняя линейную регрессию и заполняя диапазон спрогнозированными значениями. На рис. 35.2 приведен лист с ежемесячными значениями продаж за январь-июль. При использовании автозаполнения после выбора С2:С8 Excel продлевает наиболее вероятную линейную тенденцию продаж и заполняет недостающие значения. На рис. 35.3 показаны спрогнозированные значения, а также график.

Рис. 35.2. Значения продаж за январь-июль, которые будут использованы для автозаполнения дальнейших цифр

Рис. 35.3. Цифры продаж последних пяти месяцев после использования автозаполнения для прогнозирования

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

Таблица 35.1. Типы данных с возможностью автозаполнения

Первое значение	Автоматически сгенерированные значения
Воскресенье	Понедельник, вторник, среда и т. д.
Квартал-1	Квартал-2, Квартал-3, Квартал-4, Квартал-1 и т. д.
Янв	Фев, Map, Апр и т. д.
Январь	Февраль, Март, Апрель и т. д.
Месяц 1	Месяц 2, Месяц 3, Месяц 4 и т. д.

Вы также можете создавать собственные списки элементов для автоматического заполнения. Для этого откройте диалоговое окно Параметры Excel и перейдите в раздел Дополнительно. Затем прокрутите окно вниз и нажмите кнопку Изменить списки для отображения диалогового окна Списки. Введите ваши элементы в поле Элементы списка (каждый на новой строке). Затем нажмите кнопку Добавить, чтобы создать список. На рис. 35.4 показан пользовательский список названий регионов, которые используют римские цифры.

Рис. 35.4. Эти имена регионов работают с функцией автоматического заполнения Excel

Для большего контроля над тем, что происходит при использовании автозаполнения, используйте правую кнопку мыши при перетаскивании маркера заполнения. Отпустив кнопку, вы увидите контекстное меню с некоторыми параметрами (рис. 35.5). Элементы, которые доступны в контекстном меню, зависят от типа выбранных данных. Например, если первая ячейка в серии содержит дату, то будут включены связанные с датой параметры.

Рис. 35.5. Контекстное меню для автозаполнения

Сортировка, фильтр, таблица

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

Кроме этого, Вы можете настроить фильтр в контекстном меню. Фильтр по цвету, шрифту, значку, значению – скроется всё, что Вы не хотите видеть, и останется только то, что нужно.

Помните, мы говорили о подведении итогов при помощи вкладки Design (Конструктор) – все это легко сделать, используя правую кнопку мыши. Здесь Вы можете создать строку итогов (диапазон данных будет выбран автоматически) или преобразовать таблицу обратно в диапазон. Всё форматирование и данные сохранятся, а если Вы мастерски владеете горячими клавишами, то можете нажать Ctrl+Shift+T.

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

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Преобразование таблицы Excel в диапазон данных

После создания таблицы в Excel может оказаться, что функции таблицы больше не нужны или требуется только стиль таблицы.

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

1. Щелкните в любом месте таблицы, чтобы активная ячейка находилась в столбце таблицы.

2. На вкладке Конструктор в группе Сервис выберите команду Преобразовать в диапазон.

После обратного преобразования таблицы в диапазон функции таблицы станут недоступны. Например, заголовки строк больше не будут содержать стрелки для сортировки и фильтрации.

: Задание 11.

1. На листе Договоры удалите таблицу.

Для этого щелкните в любом месте таблицы и выполните команду Конструктор, Сервис, Преобразовать в диапазон.

Убедитесь в том, что теперь на листе обычный диапазон данных, а не таблица (заголовки строк не содержат стрелки для сортировки и фильтрации).

ПРОМЕЖУТОЧНЫЕ ИТОГИ

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

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

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

Пустые строки должны отсутствовать, а данные должны быть отсортированы.

Команда Промежуточные итоги недоступна при работе с таблицей Excel. Чтобы добавить промежуточные итоги в таблицу, необходимо сначала преобразовать ее в обычный диапазон данных.

Промежуточные итоги вычисляются с помощью итоговой функции (как правило, СУММА или СРЕДНЕЕ, с использованием функции ПРОМЕЖУТОЧНЫЕ ИТОГИ).

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

1. В окне открытого листа отсортируйте диапазон данных по столбцу, который формирует группу, а затем выделите диапазон.

2. Выполните команду Данные, Структура, Промежуточные итоги. Откроется окно промежуточные итоги:

3. В окне Промежуточные итоги в графе При каждом изменении в выберите столбец, по которому будут подводиться итоги.

4. В графе Операция раскройте список и выберите статистическую функцию, по которой будут вычисляться итоги (Сумма, Среднее и т. д.)

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

6. Если между итогами необходим разрыв страницы, то активируйте пункт Конец страницы между группами.

7. Чтобы созданные итоги отображались над значениями, необходимо отключить пункт Итоги под данными.

: Задание 12.

Вычислите промежуточные итоги по сотрудникам: общее количество договоров, заключенных каждым сотрудником.

1. На данном листе отсортируйте данные по столбцу, который формирует группу – Сотрудник.

2. Выполните команду Данные, Структура, Промежуточный итог. Откроется окно промежуточные итоги:

3. В окне Промежуточные итоги в графе При каждом изменении в выберите столбец, по которому будут подводиться итоги, – Сотрудник.

5. В графе Операция раскройте список и выберите статистическую функцию, по которой будут вычисляться итоги – Количество.

6. В группе Добавить итоги по отметьте столбец, содержащий значения, по которым будет подводиться итог, – Номер договора.

7. Установите флажок Итоги под данными.

9. Покажите результаты работы преподавателю.

10. Закройте файл.

Дата добавления: 2019-01-14 ; просмотров: 174 ;

Работа с диапазонами в Excel

Мне приходится часто, занимаясь мониторингом на сайте http://monitoring-mo.ru, снимать аналитику по отдельным таблицам по всем учреждениям района.

При этом формируется таблица Excel, в которой обычно сверху стоит наименование таблицы, далее шапка и данные.

Левый первый столбец – это полное наименование ОУ, второй столбец период, а далее идут данные, Таблица организована не достаточно хорошо.

1. Нет разрыва строк между названием таблицы и шапкой;
2. 2 первых ячейки шапки объединены и нет наименования ячейки;
3. Название ОУ содержит впереди своего имени название района и косую черту.

Мне, как правило, нужно отделить таблицу от названия, вставив строку

Rows(“2:2”).Select Selection.Insert Shift:=xlDown

Затем надо разделить две ячейки

Range(“A3:B3”).Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.UnMerge

Здесь можно обойтись на самом деле всего 2-мя командами – первой и последней. Всё остальное создано макрорекордером, которым я пользуюсь часто.

В первые 2 ячейки шапки вводим наименования.

Range(“A3”).Select ActiveCell.FormulaR1C1 = “Наименование ОУ” Range(“B3”).Select ActiveCell.FormulaR1C1 = “Период”

Убираем далее название района в наименованиях ОУ

Columns(“A:A”).Select Range(“A3”).Activate Selection.Replace What:=”Орехово-Зуевский муниципальный район / “, _ Replacement:=””, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _ False, SearchFormat:=False, ReplaceFormat:=False

А вот далее возникает проблема с выделением диапазонов. Мне нужно бывает выделить не весь диапазон, что можно сделать командой

или получить объект Range с этим диапазоном

Set tbl = ActiveCell.CurrentRegion

Играя с диапазоном, изменяя количество строк и столбцов, я могу выделить любую часть таблицы, над которой могу производить любые действия.

Чаще всего это выделение цветом диапазона ячеек, или изменение шрифта или выделение границ. Можно проводить различные суммирования и выборки.

Для работы с диапазонами на активном листе можно, используя также команду UsedRange. например: ActiveSheet.UsedRange.Address или ActiveSheet.UsedRange.Select. А получив адрес, можно его всячески изменять как и в команде UsedRange.

Пример выделения диапазона ячеек в файле Проверка работы с диапазоном на DropBox

Динамические диаграммы в Excel

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

1. Выделяем наш диапазон, после чего вставляем диаграмму типа «Гистограмма с группировкой». Найти этот пункт можно в разделе «Вставка» в разделе «Диаграммы–Гистограмма».
2. Делаем левый клик мышью по случайной колонке гистограммы, после чего в строке функций будет показана функция =РЯД(). На скриншоте вы можете посмотреть на детальную формулу. 
3. После этого в формулу нужно внести некоторые изменения. Необходимо заменить диапазон после «Лист1!» на название диапазона. В результате получится следующая функция: =РЯД(Лист1!$B$1;;Лист1!доход;1)
4. Теперь осталось в отчет добавить новую запись, чтобы проверить, обновляется ли диаграмма автоматически, или нет.

Полюбуемся теперь на нашу диаграмму.

Давайте подведем итоги, как мы действовали. Мы на предыдущем этапе создали динамический диапазон, размер которого зависит от того, сколько элементов в него входит. Для этого мы использовали комбинацию функций СЧЕТ и СМЕЩ. Мы этот диапазон сделали именным, и потом ссылку на это имя использовали в качестве диапазона нашей гистограммы

Какой конкретно диапазон выбирать в качестве источника данных на первом этапе, не столь важно. Главное – заменить его на имя диапазона потом

Так можно существенно сэкономить оперативную память.

Именованные диапазоны в формулах

Именованные диапазоны легко использовать в формулах

Например, допустим, вы назвали ячейку в своей книге «обновленной». Идея состоит в том, что вы можете поместить текущую дату в ячейку (Ctrl +) и ссылаться на дату в другом месте книги.

Формула в B8 выглядит так:

='Updated: '&  TEXT (updated, 'ddd, mmmm d, yyyy')

Вы можете вставить эту формулу в любое место книги, и она будет отображаться правильно. Каждый раз, когда вы меняете дату в «обновлено», сообщение будет обновляться везде, где используется формула. Видеть эта страница для получения дополнительных примеров.

Именованные диапазоны появляются при вводе формулы

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

Именованные диапазоны могут работать как константы

Поскольку именованные диапазоны создаются в центре, вы можете использовать их как константы без ссылки на ячейку. Например, вы можете создать такие имена, как MPG (миль на галлон) и CPG (стоимость галлона), и назначить фиксированные значения:

Затем вы можете использовать эти имена где угодно в формулах и обновлять их значение в одном центральном месте.

Именованные диапазоны по умолчанию являются абсолютными

По умолчанию именованные диапазоны действуют как абсолютные ссылки. Например, на этом листе формула для расчета топлива будет следующей:

=C5$D

Ссылка на D2 является абсолютной (заблокированной), поэтому формулу можно скопировать без изменения D2.

Если мы назовем D2 «MPG», формула будет иметь следующий вид:

=C5MPG

Поскольку по умолчанию MPG является абсолютным, формулу можно скопировать в столбец D как есть.

Именованные диапазоны также могут быть относительными

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

Например, вы можете создать общий именованный диапазон CellAbove следующим образом:

1. Выберите ячейку A2
2. Control + F3, чтобы открыть диспетчер имен
3. Перейдите в раздел ‘Относится к’ и введите: = A1.

CellAbove теперь будет извлекать значение из ячейки выше, где бы оно ни использовалось.

Важно: перед созданием имени убедитесь, что активная ячейка находится в правильном месте

Применение именованных диапазонов к существующим формулам

Если у вас есть формулы, в которых не используются именованные диапазоны, вы можете попросить Excel применить именованные диапазоны в формулах за вас. Начните с выбора ячеек, содержащих формулы, которые вы хотите обновить. Затем запустите Формулы> Определить имена> Применить имена.

Затем Excel заменит ссылки, имеющие соответствующий именованный диапазон, самим именем.

Вы также можете применять имена с помощью поиска и замены:

Что такое именованный диапазон ячеек в Excel?

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

По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.

Для имен действует ряд ограничений:

— имя может содержать до 255 символов;

— первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;

— имена не могут быть такими же, как ссылки на ячейки;

— пробелы в именах не допускаются;

— строчные и прописные буквы не различаются.

Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке «Формулы», в группе кнопок «Определенные имена».

Основные действия с диапазонами

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

Сравнение диапазонов — это одна из классических задач в Excel, которую рано или поздно приходится решать любому пользователю Excel. Задача по сравнению диапазонов может быть поставлена по разному. Когда-то нужно найти различия или совпадения в диапазонах при построчном их сравнении, а когда-то необходимо узнать есть ли что-то общее в сравниваемых диапазонах вообще. В зависимости от поставленной задачи различаются и методики её решения.

Например, для построчного сравнения часто используется логическая функция «ЕСЛИ» и какой-либо из операторов сравнения (также можно использовать и другие функции, например «СЧЕТЕСЛИ» из категории статистические для проверки вхождения элементов одного списка в другой).

Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке «Главная», в группе кнопок «Редактирование», в меню кнопки «Найти и выделить». Если в этом меню выбрать пункт «Перейти» и далее нажать кнопку «Выделить», то в диалоговом окне «Выделение группы ячеек» можно выбрать одну из опций «Отличия по строкам» или «Отличия по столбцам».

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

Сравнение диапазонов можно провести и при помощи надстройки для Excel, которая позволяет находить и подсвечивать заливкой различия или совпадения в двух заданных диапазонах.

Изменение (преобразование) диапазонов значений

Одним из способов преобразования диапазона значений является транспонирование. Транспонирование — это такое преобразование диапазона значений, при котором данные, расположенные построчно перемещаются в столбцы и наоборот с сохранением порядка, то есть первая строка становится первым столбцом, вторая строка — вторым столбцом и так далее.

Транспонирование можно осуществить при помощи функции «=ТРАНСП(Диапазон)», которая находится в категории «Ссылки и массивы». Есть и другой способ — копирование диапазона значений с последующей специальной вставкой, при которой ставится флажок в поле «Транспонировать».

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

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

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи. 

А теперь о том, как Таблицы облегчают жизнь и работу.

Работа с диапазоном в переменной

Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки

1 2	MsgBox myRange.Cells(6) MsgBox myRange.Cells(6).Value

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

Важно: если вы планируете работать только со значениями, используйте переменные массивов, код в них работает значительно быстрее. Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 – работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SubTest1() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Заполняем первую строку ‘Присваиваем значение первой ячейке myRange.Cells(1,1)=5 ‘Присваиваем значение второй ячейке myRange.Cells(1,2)=10 ‘Присваиваем третьей ячейке ‘значение выражения myRange.Cells(1,3)=myRange.Cells(1,1)_ *myRange.Cells(1,2) ‘Заполняем вторую строку myRange.Cells(2,1)=20 myRange.Cells(2,2)=25 myRange.Cells(2,3)=myRange.Cells(2,1)_ +myRange.Cells(2,2) ‘Заполняем третью строку myRange.Cells(3,1)=»VBA» myRange.Cells(3,2)=»Excel» myRange.Cells(3,3)=myRange.Cells(3,1)_ &» «&myRange.Cells(3,2) EndSub

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

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

1 2 3 4 5 6 7 8 9 10 11 12

SubTest2() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Первую строку выделяем жирным шрифтом myRange.Range(«A1:C1»).Font.Bold=True ‘Вторую строку выделяем фоном myRange.Range(«A2:C2»).Interior.Color=vbGreen ‘Третьей строке добавляем границы myRange.Range(«A3:C3»).Borders.LineStyle=True EndSub

Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали

Пример 3 – копирование и вставка диапазона из переменной

Значения ячеек диапазона, присвоенного переменной, передаются в другой диапазон рабочего листа с помощью оператора присваивания.

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SubTest3() ‘Объявляем переменную DimmyRange AsRange ‘Присваиваем диапазон ячеек SetmyRange=Range(«C6:E8») ‘Присваиваем ячейкам рабочего листа ‘значения ячеек переменной диапазона Range(«A1:C3»)=myRange.Value MsgBox»Пауза» ‘Копирование диапазона переменной ‘и вставка его на рабочий лист ‘с указанием начальной ячейки myRange.Copy Range(«E1») MsgBox»Пауза» ‘Копируем и вставляем часть ‘диапазона из переменной myRange.Range(«A2:C2»).Copy Range(«E11») EndSub

Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.

Автосохранение

Часто при аварийном завершении работы программы не удается сохранить внесенные изменения и приходится заново проводить работу с файлом. Чтобы избежать таких проблем, существует функция автосохранения.

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

Для этого нужно нажать Файл (или кнопку Офис на ленте), выбрать Параметры Excel, открыть Сохранение, установить флажок Автосохранение каждые… и время (например, 1 минута), нажать ОК.

Более подробно об автосохранении читайте здесь.

Кратко об авторе:

Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.

Автоматическая подсветка цветом диапазонов ячеек по условию

Будем подсвечивать цвет диапазона, который соответствует порядковому номеру указанном в критериях выборки C1.

1. Выделите диапазон ячеек C7:C22 и выберите иснтрумент «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».
2. В появившемя окне выберите опцию «Использовать формулу для определения форматируемых ячеек». Там же в поле ввода введите такую фомрулу:
3. Нажмите на кнопку формат и укажите цвет для подсветки соответствующих ячеек. Например, зеленый.

Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон

Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

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

Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.

Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите: Месяц ;
• в поле Область выберите лист Книга ;
• в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
• нажмите ОК.

Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .

При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков

Теперь создадим еще один динамический диапазон для суммирования продаж.

Для создания динамического диапазона :

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите: Продажи_за_месяц ;
• в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
• нажмите ОК.

Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).

И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.

Или, например, в апреле.

Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .