Присвоение и использование имен — Мегаобучалка
1) Выделите ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя.
2) Кликните в поле имени, которое расположено слева в строке формул:
3) Введите имя ячеек.
4) Нажмите клавишу ENTER.
Либо: Выделить группу ячеек и воспользоваться пунктом меню Вставка/Имя/Присвоить. В этом же пункте меню можно удалить ошибочно присвоенное имя. НЕОБХОДИМО ПОМНИТЬ: ссылка по имени является абсолютной.
Присваивание ячейкам имени при помощи существующих заголовков строк и столбцов
1) Выделите область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
2) Выберите команду Имя в меню Вставка, а затем — команду Создать.
3) Установите флажки в строке выше, в столбце слева, в строке ниже или в столбце справа в группе флажков По тексту, чтобы указать расположение заголовков, из которых следует создать имена.
Созданное таким образом имя ссылается только на ячейки, содержащие значения, и не включает в себя существующих заголовков строк и столбцов.
4. На листе 2 переименовав его в «Итог 1 кв» создайте таблицу вида:
.
Выполните автозаполнение ячеек С2 и D 2 последующими месяцами.
Скопируйте лист «График дежурств 1кв» книги «ЛПЗ-3 Иванов ИИ»: щелкните правой кнопкой мыши по имени листа и выберите в раскрывающемся контекстном меню Переместить/скопировать…
В появившемся окне укажите в какую книгу вы хотите скопировать лист (при этом обе книги должны быть открыты), укажите перед каким листом поместить – в нашем случае в конец документа) и не забудьте поставить галочку Создать копию. Нажмите ОК .
По умолчанию имя скопированного листа будет Лист 2. Переименуйте его в «Итог 1 кв».
Дайте имена диапазону D 2: D 32 листа «Итог 1 кв» – январь, D 33: D 60 – февраль; D 61: D 91 – март.
В прографке таблицы проведите заполнение формулами в целях подсчета итоговых значений по месяцам числа рабочих дней каждым из сторожей по следующим формулам: =СЧЁТЕСЛИ(диапазон;критерий), =СУММ(диапазон).
После ввода формул в ячейки выполните автозаполнение формулами строчки по Аксенову и Гришко.
В результате получите таблицу вида:
Сохраните книгу и закройте.
Что нужно знать и уметь.
1) Знать приемы правила создания формул, присвоения имен и понятия «абсолютная» и «относительная ссылки».
2) Уметь составлять формулы с использованием функций.
Занятие 5. Создание и форматирование диаграмм и графиков
Цель. Научиться создавать диаграммы в MS Excel.
Задачи. Изучить и научиться пользоваться инструментами меню Вставка Диаграмма.
Содержание занятия
1. Изучение приемов создания диаграмм в MS Excel.
2. Создание диаграмм и графиков.
Задание.
1. MS Excel обладает возможностями создания на основе данных книги диаграмм и графиков благодаря инструменту меню Вставка /Диаграмма.
Для создания диаграммы необходимо запустить мастер диаграмм либо уже указанным способом (в меню Вставка), либо воспользоваться пиктограммой на панели инструментов Стандартная Мастер диаграмм . В результате запустится окно «Мастер диаграмм». На шаге первом у Вас есть возможность выбрать тип диаграммы: Стандартный или Нестандартный.
На шаге втором указывается источник данных для построения диаграммы.
Во вкладке «Диапазон данных» необходимо указать диапазон, содержащий данные и выставить переключатель по строкам или по столбцам содержатся ли у Вас данные в таблице данных, на которую ссылаетесь.
Во вкладке Ряд Вы имеете возможность создавать отдельно ряды, присваивая им имена, значения, а также подписи оси Х (если это поле оставить без изменений, то по умолчанию подписи будет 1,2, 3 и т.
д.).
Если у Вас есть таблицы с данными, то возможно внести данные в поля мастера диаграмм ссылаясь на ячейки рабочего листа.
После ввода исходных данных и нажатия кнопки Далее на третьем шаге мастера диаграмм появляется окно, позволяющее указать все параметры будущей диаграммы: название диаграммы, ее осей, настроить подписи данных, вывести таблицу данных, настроить линии сетки, настройки отображения легенды.
На следующем (4 шаге) выбирается размещение диаграммы: на имеющемся или новом листе.
2. Откройте Вашу папку и создайте документ ЛПЗ 5-ИвановИИ (замените фамилию и инициалы на Ваши). Заполните свойства документа соответствующими данными.
Удалите все листы книги кроме первого
Скопируйте лист 2 из книги ЛПЗ 4-Иванов ИИ и переименуйте его в «Итог 1 кв».
Создайте на основе данных таблицы листа «Итог 1 кв» на отдельном листе диаграмму вида:
.
Откройте лист «Подсчет» книги ЛПЗ 3-ИвановИИ и удалите из него выходные дни (если этого вы ещё не сделали). Скопируйте имена работников на лист 1 книги ЛПЗ 5-ИвановИИ, а в ячейке В1 введите дату 11.01.2010:
.
Заполните даты вправо до 31.12.2010 года только рабочие дни (использовать инструмент Прогрессия)
Прографку заполнить данными из таблицы на странице «Подсчет» книги ЛПЗ 3-ИвановИИ.
По составленной таблице построить диаграмму на отдельном листе следующего вида:
Сохраните книги и закройте.
31)Имена в формулах. Присвоение имен диапазонам. Создание имен с помощью заголовков. Применение имен.
Имена и заголовки.
Для
упрощения просмотра и запоминания
формул одной или нескольким ячейкам
можно присвоить имя. Если в листе
присутствуют заголовки строк и столбцов,
то на них можно сослаться в формуле или
использовать как имена, связанные с
ячейками.
Кроме того, можно создать имя,
которое представляет одинаковые ячейки
или группу ячеек на нескольких листах.
Использование заголовков в формулах.
Использование в формуле ссылок на данные листа при помощи заголовков строк и столбцов делает ее более наглядной. Например, если таблица содержит количество проданной продукции в столбце “Продажа”, а отдел, выполнивший торговую операцию, находится в строке “Отдел”, то формула “=(Продажа Отдел)” будет задавать количество проданной продукции заданным отделом. Между названием столбца и строки обязательно должен идти пробел, а всю ссылку лучше взять в скобки.
Осторожно! При использовании одного заголовка два раза могут возникнуть проблемы.
Присвоение и использование имен.
Выделите ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя.
Укажите
поле имени, которое расположено слева
в строке формул.
Введите имя ячеек.
Нажмите клавишу ENTER.
Либо: Выделить группу ячеек и воспользоваться пунктом Меню=>Вставка=>Имя=>Присвоить.
В этом же пункте меню можно удалить ошибочно присвоенное имя.
Ссылка по имени является абсолютной.
Например, в некоторой ячейке записан курс доллара. Назовем эту ячейку “Бакс”. Предположим, что в таблице столбце “Стоимость” идут стоимости фруктов в долларах от “Ананас” до “Яблоко”. Чтобы посчитать сумму в рублях, надо использовать формулу “=сумм((Стоимость Ананас):(Стоимость Яблоко))*Бакс”.Присваивание ячейкам имени при помощи существующих заголовков строк и столбцов
Выделите область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
Выберите
команду Имя в меню Вставка, а затем —
команду Создать.
Установите флажки В строке выше, В столбце слева, В строке ниже или В столбце справа в группе флажков По тексту, чтобы указать расположение заголовков, из которых следует создать имена.
Современные
технологии обработки информации часто
приводят к тому, что возникает необходимость
представления данных в виде таблиц. В
языках программирования для такого
представления служат двухмерные массивы.
Для табличных расчетов характерны
относительно простые формулы, по которым
производятся вычисления, и большие
объемы исходных данных. Такого рода
расчеты принято относить к разряду
рутинных работ, для их выполнения следует
использовать компьютер. Для этих целей
созданы электронные таблицы (табличные
процессоры) – прикладное программное
обеспечение общего назначения,
предназначенное для обработки различных
данных, представимых в табличной форме.
Электронная таблица позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным средством моделирования различных вариантов и ситуаций. Меняя значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый.
Идея создания электронной таблицы возникла у студента Гарвардского университета Дэна Бриклина в 1979г. Выполняя скучные вычисления экономического характера с помощью бухгалтерской книги, он и его друг Роберт Фрэнкстон, который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc («Визикалк»).
VisiCalc
скоро стала одной из наиболее успешных
программ.
Новый
существенный шаг в развитии электронных
таблиц – появление в 1982г. на рынке
программных средств Lotus
1–2–3. Lotus
был первым табличным процессором,
интегрировавшим в своем составе, помимо
обычных инструментов, графику и
возможность работы с системами управления
базами данных. После разработки Lotus
1–2–3 компания Lotus
в первый же год повышает свой объем
продаж до 50 млн. дол. и становится самой
большой независимой компанией –
производителем программных средств.
Успех компании Lotus
привел к ужесточению конкуренции,
вызванной появлением на рынке новых
электронных таблиц, таких, как VP
Planner
компании Paperback
Software
и Quattro
Pro
компании Borland
International,
которые предложили пользователю
практически тот же набор инструментария,
но по значительно более низким ценам.
Следующий шаг – появление в 1987г. табличного процессора Excel фирмы Microsoft. Эта программа предложила более простой графический интерфейс в комбинации с ниспадающими меню, значительно расширив при этом функциональные возможности пакета и повысив качество выходной информации. Расширение спектра функциональных возможностей электронной таблицы, как правило, ведет к усложнению работы с программой.
В 1993 году вышла пятая версия Excel, ставшая первым приложением Microsoft Office, которое включало язык Visual Basic for Applications (VBA). Начиная с Office 97 фирма Microsoft включает VBA во все приложения пакета Microsoft Office.
В
настоящее время MS
Excel
представляет собой достаточно мощное
средство разработки информационных
систем, которое включает как электронные
таблицы (со средствами финансового и
статистического анализа, набором
стандартных математических функций,
доступных в компьютерных языках высокого
уровня, рядом дополнительных функций,
встречающихся только в библиотеках
дорогостоящих инженерных подпрограмм),
так и средства визуального программирования
(Visual
Basic
for
Applications).
Используйте диспетчер имен в Excel
Используйте диалоговое окно Name Manager для работы со всеми определенными именами и именами таблиц в книге. Например, вам может понадобиться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или отредактировать описательные комментарии или определить область действия.
Вы также можете сортировать и фильтровать список имен, а также легко добавлять, изменять или удалять имена из одного места.
Чтобы открыть диалоговое окно Name Manager , на Вкладка Формулы в группе Определенные имена
В диалоговом окне Name Manager отображается следующая информация о каждом имени в списке:
Имя столбца | Описание |
|---|---|
Имя | Один из следующих:
|
Значение | Текущее значение имени, например результат формулы, строковая константа, диапазон ячеек, ошибка, массив значений или заполнитель, если формулу невозможно вычислить.
|
Относится к | Текущая ссылка на имя. |
Объем |
|
Комментарий | Дополнительная информация об имени до 255 символов. Ниже приведены типичные примеры:
|
Относится к: | Ссылка для выбранного имени. Вы можете быстро изменить диапазон имени, изменив детали в поле Относится к . После внесения изменений вы можете нажать Подтвердить , чтобы сохранить изменения, или нажать Отменить , чтобы отменить изменения. |
Ниже приведены репрезентативные примеры:
org/ListItem»>Примечания:
org/ListItem»>Вы не можете использовать диалоговое окно Диспетчер имен во время изменения содержимого ячейки.
В диалоговом окне Name Manager не отображаются имена, определенные в Visual Basic для приложений (VBA), или скрытые имена (свойство Visible имени имеет значение False).
На вкладке Формулы в группе Определенные имена щелкните Определить имя .
В диалоговом окне Новое имя в поле Имя введите имя, которое вы хотите использовать для справки.
Примечание. Имя может содержать до 255 символов.
Область действия автоматически устанавливается по умолчанию на Workbook . Чтобы изменить область действия имени, в раскрывающемся списке Scope выберите имя рабочего листа.
При необходимости в поле Комментарий введите описательный комментарий длиной до 255 символов.
В поле Относится к выполните одно из следующих действий:
Щелкните Свернуть диалоговое окно (что временно свернет диалоговое окно), выберите ячейки на листе и нажмите Развернуть диалоговое окно .
Чтобы ввести константу, введите = (знак равенства), а затем введите значение константы.
Чтобы ввести формулу, введите = и введите формулу.
Советы:
Будьте осторожны при использовании абсолютных или относительных ссылок в формуле. Если вы создадите ссылку, щелкнув ячейку, на которую хотите сослаться, Excel создаст абсолютную ссылку, например «Лист1!$B$1». Если вы вводите ссылку, например «B1», это относительная ссылка. Если при определении имени вашей активной ячейкой является A1, то ссылка на «B1» действительно означает «ячейка в следующем столбце». Если вы используете определенное имя в формуле в ячейке, ссылка будет на ячейку в следующем столбце относительно того места, где вы вводите формулу.
Например, если вы введете формулу в C10, ссылка будет D10, а не B1.Дополнительная информация – переключение между относительными, абсолютными и смешанными ссылками
Чтобы закончить и вернуться к рабочему листу, нажмите OK .
Примечание: Чтобы сделать Новое имя Диалоговое окно шире или длиннее, щелкните и перетащите ручку захвата внизу.
Если изменить определенное имя или имя таблицы, все варианты использования этого имени в рабочей книге также будут изменены.
org/ListItem»>На вкладке Формулы в группе Определенные имена щелкните Диспетчер имен .
В Диспетчере имен , дважды щелкните имя, которое хотите изменить, или щелкните имя, которое хотите изменить, а затем щелкните Изменить .
В диалоговом окне Edit Name в поле Name введите новое имя для ссылки.
В поле Относится к измените ссылку и нажмите OK .
В диалоговом окне Name Manager в поле Относится к измените ячейку, формулу или константу, представленную именем.
На вкладке Формулы в группе Определенные имена щелкните Диспетчер имен .
В диалоговом окне Name Manager щелкните имя, которое хотите изменить.
Выберите одно или несколько имен, выполнив одно из следующих действий:
Чтобы выбрать имя, щелкните его.
org/ListItem»>Чтобы выбрать более одного имени в непрерывной группе, щелкните и перетащите имена или нажмите SHIFT и щелкните кнопкой мыши для каждого имени в группе.
Чтобы выбрать более одного имени в несмежной группе, нажмите клавишу CTRL и щелкните кнопкой мыши для каждого имени в группе.
Нажмите Удалить .
Нажмите OK , чтобы подтвердить удаление.
Используйте команды из раскрывающегося списка Фильтр для быстрого отображения подмножества имен.
Выбор каждой команды включает или выключает операцию фильтрации, что позволяет легко комбинировать или удалять различные операции фильтрации для получения желаемых результатов.
Можно фильтровать по следующим параметрам:
Выберите | от до |
|---|---|
Имена ограничены рабочим листом | Отображать только те имена, которые являются локальными для рабочего листа. |
Имена в рабочей книге | Отображать только те имена, которые являются глобальными для книги. |
Имена с ошибками | Отображать только те имена, значения которых содержат ошибки (например, #ССЫЛКА, #ЗНАЧ или #ИМЯ). |
Имена без ошибок | Отображать только те имена, значения которых не содержат ошибок. |
Определенные имена | Отображать только имена, определенные вами или программой Excel, например область печати. |
Имена таблиц | Отображать только имена таблиц. |
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
См. также
Почему я вижу диалоговое окно «Конфликт имен» в Excel?
Создать именованный диапазон в Excel
Вставить именованный диапазон в формулу в Excel
Определение и использование имен в формулах
Как удалить именованный диапазон в Excel? 2 простых способа!
Именованные диапазоны могут быть чрезвычайно полезны при работе с большим количеством данных и формул.
Именованные диапазоны позволяют быстро дать имя ячейке или диапазону ячеек, чтобы вместо ссылки использовать эти имена в формулах.
И когда вы пристрастились к использованию именованных диапазонов, есть вероятность, что вы в конечном итоге создадите много именованных диапазонов и захотите удалить некоторые или все из них.
В этом коротком уроке я покажу вам два простых способа удалить именованные диапазоны в Excel . Вы можете удалить все именованные диапазоны за один раз или выбрать вручную или отфильтровать их, а затем удалить.
Я также покажу вам, как удалять именованные ячейки и диапазоны с помощью VBA.
Итак, приступим!
В этом руководстве рассматриваются:
Удаление именованных диапазонов с помощью диспетчера имен
В Excel есть диспетчер имен , в котором вы можете управлять всеми именованными диапазонами (создавать, редактировать или удалять).
Итак, если вы хотите удалить некоторые или все именованные диапазоны из своей книги, вы можете сделать это с помощью диспетчера имен.
Ниже приведены шаги по удалению именованных диапазонов с помощью диспетчера имен:
- Щелкните вкладку «Формула» на ленте
- В группе «Определенные имена» щелкните «Диспетчер имен».
Откроется диалоговое окно «Диспетчер имен», в котором перечислены все именованные диапазоны в книге
- Выберите тот, который вы хотите удалить
- Нажмите кнопку «Удалить».
Вышеуказанные шаги удалят выбранный именованный диапазон из книги.
Если вы хотите удалить сразу несколько именованных диапазонов, вы можете сделать это, выбрав те, которые вы хотите удалить, и затем нажав кнопку Удалить.
Чтобы выбрать несколько именованных диапазонов, удерживайте клавишу Control, а затем выберите именованные диапазоны один за другим. Если вы хотите выбрать блок именованных диапазонов за один раз, выберите первый, удерживайте клавишу SHIFT, а затем выберите последний. Это выберет все именованные диапазоны между ними, и вы сможете удалить их все за один раз.
Фильтрация именованных диапазонов
По мере того, как вы становитесь более опытными в Excel и начинаете регулярно использовать именованные диапазоны, существует вероятность того, что в вашей книге будет много именованных ячеек и диапазонов.
Несмотря на то, что диспетчер имен упрощает работу с этими именованными диапазонами, он все же может стать довольно громоздким, когда у вас их много.
Диспетчер имен позволяет быстро фильтровать именованные диапазоны на основе следующих критериев:
- Имена, относящиеся к рабочему листу
- Имена в пределах рабочей книги
- Имена с ошибками
- Имена без ошибок
- Определенные имена
- Имена таблиц
диалоговое окно «Диспетчер имен»)
Удаление именованных диапазонов с помощью VBA
Хотя использование диспетчера имен является эффективным способом удаления именованных диапазонов, если вам нужно делать это довольно часто, вы можете использовать простой код VBA для удаления всех именованных диапазонов или именованные диапазоны, содержащие определенную текстовую строку.
Ниже приведен код VBA, который удалит все именованные диапазоны из всей книги.
'Код Сумита Бансала с https://trumpexcel.com Sub УдалитьИмена() Dim RName как имя Для каждого RName в Application.ActiveWorkbook.Names RName.Удалить Следующий End Sub
Приведенный выше код использует цикл For Next для последовательного прохождения всех именованных диапазонов и их удаления. После запуска этого кода VBA в вашей книге не будет именованных диапазонов.
Вы также можете немного изменить этот код, чтобы удалить только те именованные диапазоны, которые содержат определенное слово.
Например, допустим, я хочу удалить только те имена, которые содержат слово «продажи» (в любом месте имени).
Вы можете сделать это, используя приведенный ниже код VBA:
'Код Sumit Bansal с https://trumpexcel.com Sub УдалитьИмена() Dim RName как имя Для каждого RName в Application.ActiveWorkbook.Names Если InStr(1, RName.Name, "продажи", vbTextCompare) > 0, то RName.Delete Следующий End Sub
Приведенный выше код снова перебирает каждое имя в рабочей книге, а затем проверяет, содержит ли имя слово sales или нет.
Это делается с помощью функции VBA INSTR вместе с условием IF Then. Будут удалены только те имена, где есть слово «продажи», а все остальные будут проигнорированы.
Теперь большой вопрос — как вы используете этот код?
Ниже приведены шаги для использования этого кода в файле Excel:
- Перейдите на вкладку «Разработчик», а затем щелкните Visual Basic (или используйте сочетание клавиш ALT + F11). Это откроет редактор Visual Basic
- : Выберите в меню пункт «Вставить», а затем нажмите «Модуль». Это вставит новый модуль для текущей рабочей книги
- Скопируйте и вставьте приведенный выше код VBA в окно кода модуля
- Чтобы выполнить код макроса, щелкните значок «Выполнить» на панели инструментов (или поместите курсор в любое место в код и используйте сочетание клавиш F5)
Вышеуказанные шаги запустят код VBA и удалит именованные диапазоны из вашей книги.
Обратите внимание, что изменение кода VBA необратимо.