Как сделать переключатель в excel?

Переключатель — элемент управления формы в MS EXCEL

Элементы Переключатель позволяют делать выбор значений из заранее заданного диапазона 1, 2, 3, . Этот элемент имеет много общего с Флажком.

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

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

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

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

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

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

Вставка Переключателя

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

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

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

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

От одного переключателя на листе нет никакого толка, т.к. он должен что-то переключать. Поэтому добавим еще 2 переключателя.

Выделение Переключателей

Чтобы выделить Переключатель нажмите и удерживайте клавишу CTRL, затем кликните левой клавишей на Переключатель. Если продолжать удерживать CTRL можно выделить все 3 Переключателя.

Выравнивание Переключателей

Выровнять Переключатели по левому краю, выделите их, затем нажмите кнопку меню Выровнять по левому краю ( Формат/ Упорядочить/ Выровнять ).

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

Опять выделите Переключатели и нажмите кнопку меню Распределить по вертикали ( Формат/ Упорядочить/ Выровнять ).

При желании Переключатели можно сгруппировать (выделите их, нажмите на них правой клавишей мыши, в контекстном меню выберите Группировать/ Группировать ).

Перемещение Переключателей и изменение их размеров

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

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

Связываем Переключатель с ячейкой

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

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

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

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

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

Чтобы организовать 2 независимых группы Переключателей, необходимо использовать Элемент управления формы — Группа. Сначала разместите на листе две Группы, затем поместите на них требуемое количество Переключателей, затем свяжите по одному из Переключателей из каждой группы с разными ячейками на листе (см. лист Группа в файле примера ).

Читать еще:  Как сделать ссылку на значение в excel на другой лист?

Примечание: Использовать только 2 Переключателя на листе не имеет смысла — их функциональность эквивалентна элементу Флажок, а затрат на их создание в 2 раза больше. Флажок возвращает ИСТИНА или ЛОЖЬ, а 2 Переключателя — 1 или 2. Хотя, это, безусловно, дело вкуса.

Использование Переключателей

Переключатели часто используются для выбора опций или параметров вычислений. Например, при планировании планов продаж можно рассчитать объемы продаж по трем сценариям «Базовый», «Умеренный», «Оптимистичный», каждый из которых предусматривает свой рост продаж +5%, +10%, +20%.

Переключатели связаны с ячейкой А1. Значения в ячейке (от 1 до 3) преобразуются в 5%, 10%, 20% с помощью формулы =ВЫБОР($A$1;5%;10%;20%) в ячейке Е7.

Перерасчет планов продаж ведется с помощью формулы =B11*(1+$E$7)

Для выделения выбранного сценария в диапазоне С11:С16 использовано Условное форматирование.

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

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

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

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

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

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

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

Добавление флажков и переключателей (элементы управления формы)

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

Чтобы добавить флажок или переключатель, вам понадобится вкладка Разработчик на ленте.

Примечания: Чтобы добавить вкладку «Разработчик», выполните следующие действия:

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

В Excel 2007: нажмите кнопку Microsoft Office и выберите Параметры Excel > Популярные > Показывать вкладку «Разработчик» на ленте.

Чтобы добавить флажок, откройте вкладку Разработчик, нажмите кнопку Вставить и в разделе Элементы управления формы щелкните .

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

Выберите ячейку, в которой нужно добавить флажок или переключатель.

Совет: За один раз можно добавить только один флажок или переключатель. Чтобы ускорить работу, выделите созданный элемент управления и выберите команды Копировать > Вставить.

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

Совет: Если виден не весь текст, щелкните и перетаскивайте один из маркеров, пока не будет виден весь текст. Размер элемента управления и его расстояние от текста изменить нельзя.

Форматирование элемента управления

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

Примечание: Размер переключателя внутри элемента управления и его расстояние от текста невозможно изменить.

Чтобы отформатировать элемент управления, щелкните его правой кнопкой мыши и выберите пункт Формат элемента управления.

В диалоговом окне Формат элемента управления на вкладке Элемент управления можно изменить следующие параметры:

установлен: отображает выбранный переключатель.

снят: отображает невыбранный переключатель.

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

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

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

Например, на форме сотрудника в группе Тип занятости есть два переключателя (Полная и Частичная), связанные с ячейкой C1. Когда пользователь выбирает один из них, формула в ячейке D1 использует значение «Полная», если установлен первый переключатель, или «Частичная», если выбран второй переключатель.

Если нужно оценить более двух параметров в одной группе, для этого можно использовать функции ВЫБОР или ПРОСМОТР.

Нажмите кнопку ОК.

Удаление элемента управления

Щелкните элемент управления правой кнопкой мыши и нажмите клавишу DELETE.

В настоящее время элементы управления «флажок» нельзя использовать в Excel в Интернете. Если вы работаете в Excel в Интернете и открыли книгу с флажками или другими элементами управления (объектами), вы не сможете редактировать книгу, не удаляя эти элементы управления.

Читать еще:  Как сделать диаграмму в excel 2013?

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

Если у вас есть классическое приложение Excel, нажмите кнопку Открыть в Excel и добавьте флажки илипереключатель.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Функция ПЕРЕКЛЮЧ для создания переключателя значений в Excel

Функция ПЕРЕКЛЮЧ предназначена для логического сравнения значений в Excel принимает на вход логическое выражение и список возможных значений, с которыми сравнивается результат выполнения логического выражения, и возвращает значение, соответствующее первому найденному совпадению в списке.

Как сделать переключатель в Excel

Наряду с функцией ЕСЛИМН, функция ПЕРЕКЛЮЧ была введена для упрощения вычисления формул, содержащих большое число вложенных функций ЕСЛИ.

  1. Данная функция доступна пользователям MS Office версии 2016 и более новых.
  2. Для тех, кто знаком с языками программирования, для лучшего понимания рассматриваемой функции можно использовать ее аналогию с конструкцией switch – case, используемой, например в Java или C#.

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

Вид таблицы данных:

Реализуем задачу с использованием рассматриваемой функции:

Значение, переданное в качестве первого аргумента (ссылка на ячейку A2 со списком), последовательно сравнивается с последующими аргументами (значение1, значение2…). В случае совпадения будет выведен соответствующий результат.

Выберем число 3 в списке. Полученное значение:

В результат получился переключатель между текстовыми значениями указанных в аргументах функции. По принципу действия сильно напоминает функцию ВЫБОР, но в следующем примере рассмотрим в чем преимущества функции ПЕРЕКЛЮЧ.

Создание переключателя между формулами в Excel

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

Для быстрого переключения между операциями создадим список.

Вид таблицы данных:

Для быстрого вывода результатов вычислений в столбец C используем следующую формулу массива:

Для проверки выберем значение «Умножить» из списка. Полученные результаты:

Правила использования функции ПЕРЕКЛЮЧ в Excel

Рассматриваемая функция имеет следующую синтаксическую запись:

=ПЕРЕКЛЮЧ( выражение; значение1; результат1; [по_умолчанию или значение2; результат2]; …[по_умолчанию или значение3; результат3])

  • выражение – обязательный, принимает ссылку на ячейку с проверяемыми данными или логическое выражение, результат вычисления которого будет сравниваться с последующими значениями;
  • значение1 – обязательный, принимает ссылку на ячейку или значение, с которым будут сравниваться данные, принятые в качестве первого аргумента;
  • результат1 – обязательный, принимает любое значение или ссылку на ячейку, содержащую выводимый результат, если данные, переданные в качестве аргументов выражение и значение1 совпали;
  • [по_умолчанию_или_значение2] – не обязательный, принимает либо данные, сравниваемые с выражение или значение, которое будет выводиться по умолчанию, если необходимо выполнить сравнение только с одним значением;
  • [результат2] – необязательный, принимает значение или ссылку на ячейку с данными, которые будут возвращены, если значения выражение и [по_умолчанию_или_значение2] совпали. Последующие аргументы имеют тот же смысл.
  1. Максимальное число аргументов, принимаемых функцией ПЕРКЛЮЧ, составляет 254. То есть, можно выполнить проверку по списку из 126 значений/результатов.
  2. Если рассматриваемая функция содержит только 3 аргумента, то есть значение по умолчанию явно не задано, в случае если проверяемые значения не совпадают, функция вернет код ошибки #Н/Д. Например, =ПЕРЕКЛЮЧ(3*2;7;”семь”) вернет #Н/Д, поскольку 3*2=6, то есть не совпадает с 7, а значение по умолчанию не введено в виде следующего аргумента.

Создание кнопки в Microsoft Excel

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

Процедура создания

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

Способ 1: автофигура

Прежде всего, рассмотрим, как создать кнопку из набора встроенных фигур Excel.

  1. Производим перемещение во вкладку «Вставка». Щелкаем по значку «Фигуры», который размещен на ленте в блоке инструментов «Иллюстрации». Раскрывается список всевозможных фигур. Выбираем ту фигуру, которая, как вы считаете, подойдет более всего на роль кнопки. Например, такой фигурой может быть прямоугольник со сглаженными углами.
Читать еще:  Как сделать битый файл excel?

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

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

Теперь при клике по созданному нами объекту будет осуществляться перемещение на выбранный лист документа.

Способ 2: стороннее изображение

В качестве кнопки можно также использовать сторонний рисунок.

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

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

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

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

  • Открывается окно управление макросами. В нем нужно выделить тот макрос, который вы желаете применять при нажатии кнопки. Этот макрос должен быть уже записан в книге. Следует выделить его наименование и нажать на кнопку «OK».
  • Теперь при нажатии на объект будет запускаться выбранный макрос.

    Способ 3: элемент ActiveX

    Наиболее функциональной кнопку получится создать в том случае, если за её первооснову брать элемент ActiveX. Посмотрим, как это делается на практике.

      Для того чтобы иметь возможность работать с элементами ActiveX, прежде всего, нужно активировать вкладку разработчика. Дело в том, что по умолчанию она отключена. Поэтому, если вы её до сих пор ещё не включили, то переходите во вкладку «Файл», а затем перемещайтесь в раздел «Параметры».

    В активировавшемся окне параметров перемещаемся в раздел «Настройка ленты». В правой части окна устанавливаем галочку около пункта «Разработчик», если она отсутствует. Далее выполняем щелчок по кнопке «OK» в нижней части окна. Теперь вкладка разработчика будет активирована в вашей версии Excel.

    После этого перемещаемся во вкладку «Разработчик». Щелкаем по кнопке «Вставить», расположенной на ленте в блоке инструментов «Элементы управления». В группе «Элементы ActiveX» кликаем по самому первому элементу, который имеет вид кнопки.

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

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

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

    Способ 4: элементы управления формы

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

      Переходим во вкладку «Разработчик» и кликаем по знакомой нам кнопке «Вставить», размещенной на ленте в группе «Элементы управления». Открывается список. В нем нужно выбрать первый же элемент, который размещен в группе «Элементы управления формы». Данный объект визуально выглядит точно так же, как и аналогичный элемент ActiveX, о котором мы говорили чуть выше.

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

  • После этого назначаем для созданного объекта макрос, как это было показано в Способе 2 или присваиваем гиперссылку, как было описано в Способе 1.
  • Как видим, в Экселе создать функциональную кнопку не так сложно, как это может показаться неопытному пользователю. К тому же данную процедуру можно выполнить с помощью четырех различных способов на свое усмотрение.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Ссылка на основную публикацию
    Adblock
    detector