Счетчик - элемент управления формы в EXCEL

Элемент Счетчик позволяет изменять значения в определенном диапазоне с определенным шагом (1, 2, 3, ...). По умолчанию диапазон изменения значений определен от 0 до 30000, шаг =1.

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.

• В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
• В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно также вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п.  Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

Счетчик ( Spin button, Spinner ) как, впрочем и все другие Элементы управления формы, возвращает только 1 числовое значение. См. файл примера лист Счетчик .

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

Вставка Счетчика

Через меню Разработчик/ Элементы управления/ Вставить выберем левой клавишей мыши элемент Счетчик.

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

превратится в тонкий крестик.

Кликнув левой клавишей мыши в нужное место на листе, элемент Счетчик будет помещен на лист.

Выделение Счетчика

После вставки Счетчика он становится выделенным. Если кликнуть в любом другом месте листа, то Счетчик перестанет быть выделенным. Чтобы снова его выделить нужно кликнуть его ПРАВОЙ клавишей мыши (клик ЛЕВОЙ клавиши увеличивает или уменьшает значение в связанной ячейке (см. ниже)). После клика правой кнопкой также появляется контекстное меню, чтобы его убрать можно нажать ESC или кликнуть левой клавишей по Счетчику .

Перемещение Счетчика и изменение его размеров

Если навести курсор на выделенный Счетчик (курсор примет форму 4-х направленных в разные стороны стрелок), затем нажать и удерживать левую кнопку мыши, то можно переместить Счетчик . Удерживая клавишу ALT можно выровнять Счетчик по границам ячеек. Выделенный Счетчик также можно перемещать стрелками с клавиатуры.

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

Связываем Счетчик с ячейкой

Как было сказано выше, все Элементы управления формы возвращают значение. Это значение помещается в ячейку определенную пользователем. Чтобы связать Элемент управления с ячейкой, кликните на него ПРАВОЙ клавишей мыши, в появившемся контекстном меню выберите Формат объекта... Появится диалоговое окно, выберите вкладку Элемент управления (если такая вкладка отсутствует, то Вы вставили Элемент ActiveX, а не Элемент управления формы, об этом см. выше).

В поле Связь с ячейкой нужно ввести ссылку на ячейку. Свяжем наш Счетчик с ячейкой А1 .

Также установим минимальное значение =1, максимальное =101, шаг изменения =2.

Нажмите ОК.

Убедитесь, что Счетчик не выделен. Пощелкайте левой клавишей мыши по верхней кнопке Счетчика . В ячейке А1 значение будет увеличиваться в указанном диапазоне, причем с шагом 2 (1, 3, 5, ...), т.е. в ячейку будут вводиться только нечетные числа. При щелчке по нижней кнопке, значения будут уменьшаться.

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

Одну ячейку можно связать с несколькими элементами управления, но имеет ли это смысл? Решать Вам.

Примечание . Можно принудительно ввести в ячейку текстовое значение, но оно будет заменено при следующем нажатии Счетчика . Проведем эксперимент. Пусть в ячейке А1 введено число 5. Даже если Вы введете в ячейку А1 текст " строка ", то при следующем нажатии Счетчика , например вверх, в ячейке появится число 7 (если шаг =2), т.е. Счетчик хранит текущее значение не в ячейке, а где-то в себе.

Если, в нашем примере, Вы введете четное значение, то Счетчик не сбросит его, а будет прибавлять 2 и Вы получите четную последовательность 2, 4, 6, ... Но, при достижении верхней границы его поведение изменится 96, 98, 100, 101, т.к. максимальное значение установлено нами =101. Теперь при движении вниз Счетчик будет воспроизводить последовательность нечетных чисел! Тоже справедливо и для нижней границы: 6, 4, 2, 1, т.к. минимальное значение установлено =1.  Поэтому, следите, чтобы граничные значения (при шаге отличным от 1), содержались в требуемой последовательности, иначе при движении вверх и вниз Вы можете получить разные последовательности. Например, для последовательности 1, 4, 7, 10 (шаг 3) правильно установить границы 1 и 10. Если Вы установите границы 1 и 9, то при движении от 1 Вы получите последовательность 1, 4, 7, 9, затем при движении от 9 - получите 9, 6, 3, 1, т.е. 2 разные последовательности!

Использование Счетчика

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

Приведем 2 примера использования Счетчика.

1. График функции с параметром

Построим график функции y=5*x+а , где а - это параметр, который мы будем изменять с помощью Счетчика . При изменении параметра а график (прямая) будет смещаться вверх или вниз (см. файл примера лист Счетчик ).

При нажатии на Счетчик (верхняя клавиша), значение в связанной ячейке В7 будет увеличиваться, следовательно все значения в столбце у также будут увеличиваться, сдвигая график вверх.

2. Формирование отчета

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

Здесь Счетчик будем использовать, чтобы последовательно выбирать студентов. Строка с выбранным студентом подсвечивается Условным форматированием . Данные из таблицы в отчет будут попадать с помощью формулы =ВПР(D26;A29:B37;2)

Нажимая на Счетчик , отчет будет обновляться, а выбранный студент выделяться в таблице.

Имя Элемента управления

У каждого Элемента управления есть имя. Чтобы его узнать нужно выделить Счетчик , в Поле имя будет отображено его имя. Чтобы изменить имя Счетчика - в Поле имя введите новое имя и нажмите клавишу ENTER . Также имя можно изменить в Области выделения ( Главная / Редактирование/ Найти и выделить/ Область выделения ).

Зачем нам знать его имя? Если Вы не планируете управлять Счетчиком из программы VBA, то имя может потребоваться только для настройки его отображения на листе. Об этом читайте ниже.

Прячем Счетчик на листе

Включите Область выделения ( Главная / Редактирование/ Найти и выделить )

В Области выделения можно управлять отображением не только Элементов управления, но и других объектов на листе, например рисунков.

Нажмите на изображение глаза напротив имени объекта и объект исчезнет/ появится.

Расширяем возможности Счетчика

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

Чтобы иметь возможность изменять значение в ячейке с шагом 0,1 используйте формулу =A42/10 ( Счетчик связан с ячейкой А42 ).

Чтобы изменять значение в ячейке от -101 до -1, используйте формулу =-102+A50 (границы Счетчика установлены от 1 до 101, Счетчик связан с ячейкой А50 )

Шаг изменения счетчика можно сделать переменным, например, используя квадратичную зависимость (1, 4, 9, 16, ...) с помощью формулы =A58*A58 ( Счетчик связан с ячейкой А58 ).

Шаг изменения счетчика можно сделать различным в зависимости от диапазона, например, (в диапазоне от 1 до 11 шаг=1, от 11 до 101, шаг=10) с помощью формулы =ЕСЛИ(A66<11;A66;(A66-10)*10+1) (границы Счетчика установлены от 1 до 20, Счетчик связан с ячейкой А66 ).