Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.
Именная ячейка C7
Имя ограничено 255 символами, что более чем достаточно. Имя также не должно содержать пробелов, поэтому, если оно состоит из нескольких слов, можно разделить их символом подчеркивания.
Если теперь мы хотим вывести это значение на другие листы книги или использовать его в дальнейших расчетах, нет необходимости переходить на первый лист и вручную указывать ячейку. Просто введите имя ячейки, и ее значение будет заменено.
Использование имени ячейки
Ячейки, строки, столбцы
Начнем с выделения ячеек, строк и столбцов.
- Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.
- Чтобы выбрать столбец C, щелкните по заголовку столбца C.
- Чтобы выбрать строку 3, щелкните по заголовку строки 3.
Примеры диапазона
Диапазон — это набор из двух или более ячеек.
- Чтобы выделить диапазон B2:C4, щелкните в правом нижнем углу ячейки B2 и перетащите указатель мыши в ячейку C4.
- Чтобы выделить диапазон, состоящий из отдельных (не смежных) ячеек, удерживая нажатой клавишу Ctrl, щелкните каждую ячейку, которую вы хотите включить в диапазон.
Заполнение диапазона
Следуйте приведенным ниже инструкциям, чтобы заполнить диапазон:
- В ячейку B2 введите значение 2.
- Выделите ячейку B2, зажмите ее правый нижний угол и перетащите вниз к ячейке B8.
Результат:
Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
- Введите значение 2 в ячейку B2 и значение 4 в ячейку B3.
- Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.
Excel автоматически заполнит диапазон на основе формулы из первых двух значений. Довольно круто, правда? Вот еще один пример:
- Введите дату 13/6/2013 в ячейку B2 и дату 16/6/2013 в ячейку B3 (на рисунке показан американский эквивалент этих дат).
- Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.
Именованный диапазон
Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (1) и ввести его имя в поле имени (2):
Создание именованного диапазона
Затем это имя можно использовать в формулах, например, для вычисления суммы:
Использование именованного диапазона в формуле
Именованный диапазон также можно создать на вкладке Формулы, выбрав инструмент Задать имя.
Создание именованного диапазона с помощью панели инструментов
Появится диалоговое окно, в котором нужно ввести имя диапазона, выбрать область, к которой будет применяться имя (т.е. ко всей книге или к отдельным листам), при необходимости заполнить примечание, а затем выбрать соответствующий диапазон на листе.
Создание имени с помощью диалогового окна
Для работы с существующими диапазонами на вкладке Формулы есть Менеджер имен.
Именной менеджер
Используйте этот инструмент для удаления, изменения или добавления новых имен к ячейкам или диапазонам.
Управление именованными диапазонами
Однако важно понимать, что если вы используете именованные диапазоны в формулах, удаление имени такого диапазона приведет к ошибкам.
Задача
У вас есть таблица продаж некоторых товаров по месяцам (см. файл-образец ):
Задача состоит в том, чтобы найти общий объем продаж продукции в данном месяце. Пользователь должен иметь возможность выбрать месяц и получить общую сумму продаж. Пользователь должен выбрать месяц, используя выпадающий список.
Чтобы решить эту проблему, нам нужно создать два динамических диапазона: один для выпадающего списка, содержащего месяцы, а другой для диапазона суммы.
Для генерации динамических диапазонов мы будем использовать функцию HUMMING(), которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Вы можете указать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создайте динамический диапазон для выпадающего списка, содержащего месяцы. С одной стороны, необходимо учесть, что пользователь может добавить продажи за месяцы после апреля (май, июнь…), с другой стороны, выпадающий список не должен содержать пустых строк. Динамический диапазон как раз и является решением этой проблемы.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Месяц ;
- В поле Область выберите Книжный лист ;
- В поле Range введите формулу =MEMBERSHIP(sheet1!$B$5;;;1;ACCOUNT(sheet1!$B$5:$I$5)).
- Нажмите OK.
Теперь подробнее. Любой диапазон в EXCEL определяется координатами верхней левой и нижней правой ячеек диапазона. Начальной ячейкой, от которой рассчитывается положение нашего динамического диапазона, является ячейка B5 . Если аргументы offset_by_rows, offset_by_columns не заданы (как в нашем случае), то эта ячейка является верхней левой ячейкой диапазона. Правая нижняя ячейка диапазона задается аргументами height и width . В нашем случае значение высоты =1, а значение ширины диапазона равно результату расчета формулы SCHOTZ(sheet1!$B$5:$I$5), который равен 4 (строка 5 содержит 4 месяца с января по апрель). Итак, адрес правой нижней ячейки нашего динамического диапазона определен — это E 5 .
Когда вы заполните таблицу данными о продажах за май, июнь и так далее, формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и таким образом определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.
ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек! Т.е. вы должны заполнить список месяцами без пробелов.
Теперь создадим еще один динамический диапазон для подведения итогов продаж.
Для создания динамического диапазона :
- На вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Продажи_в_месяц;
- В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
- нажмите OK.
Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Левый верхний угол нашего динамического диапазона (начиная с ячейки A6) перемещается на это количество столбцов, высота диапазона остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества товаров в диапазоне).
И, наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж в выбранном месяце.
Например, в мае месяце.
Или, например, в апреле месяце.
Примечание: Вместо формулы SMUM() можно использовать формулу INDEX() : = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5)) для расчета количества завершенных месяцев.
Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ), таким образом возвращая ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Текущий динамический диапазон можно выделить с помощью условного форматирования . В файле примера правило условного форматирования применяется к ячейкам диапазона B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяц)
Условное форматирование автоматически выделило серым цветом продажи текущего месяца, который был выбран в выпадающем списке.
Пример 2. Определите количество столбцов в таблице и введите это значение в ячейку A16.
Таблица:
Мы используем формулу OVERALL, выбирая в качестве параметра поочередно каждый столбец ячейки. Нажмите и удерживайте клавишу Ctrl перед выбором следующего столбца. Если вы добавите «)» и нажмите Enter, появится диалоговое окно, указывающее на то, что вы ввели слишком много аргументов. Добавьте дополнительные открывающие и закрывающие скобки.
Результат расчета:
Определение принадлежности ячейки к диапазону таблицы
Пример 3 Определяет, принадлежит ли данная ячейка заданному диапазону ячеек.
Рассмотренная здесь функция также позволяет определить, принадлежит ли ячейка выбранному диапазону. Процедура выполняется следующим образом:
-
- Введите часть формулы «=WORLD(()» в любую ячейку и выделите любой диапазон ячеек для заполнения аргументов:
-
- Поставьте пробел и выберите любую ячейку в этом диапазоне:
-
- Закройте обе скобки и нажмите Enter. Результат будет следующим:
-
- Если вы выберете ячейку из диапазона, отличного от указанного, вы получите ошибку #empty!
Эта ошибка указывает на то, что ячейка не принадлежит выбранному диапазону.
Если вы выделите более одной ячейки в диапазоне, функция VARIABLE вернет количество выделенных ячеек:
Описанные возможности этой функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
См. также = IF(ANSWER(A2);A2;B2) вы копируете макрос «Фильтр» соответствующим образом…. Это будет работать только в таблице: Я думаю, что это возможно, если колонна. Т.е. получается, что ВСЕ», затем выполните и нажмите кнопку Вставить более сложную процедуру, щелкните значок Вставить на следующих действиях.Вставить, вы можете выбрать для временного отображения данных, выбранный раздел наПримечание: вставить как выходные значения таблицы с необходимостью?
The_Prist скопируйте выделенный диапазон, который вы выбрали для вышеуказанных действийCtrl+Paste. Чтобы переместить ячейки, щелкните мышью. Вставьте варианты, которые не требуют другого рабочего листа или Попробуйте как можно чаще
Диапазон ячеек
Группа соседних
ячеек, образующих в таблице область
прямоугольной формы, называется
диапазоном.
Диапазон ячеек обозначается адресами
ячеек, расположенных в противоположных
углах прямоугольника, и разделенных
двоеточием (A1:A5). Для выделения
прямоугольного диапазона нужно щелкнуть
по первой ячейке диапазона и удерживая
кнопку мыши протянуть указатель мыши
от одной угловой ячейки до противоположной
(последней в диапазоне) по диагонали,
отпустить кнопку мыши. При этом рамка
текущей ячейки расширяется и охватывает
весь выбранный диапазон. Первая ячейка
выделенного диапазона остается белой,
а остальные выделяются цветом; при
выделении следующего диапазона первая
выделенная ячейка остается белой,
остальные выделяются цветом, а в
предыдущем диапазоне все ячейки
выделяются цветом.
Для выделения
строки или столбца целиком, следует
щелкнуть на заголовке строки или столбца
соответственно. Для выделения нескольких
диапазонов одновременно надо выделить
один диапазон, а затем прижать клавишу
CTRL
и выделить все остальные диапазоны.
Задание 1.3: Выделение диапазонов.
-
Установить
указатель мыши в виде белого креста на
первую ячейку выделяемого диапазона
А1. -
Нажать левую
кнопку мыши и перетащить указатель
мыши на последнюю ячейку выделяемого
диапазона Н5. Выделен диапазон А1:Н5. -
Снять выделение
щелчком в любом свободном от выделения
месте таблицы. -
Выделить столбец
или строку щелчком по заголовку столбца
или строки соответственно. -
Выделить несколько
расположенных рядом столбцов или строк,
щелчком по заголовку первого объекта
и протаскиванием указателя для выделения
других столбцов или строк. -
Выделить весь
рабочий лист щелчком на сером
прямоугольнике на пересечении заголовка
строк и столбцов. -
Выделить несколько
диапазонов: выделить первый диапазон,
нажать клавишу Ctrl, и, не отпуская ее
выделить следующий диапазон. Выделите
группу диапазонов A1:A3, B3:D6, F1:F65536 (столбец
F), A8:A256 (строка 8).
Маркер заполнения
Небольшой черный
квадрат в правом нижнем углу активной
ячейки или диапазона называется маркером
заполнения. Попав на маркер заполнения,
указатель мыши принимает вид черного
креста.
Активная
ячейка Диапазон ячеек
В выделенном
диапазоне ячеек имеется только один
маркер заполнения.
Вводить данные
можно только в активную (выделенную)
ячейку. В Excel используются данные
следующих типов: текст, число, дата и
время, формула, функция. Тип данных
определяется автоматически при вводе.
Тип данных можно переопределять.
Текстовая
информация
Текст — это комбинация
букв, цифр и пробелов. Для ввода данных
необходимо выбрать ячейку и начать
ввод. В ячейке отобразится курсор (точка
вставки). В строке
формул также
отобразится содержимое ячейки, при этом
включатся кнопки
.
Ввод данных можно осуществлять
непосредственно в строке формул. Для
завершения ввода необходимо нажать
клавиши Enter или Tab или кнопку в виде
зеленой галочки в строке формул. По
умолчанию текст в ячейке выравнивается
по левому краю. Отменяет ввод клавиша
Esc на клавиатуре или кнопка в виде
красного крестика в строке формул. В
стандартных ячейках таблицы размещается
8-9 символов. При вводе более длинного
текста, онвизуально
располагается и на соседних ячейках.
Если один раз щелкнуть на текущей ячейке
и снова ввести данные, то старые данные
замещаются новыми. Если дважды щелкнуть
по текущей ячейке или выделить ячейку
и один раз щелкнуть по строке формул,
то можно редактировать содержимое, не
удаляя уже введенные данные.
Короткий текст
можно отредактировать заменой, объемные
данные удобнее редактировать в строке
формул.
Числовая информация
Числа — это цифры
от 0 до 9 и символы «+»,
«—»,
«/»,
«.»,
«,»,
«%»,
«$»,
«(«,
«)».
Для ввода отрицательного числа надо
поставить перед ним знак «-» или взять
число в скобки, для ввода дроби надо
ввести целую часть, пробел и дробную
часть. Целая часть десятичной дроби
отделяется от ее дробной части запятой.
По умолчанию числа в ячейке выравниваются
по правому краю.
Пример.
-7, (7), 0 2/5, 2 1/3. Если после ввода числа в
ячейке появляется последовательность
символов
,
это означает, что для отображения числа
не хватает ширины столбца и ее надо
увеличить.
Ширину столбца
можно отрегулировать вручную
или командой Формат/Столбец
/Автоподбор_ширины.
Ввод и редактирование числовой информации
аналогичны таким же операциям для
текстовой информации.
Задание 2.1: Ввод
и редактирование текста и числовых
данных.
1. Ввести на первом
рабочем листе книги следующие данные,
начиная с ячейки А1. Завершать ввод
данных клавишей Enter.
2. Сравнить действия
при нажатии клавиш Enter (рамка активной
ячейки перемещается вниз) и Tab (рамка
перемещается вправо).
3. Числа в первом
столбце ввести с использованием маркера
заполнения:
|
|
— |
|
|
— |
|
|
— |
|
|
— |
|
|
— |
4. При необходимости
внести исправления следующим образом:
a.
выделить ячейку, дважды щелкнуть, в
ячейке появится курсор, можно вносить
исправления;
b.
выделить ячейку, щелкнуть по строке
формул, в ней появится курсор, править
в строке формул. (Для удаления символов
использовать клавиши Del или BackSpасе).
5. Отрегулировать
ширину столбцов вручную:
a.
подвести указатель мыши к правой границе
заголовка столбца, он примет вид
двунаправленной стрелки, протащить
край заголовка вправо до установления
нужной ширины, отпустить кнопку мыши;
b.
дважды щелкнуть на правой границе
заголовка столбца — ширина будет
установлена автоматически по самой
длинной записи. (Возврат вверх
или к разделу Работа
с таблицами).
6. Сохранить файл
с названием Ведомость.xls в папке на диске
D: студенты (имя папки может быть изменено)
командой меню Файл/Сохранить.
В диалоговом окне «Сохранение документа»
выбрать свою рабочую папку (ь),
указать имя файла (ь)
и уточнить тип файла (должно быть
установлено по умолчанию — Книга Excel).
Дата и время
В Microsoft Excel даты и
время суток интерпретируются как числа.
При вводе значений даты или времени
происходит их автоматическое распознавание.
Для корректного ввода данных необходимо
использовать коды форматов. Например,
для ввода дат можно использовать
следующие шаблоны:
|
Вид |
Шаблон |
|
Месяцев |
M |
|
Месяцев |
ММ |
|
Месяцев |
МММ |
|
Месяцев |
ММММ |
|
Месяцев |
МММММ |
|
Дней |
Д |
|
Дней |
ДД |
|
Лет |
ГГ |
|
Лет |
ГГГГ |
День, Месяц и Год
вводятся цифрами или буквами. При вводе
данных цифрами в качестве разделителя
используется точка, пробелы не допустимы.
Если месяц вводится буквенным обозначением,
то в качестве разделителя используется
пробел. При вводе времени в качестве
разделителя используется «:», Часы,
Минуты и Секунды вводятся одной или
двумя цифрами. По умолчанию значения
даты и времени выравниваются в ячейке
по правому краю. В строке формул дата
отображается в формате дд.мм.гг,
а время — в формате чч:мм:сс.
Если ввод не корректен, то введенные
значения интерпретируются как текст,
который выравнивается в ячейке по левому
краю. Если после ввода даты и (или) времени
в ячейке появляется последовательность
символов, то надо увеличить ширину
столбца.
Пример.
Можно вводить даты так: 1.1.01, 07.05.01, 01 сен
2001. Можно вводить время так: 1:10, 13:10:57,
8:1:2.
Соседние файлы в папке ИНФ-КА для ИСТ-11з
- #
- #
- #
- #
- #
Ячейка в Excel — это прямоугольная область рабочего листа, которая образуется при пересечении одного столбца и одной строки. Если две ячейки имеют одну общую сторону, то ячейки называют смежными. Соответственно, если две ячейки общих сторон не имеют, то их называют несмежными. Кроме этого в Excel различают диапазон ячеек и группу ячеек.
В свою очередь диапазоны ячеек также могут быть как смежными, так и несмежными. Смежный диапазон ячеек в Excel — это совокупность ячеек, представляющая собой прямоугольную область, полученную при пересечении нескольких столбцов и нескольких строк, идущих подряд. Два или более диапазонов, не примыкающих друг к другу, не имеющие общих сторон, являются несмежными. Такие диапазоны также называют несвязанными. Ячейки, входящие в несмежные диапазоны образуют группу ячеек.
Что такое используемый диапазон ячеек в Excel?
Диапазон рабочего листа, все ячейки которого находятся между первой заполненной и последней заполненной ячейкой, называется используемым диапазоном. Другими словами использыемый диапазон — это область ячеек, используемая пользователем для хранения информации (значений, форматов и так далее).
Что такое именованный диапазон ячеек в Excel?
Именованный диапазон — это ячека, либо диапазон ячеек, которому присвоено имя. Имя — краткое, осмысленное обозначение. Имена ячеек и диапазонов могут использоваться при создании формул вместо адресов ячеек, а также при выделении нужных диапазонов. Присвоить имя диапазону можно различными способами, самый простой из которых — выделить нужную ячейку или диапазон и в адресном окошке строки формул написать имя для именованного диапазона.
По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.
Для имен действует ряд ограничений:
— имя может содержать до 255 символов;
— первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;
— имена не могут быть такими же, как ссылки на ячейки;
— пробелы в именах не допускаются;
— строчные и прописные буквы не различаются.
Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке «Формулы», в группе кнопок «Определенные имена».
Основные действия с диапазонами
Выделение диапазонов
О том как выделять ячейки и группы ячеек уже рассказывалось в одной из наших публикаций. Также ранее рассматривалась тема о том как выделять строки в рабочих листах Excel, но строка является одним из частных видов диапазона ячеек. Рассмотрим несколько способов выделения диапазонов ячеек в общем виде.
Способ первый: для выделения небольшого диапазона ячеек, находящихся рядом, можно просто провести по ним курсор мыши в виде широкого белого креста при нажатой левой кнопке мыши. Первая ячейка диапазона при этом останется неподсвеченной и готовой к вводу информации.
Способ второй: для выделения большого диапазона ячеек необходимо щелкнуть по первой ячейке диапазона, после чего при нажатой клавише Shift, щелкнуть по последней ячейке диапазона. При этом можно использовать горячие клавиши для перехода в начало или конец строки, а также для перехода в начало или конец рабочего листа.
Способ третий: для выделения диапазона можно просто написать адрес этого диапазона в адресном окошке строки формул, а если диапазон именованный, то достаточно в адресном окошке написать его имя.
Сравнение диапазонов
Сравнение диапазонов — это одна из классических задач в Excel, которую рано или поздно приходится решать любому пользователю Excel. Задача по сравнению диапазонов может быть поставлена по разному. Когда-то нужно найти различия или совпадения в диапазонах при построчном их сравнении, а когда-то необходимо узнать есть ли что-то общее в сравниваемых диапазонах вообще. В зависимости от поставленной задачи различаются и методики её решения.
Например, для построчного сравнения часто используется логическая функция «ЕСЛИ» и какой-либо из операторов сравнения (также можно использовать и другие функции, например «СЧЕТЕСЛИ» из категории статистические для проверки вхождения элементов одного списка в другой).
Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке «Главная», в группе кнопок «Редактирование», в меню кнопки «Найти и выделить». Если в этом меню выбрать пункт «Перейти» и далее нажать кнопку «Выделить», то в диалоговом окне «Выделение группы ячеек» можно выбрать одну из опций «Отличия по строкам» или «Отличия по столбцам».
Для поиска повторяющихся или уникальных значений в двух диапазонах можно использовать условное форматирование.
Сравнение диапазонов можно провести и при помощи надстройки для Excel, которая позволяет находить и подсвечивать заливкой различия или совпадения в двух заданных диапазонах.
Изменение (преобразование) диапазонов значений
Одним из способов преобразования диапазона значений является транспонирование. Транспонирование — это такое преобразование диапазона значений, при котором данные, расположенные построчно перемещаются в столбцы и наоборот с сохранением порядка, то есть первая строка становится первым столбцом, вторая строка — вторым столбцом и так далее.
Транспонирование можно осуществить при помощи функции «=ТРАНСП(Диапазон)», которая находится в категории «Ссылки и массивы». Есть и другой способ — копирование диапазона значений с последующей специальной вставкой, при которой ставится флажок в поле «Транспонировать».
Есть еще одна возможность изменения выбранного диапазона значений. При помощи надстройки для Excel можно значения исходного диапазона разложить по заданному количеству строк либо столбцов.
А знаете ли Вы, что можно быстро собрать диапазоны значений с разных листов на итоговый лист?
Надстройка для работы с диапазонами в Excel
Кнопки надстройки расположены на отдельной вкладке ленты Excel, каждая кнопка вызывает пользовательскую форму (диалоговое окно), при помощи которой процедурам VBA задаются начальные условия. Надстройка позволяет осуществлять различные действия с диапазонами ячеек, такие как: копирование диапазонов из определенных листов различных рабочих книг и вставка их на итоговый лист, сравнение двух диапазонов, вставка заданного диапазона в определенные листы различных рабочих книг, производить математические действия между значениями ячеек диапазона и заданным числом, изменение размеров диапазона, применение текстовых функций к текстовым значениям ячеек диапазона, копирование и вставка диапазонов ячеек со всех листов книги на итоговый лист.
скачать надстройку для работы с диапазонами