Массовое создание ИД
ИД можно создавать массово на основе ваших таблицы и заголовков столбцов/строк. Выделите необходимый диапазон данных и выберите пункт меню Создать из выделенного . Будет предложен такой диалог:
Укажите в нём, где располагаются имена ваших ИД. Если вы укажете, что имена расположены в верхней строке, то Excel создаст ИД для каждой строки и назовёт их теми идентификаторами, которые в верхней строке располагаются. В предложенном примере мы имеем 5 столбцов и после нажатия OK можно проконтролировать, что создано 5 ИД с соответствующими именами:
Тоже самое можно проделать и со строками, если на то есть необходимость.
Ссылка на именованный диапазон
В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls.
В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls.
Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон.
В следующем примере показано, как можно написать эту же процедуру для активной книги.
Пример кода, предоставляемый: Деннис Валлентин(Dennis Wallentin), VSTO & .NET & Excel
В этом примере в качестве формулы для проверки данных используется именованный диапазон. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10.
Урок на тему «Относительные и абсолютные адреса в MS Excel»
воспитывающая – развивать у учащихся техническое мышление, внимание, память, профессиональный интерес. ТИП УРОКА: получение новых знаний и навыков, групповая форма работы
ТИП УРОКА: получение новых знаний и навыков, групповая форма работы
ВИД УРОКА: урок с элементами ролевой игры.
МПС: Программное обеспечение, машинопись.
МТО: Компьютеры, дидактический раздаточный материал.
I . ОРГАНИЗАЦИОННАЯ ЧАСТЬ (3 мин)
Проверка присутствия учащихся на уроке.
II . ВВОДНЫЙ ИНСТРУКТАЖ (10 мин)
1. Сообщить тему и цели урока.
Мы продолжаем изучать приложения MicroSoft. Кстати, какое уже нами изучено?
учащиеся — Microsoft Word .
— Что это за приложение?
учащиеся — Word — это текстовый процессор, организующий работу по созданию, форматированию, редактированию текстового файла, так же включает в себя редактор формул, графические средства оформления.
— Правильно. А для чего предназначен Excel и из чего он состоит?
учащиеся — Excel — это редактор электронных таблиц. Лист из строк и столбцов, что позволяет четко позиционировать текст, графику, математические формулы.
— Большие объемы информации для лучшего хранения и использования должны быть систематизированы, т.е. «разложены по полочкам», что и позволяет сделать Excel.
— А теперь записываем число и тему нашего урока. «Абсолютные и относительные ссылки«. Мы разберем их смысловое и функциональное отличие, способы записи, попрактикуемся в их использовании на страницах Excel.
2. Повторение правил ТБ при работе с ВТ. Включение компьютеров.
3. Объяснение нового материала:
Часто говорят: «Всё относительно!». И это правда. Вот, в данный момент вы сидите и относительно стула никуда не перемещаетесь, но относительно солнца — вы летите по кругу, да ещё вращаетесь вокруг оси вместе с планетой.Ваша группа выглядит как таблица Excel. Скажите что здесь относительное и что абсолютное.
(Учащиеся перечисляют объекты, людей, которые могут быть и относительными и абсолютными одновременно:
например, ссылка — это обращение. Ссылка на Анну Павловну, это абсолютная ссылка, а ссылка на мастера – относительная, ведь их несколько. Ссылка на имя ученика — это относительная ссылка, а имя и фамилия — (почти всегда) — абсолютная. Ссылка — слева, справа — относительна. Должна быть привязка в одному из желательно по двум свойствам к абсолютному объекту…)
Понятие адрес ячейки.
Электронная таблица Excel состоит из 65536 строк и 256 столбцов. Строки нумеруются целыми числами от 1 до 65536, а столбцы – латинскими буквами A , B , C ,…, AA , BB ,… .
На пересечении столбца и строки располагается структурный элемент таблицы – ячейка.
В любую ячейку таблицы можно ввести исходные данные: число или текст, а также формулы для расчета производной информации.
Для указания на конкретную ячейку используется адрес, который состоит из обозначения столбца и номера строки, на пересечении которых находится ячейка, он называется относительным Например А1, С4.
Понятие относительный адрес.
При относительной адресации адреса исходных ячеек определяются относительно адреса производной ячейки. А3, С4
Понятие абсолютный адрес.
При абсолютной адресации в качестве адресов исходных ячеек используется конкретное значение номеров строи и столбцов, которое указывается знаком $. Например $А$1 знак $ позволяет защитить ячейку при автозаполнении от её изменений.
Вы уже работали с таблицами, с вычислениями формул в них, и знаете что если большая таблица, а нужно для каждой строки или столбца выполнить одинаковые вычисления, то займет много времени. Программа Excel , создана специально для того, что бы облегчить нам работу. Для этого в Excel есть уже известная вам функция автозаполнение. Но если использовать автозаполнение в формулах где одно число нужно умножить или поделить на некоторое значение, находящееся вне таблицы с данными (Рис.1), то ответ будет не верным (Рис.2.), т.к. адрес является относительным и соответственно будет спускаться, т.е. умножаться на ячейку находящуюся ниже, а это не правильно (Рис.3). Пример:
Excel: Ссылки относительные и абсолютные
- Часто при использовании формул в Excel после ввода формулы в одну ячейку необходимо скопировать или распространить ее на блок ячеек.
- При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок.
- Ссылка в Excel — адрес ячейки или связного диапазона ячеек.
- Адрес ячейки определяется пересечением столбца и строки, например: A1, C16.
- Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
- Ссылки в Excel бывают 3-х типов:
- Относительные ссылки (пример: A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 или A$1).
Относительные ссылки
«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной.
Пример.
В ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше.
При копировании или «протаскивании» c помощью Маркера заполнения формулы, например, в ячейку А7 формула изменяется (Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с новым положением ячейки).
Теперь формула в ячейке А7 ссылается на ячейки С4 и С5. Названия ссылок изменились, но осталось неизменным их положение относительно ячейки, в которой находится формула (два столбца вправо и на три (С4) и две (С5) ячейки выше).
Относительные ссылки целесообразно использовать в формулах в двух случаях:
- Если формулу не предполагается копировать в другие ячейки.
- Если формулу необходимо скопировать в идентичные ячейки.
Абсолютные ссылки
Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка. Для этого перед символами ссылки устанавливаются символы «$» (формат записи $А$1).
Абсолютные ссылки в формулах используются в случаях:
- Необходимости применения в формулах констант.
- Необходимости фиксации диапазона для проведения расчетов.
Пример.
В диапазоне А1:А5 указаны зарплаты сотрудников отдела, а в С1 – процент премии, установленный для всего отдела. Подсчитаем премию каждого сотрудника и поместим в диапазоне В1:В5.
Для расчета премии первого сотрудника введем в ячейку В1 формулу =А1*С1.
Если мы с помощью Маркера заполнения протянем формулу вниз, то получим в ячейке В2 формулу =А2*С2, в ячейке В3 — =А3*С3 и т.д. Так как в ячейках диапазона С2:С5 нет значений, то в диапазоне В2 : В5 получаем нули. Для исправления ошибки, необходимо зафиксировать в формуле ссылку на ячейку С1, т.е. заменить относительную ссылку С1 на абсолютную $C$1.
Для этого:
- выделите ячейку В1
- в Строке формул поставьте знак «$» перед буквой столбца и адресом строки $С$1. Более быстрый способ — в Строке формул поставьте курсор на ссылку С1 (можно перед С, перед или после 1) и нажмите один раз клавишу «F4». Ссылка С1 выделится и превратится в $C$1.
- нажмите ENTER
Формула приняла вид « =А1*$С$1». Маркером заполнения протяните полученную формулу вниз.
Теперь диапазон В2: В5 заполнен значениями премий сотрудников.
Быстрый способ сделать относительную ссылку абсолютной — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знаки «$».
Семинары. Вебинары. Конференции
Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.
Использование ДВССЫЛ с другими функциями Excel
Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.
Пример 1. Функции ДВССЫЛ и СТРОКА
Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать ), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13
{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}
Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.
Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:
={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}
Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.
Вот пример:
={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}
А чтобы указать переменное количество значений, которое мы хотим сосчитать, можно это число вынести в отдельную ячейку. К примеру, в С1 запишем цифру 3. Тогда формулу можно изменить таким образом:
={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}
Согласитесь, что получается достаточно гибкий расчёт.
Пример 2. Функции ДВССЫЛ и АДРЕС
Вы можете использовать Excel ДВССЫЛ вместе с функцией АДРЕС, чтобы получить значение в определенной ячейке на лету.
Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.
Чтобы создать ссылку на ячейку, вы просто встраиваете функцию АДРЕС в формулу ДВССЫЛ, например:
=ДВССЫЛ(АДРЕС(1;3))
Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье .
И вот еще несколько примеров формул в которых используется функция ДВССЫЛ, и которые могут оказаться полезными:
- ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
- — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.
Виды представления ссылок
Есть два вида представления ссылок в Microsoft Excel:
Классический;
Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).
Включить стиль ссылок R1C1 можно в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»
Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=RC». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.
Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).
В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения в диапазоне B1:B10 на листе «Маркетинг» в той же самой книге.
Ссылка на другой лист в той же книге
Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек
Смешанные ссылки
Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.
Скопированная формула со смешанной ссылкой
Как создать таблицу в Word
Прежде чем запускать специально созданный для работы с таблицами редактор Excel с его непростыми, скажем прямо, таблицами, вспомним про то, что и в текстовом редакторе Microsoft Word тоже есть таблицы. Поэтому для начала нужно попробовать в Word.
Для представленного выше списка из 2-х учеников потребуется создать в редакторе MS Word таблицу из 3-х строк (учеников у нас в списке пока двое, а самую первую строку таблицы мы используем для заголовков столбцов), и из 5-и столбцов для ввода в них следующих данных:
- фамилия ученика
- имя ученика
- отчество ученика
- дата рождения ученика
- телефон ученика.
Приступаем. Откроем текстовый редактор Ворд. Введем в первой строке заголовок нашей таблицы, например, «Список учащихся 8В класса».
- Затем поставим курсор на вторую строку,
- откроем вкладку «Вставка» (1 на рис. 1),
- кликнем по иконке «Таблица» (2 на рис. 1) и
- выберем строку меню «Вставить таблицу» (3 на рис. 1).
Рис. 1 (кликните по рисунку для увеличения). Создание новой таблицы в редакторе Word.
В открывшемся служебном окне «Вставка таблицы»
- указываем «Число столбцов» 5 (1 на рис. 2) и
- «Число строк» 3 (2 на рис. 2) ,
- далее жмем на кнопку «ОК» (3 на рис. 2).
Рис. 2. Описание вставляемой таблицы (указание числа столбцов и строк) в редакторе Ворд.
Таблица успешно вставляется и имеет вид, как показано на рис. 3.
Рис. 3 (кликните по рисунку для увеличения). Вставленная пока еще пустая таблица из 3-х строк и 5-и столбцов в редакторе Microsoft Word.
После чего заполняем таблицу: заносим данные во все клеточки таблицы. В первую строку вводим заголовки таблицы, размещаем их по центру, и выделяем полужирным шрифтом.
В оставшиеся 2 строки таблицы заносим данные ученика по фамилии Иванов и ученицы по фамилии Петрова (рис. 4).
Рис. 4 (кликните по рисунку для увеличения). Заполненная таблица, сделанная в редакторе Ворд.
Теперь данные размещены в таблице. Это не только красивее, чем просто список, но еще и удобно. В первой колонке таблицы теперь стоят только фамилии учеников, во второй – имена, в третьей – отчества и так далее.
Как выделить и перенести часть таблицы в Ворде
Данные в таблице у нас теперь, как говорят, структурированы. А значит, их легко найти, выделить, куда-то перенести отдельно.
Скажем, нам нужны только фамилии учеников. Выделяем данные из первого столбца таблицы. Для этого помещаем курсор мышки в клетку, которая будет второй по вертикали в первой колонке таблицы – на фамилию «Иванов». Нажимаем на левую кнопку мышки и, не отпуская ее, перемещаем курсор мышки в третью строку таблицы. То есть как бы «проводим» мышкой по клеткам таблицы, где занесены фамилии учеников. Результат такого выделения показан на рис. 5.
Рис. 5 (кликните по рисунку для увеличения). Выделение одной колонки таблицы в редакторе MS Word.
Затем копируем эти данные в буфер обмена (клик мышкой на кнопку «Копировать» — 1 на рис. 6).
Рис. 6 (кликните по рисунку для увеличения). Копирование выделенной части таблицы в буфер обмена в редакторе Microsoft Word.
После копирования части таблицы в буфер обмена давайте установим курсор под таблицей не прямо на следующей строке, а через одну строку (выделено синим цветом – 1 на рис. 7), и кликнем по кнопке «Вставить» (2 на рис. 7).
Рис. 7 (кликните по рисунку для увеличения). Подготовка к вставке части таблицы в другое место текста в редакторе Word.
Часть таблицы, а именно – фамилии учеников – вставится ниже под основной таблицей (рис. 8).
Рис. 8 (кликните по рисунку для увеличения). Часть таблицы (фамилии учеников) воспроизведена в другом месте текста в редакторе Ворд.
Присвоение наименования
Присвоить наименование массиву или отдельной ячейке можно несколькими способами, как с помощью инструментов на ленте, так и используя контекстное меню. Оно должно соответствовать целому ряду требований:
- начинаться с буквы, с подчеркивания или со слеша, а не с цифры или другого символа;
- не содержать пробелов (вместо них можно использовать нижнее подчеркивание);
- не являться одновременно адресом ячейки или диапазона (то есть, названия типа «A1:B2» исключаются);
- иметь длину до 255 символов включительно;
- являться уникальным в данном документе (одни и те же буквы, написанные в верхнем и нижнем регистре, считаются идентичными).
Способ 1: строка имен
Проще и быстрее всего дать наименование ячейке или области, введя его в строку имен. Это поле расположено слева от строки формул.
- Выделяем ячейку или диапазон, над которым следует провести процедуру.
- В строку имен вписываем желаемое наименование области, учитывая правила написания названий. Жмем на кнопку Enter.
После этого название диапазону или ячейке будет присвоено. При их выделении оно отобразится в строке имен. Нужно отметить, что и при присвоении названий любым другим из тех способов, которые будут описаны ниже, наименование выделенного диапазона также будет отображаться в этой строке.
Способ 2: контекстное меню
Довольно распространенным способом присвоить наименование ячейкам является использование контекстного меню.
- Выделяем область, над которой желаем произвести операцию. Кликаем по ней правой кнопкой мыши. В появившемся контекстном меню выбираем пункт «Присвоить имя…».
- Открывается небольшое окошко. В поле «Имя» нужно вбить с клавиатуры желаемое наименование.
В поле «Область» указывается та область, в которой при ссылке на присвоенное название будет идентифицироваться именно выделенный диапазон ячеек. В её качестве может выступать, как книга в целом, так и её отдельные листы. В большинстве случаев рекомендуется оставить эту настройку по умолчанию. Таким образом, в качестве области ссылок будет выступать вся книга.
В поле «Примечание» можно указать любую заметку, характеризующую выделенный диапазон, но это не обязательный параметр.
В поле «Диапазон» указываются координаты области, которой мы даем имя. Автоматически сюда заносится адрес того диапазона, который был первоначально выделен.
После того, как все настройки указаны, жмем на кнопку «OK».
Название выбранному массиву присвоено.
Способ 3: присвоение названия с помощью кнопки на ленте
Также название диапазону можно присвоить с помощью специальной кнопки на ленте.
- Выделяем ячейку или диапазон, которым нужно дать наименование. Переходим во вкладку «Формулы». Кликаем по кнопке «Присвоить имя». Она расположена на ленте в блоке инструментов «Определенные имена».
- После этого открывается уже знакомое нам окошко присвоения названия. Все дальнейшие действия в точности повторяют те, которые применялись при выполнении данной операции первым способом.
Способ 4: Диспетчер имен
Название для ячейки можно создать и через Диспетчер имен.
- Находясь во вкладке «Формулы», кликаем по кнопке «Диспетчер имен», которая расположена на ленте в группе инструментов «Определенные имена».
- Открывается окно «Диспетчера имен…». Для добавления нового наименования области жмем на кнопку «Создать…».
- Открывается уже хорошо нам знакомое окно добавления имени. Наименование добавляем так же, как и в ранее описанных вариантах. Чтобы указать координаты объекта, ставим курсор в поле «Диапазон», а затем прямо на листе выделяем область, которую нужно назвать. После этого жмем на кнопку «OK».
На этом процедура закончена.
Но это не единственная возможность Диспетчера имен. Этот инструмент может не только создавать наименования, но и управлять или удалять их.
Для редактирования после открытия окна Диспетчера имен, выделяем нужную запись (если именованных областей в документе несколько) и жмем на кнопку «Изменить…».
После этого открывается все то же окно добавления названия, в котором можно изменить наименование области или адрес диапазона.
Для удаления записи выделяем элемент и жмем на кнопку «Удалить».
После этого открывается небольшое окошко, которое просит подтвердить удаление. Жмем на кнопку «OK».
Кроме того, в Диспетчере имен есть фильтр. Он предназначен для отбора записей и сортировки. Особенно этого удобно, когда именованных областей очень много.
Как видим, Эксель предлагает сразу несколько вариантов присвоения имени. Кроме выполнения процедуры через специальную строку, все из них предусматривают работу с окном создания названия. Кроме того, с помощью Диспетчера имен наименования можно редактировать и удалять.
Динамический именованный диапазон
До сих пор мы рассмотрели только те именованные диапазоны, которые не будут менять свою область.
Если мы создали B1:B20 диапазон, то он всегда будет ссылаться на эту область.
Грубо говоря, если у вас появятся новые ячейки в столбике, наш именованный диапазон не будет учитывать и включать в себя их. Это не очень удобно, что же делать?
В Excel можно создать и автоматически изменяемый именованный диапазон. Если я добавлю несколько новых ячеек в столбик, он будет включать в себя и их.
Для этого мы будем вызывать функцию ИНДЕКС.
Допустим у вас есть такая табличка:
Пошаговая инструкция:
Щелкните на «Формулы» -> «Присвоить имя»;
В открывшемся окне укажите данные на ваш выбор, а в поле «Диапазон» введите — =$A$2:ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»»&»»)
Подтвердите.
Готово!
Как это работает?
Чтобы понять как эта «фишка» работает, нужно знать как работает функция ИНДЕКС.
Чаще всего ИНДЕКС используют для получения значения из какой-либо ячейки.
Формула которую мы использовали при создании диапазона:
ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,»<>»&»») -> выдаст нам значение последнего элемента столбика.
Но если функция будет выглядеть так: (=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,»<>»&»»))) то она вернет порядковый номер последней ячейки, а не значение.
В нашем случае, это — $A$2:$A$11.
И соответственно если мы создадим новые ячейки — все будет пересчитано.
Обратите внимание:
Эта конструкция сработает только в том случае, если в ячейках идущих друг за другом не будет пустых. Если вам необходимо больший диапазон в функции ИНДЕКС — укажите его.
Также можно использовать функцию СМЕЩ, но она будет рассчитываться каждый раз, когда данные изменяются и может замедлять работу с программой.
Задача1 (Именованный диапазон с абсолютной адресацией)
Пусть необходимо найти объем продаж товаров (см. файл примера лист 1сезон ):
Присвоим Имя Продажи диапазону B2:B10 . При создании имени будем использовать абсолютную адресацию .
- выделите, диапазон B2:B10 на листе 1сезон ;
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Продажи ;
- в поле Область выберите лист 1сезон (имя будет работать только на этом листе) или оставьте значение Книга , чтобы имя было доступно на любом листе книги;
- убедитесь, что в поле Диапазон введена формула =’1сезон’!$B$2:$B$10
- нажмите ОК.
Теперь в любой ячейке листа 1сезон можно написать формулу в простом и наглядном виде: =СУММ(Продажи) . Будет выведена сумма значений из диапазона B2:B10 .
Также можно, например, подсчитать среднее значение продаж, записав =СРЗНАЧ(Продажи) .
Обратите внимание, что EXCEL при создании имени использовал абсолютную адресацию $B$1:$B$10. Абсолютная ссылка жестко фиксирует диапазон суммирования: в какой ячейке на листе Вы бы не написали формулу =СУММ(Продажи) – суммирование будет производиться по одному и тому же диапазону B1:B10
Иногда выгодно использовать не абсолютную, а относительную ссылку, об этом ниже.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Необходимо найти сумму продаж товаров в определенном месяце. Пользователь должен иметь возможность выбрать нужный ему месяц и получить итоговую сумму продаж. Выбор месяца пользователь должен осуществлять с помощью Выпадающего списка .
Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.
Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя >;
- в поле Имя введите: Месяц >;
- в поле Область выберите лист Книга >;
- в поле Диапазон введите формулу =СМЕЩ(лист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 .