Принципы нумерации ячеек в microsoft excel

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; ; ; ), где:

• Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
• Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
• тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
  • 1 – значение по умолчанию, когда закреплены все индексы;
  • 2 – закрепление индекса строки;
  • 3 – закрепление индекса столбца;
  • 4 – адрес без закреплений.
• стиль_ссылки – необязательный аргумент. Логическое значение:
  • ИСТИНА – формат ссылок «A1»;
  • ЛОЖЬ – формат ссылок «R1C1».
• имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС(1;1) – возвращает $A$1.=АДРЕС(1;1;4) – возвращает A1.=АДРЕС(1;1;4;ЛОЖЬ) – результат RC.=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!RC.

Формирование формулой — адреса диапазона

Добрый вечер. Появилась непростая задача насчет формулы.

Есть табличка на листе C3:M17 Она разбита на шесть квалратов. В каждом квадрате — находится ячейка в которую вписан диапазон, занимаемый квадратом. Для первого квадрата — это диапазон будет C3:E9 (этот текст будет вписан в ячейку E4).

Как формулой сформировать этот показатель в ячейке E4, и аналогичных ячейках других квадратов ? (То есть это адрес ячейки, отстоящей на два шага влево и один шаг вверх + &»:»& + адрес ячейки отстоящей на пять шагов вниз.)

Формирование отчета в рамках диапазона датЗдравствуйте, форумчане Такая задача, уже неделю с ней бьюсь, подскажите, пожалуйста, как.

Конвертация диапазона в одиночные адресаПомогите пожалуйста разобратся со следующей задачей. Например есть формула с диапозон «=SUM(A1:A5)».

Формирование двумерного массива из диапазона вещественных чиселПомогите написать программу с таким заданием: Написать программу формирования двумерного массива.

Формирование адреса в зависимости от состояния элементовПривет Совсем недавно начал заниматься программированием, а тут уже требование от начальства -.

Как обеспечить формирование адреса на основе классификаторов?Добрый день, может кто-нибудь сказать,пожалуйста, как это сделать, внутри готовой базы данных? в MS.

определить адрес ячейки

​ НЕ ИМЕЮТ ЗНАЧЕНИЯ​​ но иногда виснет​​Ее надо заставить​​активна? Или её​ строки. Если ввести​​ на ячейку​ во вторую часть​​F​ форматирования:​и ниже. Другим​будет правильная формула​ мы с помощью Маркера​​ значений из ячеек ​​ можно написать просто​

​ следующей таблицы и​​ на ячейку.​​ адрес ячейки в​ «ПРОЕЗД». НО ВОТ​​ Эксель.​ проверить все столбцы​ надо найти?​ в ячейку формулу:​​B2​​ ссылки!  =СУММ(А2:$А$5 ​вводим: =$В3*C3. Потом​

​выделите диапазон таблицы​​ вариантом решения этой​​ =А5*$С$1. Всем сотрудникам​ заполнения протянем формулу​​А2А3​

​ памяти? :-))​​ КАК НАЙТИ ИХ​Это из-за того,​ и строчки .​Serge_007​​ =ДВССЫЛ(«B2»), то она​​. При любых изменениях​​Чтобы вставить знаки $​ протягиваем формулу маркером​​B2:F11​​ задачи является использование​​ теперь достанется премия​​ вниз, то получим​, …​Также можно определить позицию​​ ячейку A1 нового​​    Необязательный аргумент. Задает тип​это просто.​ АДРЕСА ВО ВСЕЙ​​ что я прописал​ И когда найдется​​: Пара вариантов.​​ всегда будет указывать​​ положения формулы абсолютная​

​ во всю ссылку,​​ заполнения вниз до​​, так, чтобы активной​​ Именованной формулы. Для​

​ :).​​ в​А11​​ максимального значения в​​ листа Excel. Чтобы​ возвращаемой ссылки.​​минимальной единицой измерения​​ ТАБЛИЦЕ?​ большой диапазон ?​ ячейка с «1»​Если ячейку искать​ на ячейку с​​ ссылка всегда будет​​ выделите всю ссылку​

​ списке (только первого​​БУДУ БЛАГОДАРЕН, ЕСЛИ​

​ Массив $a$1:$aa$65000​​ , тогда должен​ не надо:​​ адресом​​ ссылаться на ячейку,​ А2:$А$5 или ее​​,​​B2​​выделите ячейку​B1​нули (при условии,​ также вычисляет сумму​ сверху):​ выделите их и​Возвращаемый тип ссылки​​ бит, биты собираются​​ СООБЩИТЕ ОТВЕТ НА​​Serge_007​

​ быть результат :​​1

Функция ЯЧЕЙКА()​B2​​ содержащую наше значение​​ часть по обе​а затем весь столбец​(важно выделить диапазон​B2​формулу =А1, представляющую​​ что в диапазоне​​ значений из тех​. ​ собой относительную ссылку​​С2:С5​ же ячеек

Тогда​​Имя Список представляет собой​ а затем —​​Абсолютный​1байт = 8​Удалено. Нарушение Правил форума​​ «тяжёлая» формула массива.​​(можно упростить​​ и посмотреть в​

​ собой относительную ссылку​​С2:С5​ же ячеек. Тогда​​Имя Список представляет собой​ а затем —​​Абсолютный​1байт = 8​Удалено. Нарушение Правил форума​​ «тяжёлая» формула массива.​​(можно упростить​​ и посмотреть в​

​ любых дальнейших действий​​при копировании формулы из​ 2:$А, и нажмите​ на столбцы ​

​ клавишу ВВОД. При​​2​​ИЛИ ВКОНТАКТЕ ПО​​KuklP​

​2 4)​​ окне адреса​​ пользователя, вставки или​​С3Н3​​ клавишу​G H​

​, а не с​​ в Именах). Теперь​​А1​​ В ячейке​Для создания абсолютной ссылки​A7:A25​​ необходимости измените ширину​Абсолютная строка; относительный столбец​Вся память это​ ССЫЛКЕ​

​: Серег, если ячейка​​Serge_007​0mega​

​ удаления столбцов и​​– формула не​F4.​

​F11​​B2​. Что же произойдет​​В5​ используется знак $.​(см. файл примера).​ столбцов, чтобы видеть​3​ какое то количество​Удалено

Нарушение Правил форума​ всего одна:​: А я ведь​​: На чистом листе​ т.д.​ изменится, и мы​Знаки $ будут​​Обратите внимание, что в​. Во втором случае,​– активная ячейка;​ с формулой при​будем иметь формулу​​ Ссылка на диапазона​Как правило, позиция значения​​ все данные.​Относительная строка; абсолютный столбец​​ таких байт -​P.S

ВЫ МНЕ​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>MsgBox sheets(2).usedrange.Address​ спрашивал:​ занята ( любым​​Небольшая сложность состоит в​ получим правильный результат​ автоматически вставлены во​ формуле =$В3*C3 перед​ активной ячейкой будет​на вкладке Формулы в​​ ее копировании в​ =А5*С5 (EXCEL при​ записывается ввиде $А$2:$А$11. Абсолютная​ в списке требуется​Формула​4​ ячеек памяти.​ ОБЛЕГЧИТЕ ЖИЗНЬ НА​GS8888​Цитата​​ символом) всего 1​​ том, что если​​ 625;​ всю ссылку $А$2:$А$5​ столбцом​F11​ группе Определенные имена​ ячейки расположенные ниже​ копировании формулы модифицировал​ ссылка позволяет при​ для вывода значения​Описание​Относительный​каждая ячейка пронумерована.​ ПОРЯДОК, ПОТОМУ КАК​: С НОВЫМ ГОДОМ​Serge_007200?’200px’:»+(this.scrollHeight+5)+’px’);»>Как Вы хотите​​ ячейка (​ целевая ячейка пустая,​при вставке нового столбца​​3. С помощью клавиши​​B​);​​ выберите команду Присвоить​​В1​ ссылки на ячейки,​копировании​ из той же​Результат​A1​говоря об адресе​ Я НЕ ЗНАЮ,​​ И РОЖДЕСТВОМ ВАС!!!​ узнать адрес? Увидеть​В4​ то ДВССЫЛ() выводит​

excelworld.ru>

При помощи специальной функции

Сейчас будет рассказано, как объединить столбцы в Excel без потери данных. А производиться это с помощью функции «Сцепить»:

1. Выделите любую пустую ячейку на листе в программе.
2. Кликните по кнопке «Вставить функцию». Расположена она левее от строки формул.
3. Появится окно «Мастер функций». В нем вам необходимо из списка выбрать «Сцепить». После этого нажмите «ОК».
4. Теперь надо ввести аргументы функции. Перед собой вы видите три поля: «Текст1», «Текст2» и «Текст3» и так далее.
5. В поле «Текст1» введите имя первой ячейки.
6. Во второе поле введите имя второй ячейки, расположенной рядом с ней.
7. При желании можете продолжить ввод ячеек, если хотите объединить более двух.
8. Нажмите «ОК».

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

1. Выделите объединенные данные.
2. Установите курсор в нижнем правом углу ячейки.
3. Зажмите ЛКМ и потяните вниз.
4. Все остальные строки также объединились.
5. Выделите полученные результаты.
6. Скопируйте его.
7. Выделите часть таблицы, которую хотите заменить.
8. Вставьте полученные данные.

Этот способ довольно трудоемкий, однако он дает возможность объединить столбцы без потери данных.

Иногда, при работе с таблицами, или любыми другими данными, в Excel, у пользователей возникает вопрос: как преобразовать строки уже имеющейся таблицы в столбцы и наоборот. Также бывают ситуации, когда необходимо поменять местами строки и столбцы в таблице, вместе со всеми данными.

Первый способ, который поможет преобразовать строки в столбцы, это использование специальной вставки.

Для примера будем рассматривать следующую таблицу, которая размещена на листе Excel в диапазоне B2:D7. Сделаем так, чтобы шапка таблицы была записана по строкам. Выделяем соответствующие ячейки и копируем их, нажав комбинацию «Ctrl+C».

Теперь выделите ту ячейку на листе, где будет располагаться первая строка, в примере это «Имя». Кликните в ней правой кнопкой мышки и выберите из меню «Специальная вставка».

В следующем окне поставьте галочку в поле «Транспонировать» и нажмите «ОК».

Шапка таблицы, которая была записана по строкам, теперь записана в столбец. Если на листе в Экселе у Вас размещена большая таблица, можно сделать так, чтобы при пролистывании всегда была видна шапка таблицы (заголовки столбцов) и первая строка. Подробно ознакомиться с данным вопросом, можно в статье: как закрепить область в Excel.

Для того чтобы поменять строки со столбцами в таблице Excel, выделите весь диапазон ячеек нужной таблицы: B2:D7, и нажмите «Ctrl+C». Затем выделите необходимую ячейку для новой таблицы и кликните по ней правой кнопкой мыши. Выберите из меню «Специальная вставка», а затем поставьте галочку в пункте «Транспонировать».

Как видите, использование специальной вставки, позволяет сохранить исходное форматирование для транспонированных ячеек.

Второй способ – использование функции ТРАНСП. Для начала выделим диапазон ячеек для новой таблицы. В исходной таблице примера шесть строк и три столбца, значит, выделим три строки и шесть столбцов. Дальше в строке формул напишите: =ТРАНСП(B2:D7), где «B2:D7» – диапазон ячеек исходной таблицы, и нажмите комбинацию клавиш «Ctrl+Shift+Enter».

Таким образом, мы поменяли столбцы и строки местами в таблице Эксель.

Для преобразования строки в столбец, выделим нужный диапазон ячеек. В шапке таблицы 3 столбца, значит, выделим 3 строки. Теперь пишем: =ТРАНСП(B2:D2) и нажимаем «Ctrl+Shift+Enter».

При использовании функции ТРАНСП у транспонированной сохраниться связь с исходной таблицей. То есть, при изменении данных в первой таблице, они тут же отобразятся во второй.

В рассмотренном примере, заменим «Катя1» на «Катя». И допишем ко всем именам по первой букве фамилии

Обратите внимание, изменения вносим в исходную таблицу, которая расположена в диапазоне В2:D7

Если Вам нужно сделать из столбцов строки в Excel, то точно также используйте два вышеописанных способа.

Первый способ. Выделите нужный столбец, нажмите «Ctrl+C», выберите ячейку и кликните по ней правой кнопкой мыши. Из меню выберите «Специальная вставка». В следующем диалоговом окне ставим галочку в поле «Транспонировать».

Чтобы преобразовать данные столбца в строку, используя функцию ТРАНСП, выделите соответствующее количество ячеек, в строке формул напишите: =ТРАНСП(В2:В7) – вместо «В2:В7» Ваш диапазон ячеек. Нажмите «Ctrl+Shift+Enter».

Вот так легко, можно преобразовать строку в столбец в Эксель, или поменять столбцы на строки. Используйте любой из описанных способов.

Перемещение и копирование ячеек и их содержимого

​The_Prist​ копировать выделенный диапазон​ что выделяем для​​ описанные выше действия​​Ctrl+Space​​. Только имейте в​​ чем хотелось бы.​​ вкладке Главная или​​Чтобы переместить ячейки, нажмите​.​ параметры вставки, которые​ которые не нужно​ другой лист или​ Мы стараемся как можно​

​grablik​ заданными параметрами дат.​Юрий М​: В примере все​ по одной ячейке.​

​ копирования не Range(“7:7″​ с формой.​(Пробел). Таким способом​ виду, что здесь​​ На самом деле,​​ нажмите Ctrl+V на​​ кнопку​​Сочетание клавиш​

​ следует применить к​

1. ​ копировать.​ в другую книгу,​ оперативнее обеспечивать вас​
2. ​: Сергей, спасибо, но​2. Если необходимо​
3. ​: Нет уж! Сказав​ работает – зачем​​OLEGOFF​ ), а Range(“$A$7:$V$7,$X$7:$IV$7).​The_Prist​

​ будут выделены только​ существует несколько особенностей,​ это один из​

1. ​ клавиатуре.​Вырезать​
2. ​ Можно также нажать сочетание​​ выделенному диапазону.​​Выделите ячейку или диапазон​​ щелкните ярлычок другого​ ​ актуальными справочными материалами​ это не то​
3. ​ в сформированной таблице​ “а”,- говорите и​ тогда такой пример?​
4. ​: Я так делаю​​ Выделите строку, скопируйте​​: sofi, честно -​​ ячейки с данными,​ ​ в зависимости от​ тех случаев, когда​

​Вырезанные ячейки переместятся на​.​​ клавиш CTRL+V.​​При копировании значения последовательно​​ ячеек с данными,​​ листа или выберите​

1. ​ на вашем языке.​ что нужно, потому​
2. ​ добавит периоды (скажем​​ “б”.​​ Ведь очевидно, что​
3. ​ при помощи макроса,но​​ и посмотрите где​​ не получилось добиться​

Виды гиперссылок

Если добавлять гиперссылку первым способом (напрямую в ячейку), то пользователь будет работать с диалоговым окном «Добавление гиперссылки», в котором предлагается 4 способа связи:

1. Файл, веб-страница (здесь указывается путь к файлу или адрес сайта).
2. Место в документе (лист или ячейка).
3. Новый документ (путь к новому документу).
4. Электронная почта (здесь указывается адрес получателя, который будет отображен при открытии Microsoft Outlook).

Существующие типы гиперссылок

Если вдруг нужно будет изменить или удалить существующую гиперссылку, достаточно щелкнуть на ней правой кнопкой мыши и выбрать соответствующий пункт («изменить» или «удалить гиперссылку»).

Такими довольно простыми способами можно создать гиперссылку в Excel. Наиболее простым и практичным способом является первый — через ячейку. Дело в том, что именно здесь собраны все варианты: можно создать ссылку на любое поле в документе, можно указать путь к файлу, адрес сайта и т.д. Хотя использовать можно любой из этих способов — как удобнее.

значения info_type

В следующем списке описаны текстовые значения, которые можно использовать для аргумента info_type. Эти значения должны быть введены в функцию ЯЧЕЙКИ с кавычками (“”).

Ссылка на первую ячейку в аргументе “ссылка” в виде текстовой строки.

Номер столбца ячейки в аргументе “ссылка”.

1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях — 0 (ноль).

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Значение левой верхней ячейки в ссылке; не формула.

Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка (“”).

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется “-“. Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется “()”.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях — 0.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (‘) соответствует тексту, выровненному влево, двойная кавычка (“) — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта () — тексту, распределенному по всей ширине ячейки, а пустой текст (“”) — любому другому содержимому ячейки.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

0, если ячейка разблокирована, и 1, если ячейка заблокирована.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Номер строки ячейки в аргументе “ссылка”.

Текстовое значение, соответствующее типу данных в ячейке. Значение “b” соответствует пустой ячейке, “l” — текстовой константе в ячейке, “v” — любому другому содержимому.

Возвращает массив с двумя элементами.

Первый элемент в массиве — это ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.

Второй элемент массива — это логическое значение, равно TRUE, если ширина столбца является значением по умолчанию или FALSE, если ширина была явно задана пользователем.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Коды форматов функции ЯЧЕЙКА

В приведенном ниже списке описаны текстовые значения, возвращаемые функцией ЯЧЕЙКА, если в качестве аргумента “тип_сведений” указано значение “формат”, а аргумент ссылки указывает на ячейку, отформатированную с использованием встроенного числового формата.

Формат Microsoft Excel

Значение, возвращаемое функцией ЯЧЕЙКА

Синтаксис функции

ГИПЕРССЫЛКА(адрес, )

Адрес — адрес страницы в сети интернет или путь к файлу на диске. Адрес может указывать на определенное место в Книге, например на ячейку или именованный диапазон. Путь может представлять собой путь к файлу на жестком диске, либо URL-адрес в Интернете или интрасети. Аргумент Адрес записывается в виде текстовой строки, заключенной в кавычки, или представляет собой ссылку на ячейку, содержащую гиперссылку или путь в виде текстовой строки (без кавычек).

Имя — необязательный аргумент, необходимый для отображения текста гиперссылки (т.е. просто текстовая строка для отображения в гиперссылке). Текст ссылки отображается синим цветом с подчеркиванием. Если этот аргумент опущен, в ячейке в качестве текста ссылки отображается аргумент Адрес . Также как и Адрес, аргумент Имя может быть представлен текстовой строкой, именем или ссылкой на ячейку, содержащей текст. Если аргумент Имя возвращает значение ошибки (например, #ЗНАЧ!), вместо текста ссылки в ячейке отображается значение ошибки.

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

Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel.

Теперь произвольно выберем три ячейки:

• В первой ячейки укажем ФИО интересующего нас сотрудника. Допустим это будет ячейка: J2;
• В второй ячейки укажем интересующий нас месяц первого полугодия. Это будет ячейка: J3;
• В третью ячейку поместим нашу формулу (ИНДЕКС и ПОИСКПОЗ). Это будет ячейка: J4. Данная формула, в указанную нами ячейку, вернет значение равное количеству продаж условного товара, сделанных указанным нами сотрудником в указанный месяц.

Итак, с помощью кнопки Вставить функцию вызовем функцию ИНДЕКС. В диалоговом окне Мастер функций — шаг 1а из 2 выбирем первый вариант (массив; номер_строки; номер_столбца). Далее появится диалоговое окно Аргументы функции.

В поле Массив указываем диапазон ячеек, в которых функция будет осуществлять поиск искомого значения. В нашем примере это диапазон ячеек B2:G11.

В поле Номер_строки вводим вручную: ПОИСКПОЗ(J2;A2:A11;0). Где: J2 — ячейка, в которой мы укажем ФИО интересующего нас сотрудника;  А2:А11 — диапазон ячеек с именами сотрудников (в исходной таблице); 0 — поиск точного совпадения (данный критерий можно менять в зависимости от задачи, которая стоит перед пользователем).

В поле Номер_столбца вводим вручную: ПОИСКПОЗ(J3;B1:G1;0). Где: J3 — ячейка, в которой мы укажем  интересующий нас месяца; B1:G1 — диапазон ячеек с месяцами (в исходной таблице); 0 — поиск точного совпадения (данный критерий можно менять в зависимости от задачи, которая стоит перед пользователем).

Все вместе выглядит вот так:

=ИНДЕКС(B2:G11;ПОИСКПОЗ(J2;A2:A11;0);ПОИСКПОЗ(J3;B1:G1;0))

Нажимаем ОК.

В ячейку J2 вводим ФИО сотрудника. Например: Волков В.В.

В ячейку J3 вводим название месяца. Например: Апрель.

Так как в аргументах функции ПОИСКПОЗ заданы точные критерии поиска (тип сопоставления — 0), необходимо вводить значения (ФИО и месяц) идентично тому, как они указаны в исходной таблице. Данный тип сопоставления можно менять в зависимости от задачи, которая стоит перед пользователям. 

ПОИСКПОЗ(J2;A2:A11;)

ПОИСКПОЗ(J3;B1:G1;)

Функция вернула значение 5434 (количество проданных единиц условного товара) в ячейку J4. Данное значение находиться на пересечении строки с ФИО Волков В.В. и столбца с названием Апрель.

Данный вариант сочетания функций ИНДЕКС и ПОИСКПОЗ будет работать и в том случае, если исходная таблица находиться на одном Листе. А ячейки, в которых будут указываться критерии для поиска и выводиться результаты вычислений функций, будут находиться на другом Листе. При этом, в формулу, необходимо будет добавить ссылки на соответствующий Лист, в котором будет находиться исходная таблица.

Если исходная таблица находиться на Листе 1, а формула и ячейки с критериями поиска на Листе 2, тогда формула будет выглядеть следующем образом:

=ИНДЕКС(Лист1!B2:G11;ПОИСКПОЗ(J2;Лист1!A2:A11;0);ПОИСКПОЗ(J3;Лист1!B1:G1;0))

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.

Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.

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

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Месяц </em>;
• в поле Область выберите лист Книга </em>;
• в поле Диапазон введите формулу =СМЕЩ(лист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) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков

Теперь создадим еще один динамический диапазон для суммирования продаж.

Для создания динамического диапазона :

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Продажи_за_месяц </em>;
• в поле Диапазон введите формулу = СМЕЩ(лист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 .

Как узнать о необходимости использования абсолютных ссылок в Excel — Трюки и приемы в Microsoft Excel

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

Абсолютная ссылка на ячейку не меняется, даже когда формула копируется и вставляется куда-нибудь в другое место. Абсолютная ссылка указывается с двумя знаками доллара, например: =$А$1 или =CУMM($A$1:$F$24).

Относительная ссылка, с другой стороны, не использует знаков доллара: =А1 или =CУMM(A1:F24).

Большинство ссылок на ячейки и наборы ячеек, которые вы будете использовать, будут относительными.

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

Когда вам нужно использовать абсолютные ссылки? Ответ прост: единственная ситуация, когда вам нужно задуматься об абсолютных ссылках, — необходимость копировать формулы.

Легче всего уяснить эту концепцию на примере. На рис. 69.1 показан обычный лист. Формула в ячейке D2, умножающая количество на стоимость единицы, следующая: =В2*С2.

Рис. 69.1. Лист с данными, где можно использовать относительные ссылки

Эта формула использует относительные ссылки на ячейки. Таким образом, при копировании формулы в другие ячейки в столбце ссылки настроятся относительно нового местоположения. Например, скопируйте формулу в ячейку D3, и она примет такой вид: =B3*C3. Что было бы, если бы ссылки на ячейки были абсолютными: =$В$2*$С$2.

В этом случае копирование формулы в расположенные ниже ячейки привело бы к неправильному результату. Формула в ячейке D3 является точно такой же, как и формула в ячейке D2, и возвращает итоговое значение для стульев, а не рабочих столов.

Теперь мы расширим пример и будем рассчитывать налог с продаж. Ставка налога хранится в ячейке В7 (рис. 69.2). В такой ситуации формула в ячейке Е2 будет следующей: =D2*$B$7.

Рис. 69.2. Формула, которая ссылается на ячейку, содержащую налог с продаж, должна быть абсолютной

Итоговая сумма умножается на налоговую ставку, которая хранится в ячейке В7

Обратите внимание, что ссылка на В7 является абсолютной и не изменится при копировании ячейки. Когда формула в Е2 копируется в ячейки, расположенные ниже, ячейка Е3 содержит следующую формулу: =D3*$B$7

Ссылка на ячейку D2 регулируется, а ссылка на ячейку В7 — нет. Это и есть именно то, чего вы хотите.