Числовые последовательности в EXCEL (порядковые номера 1,2,3. и др.)
Сформируем последовательность 1, 2, 3, . Пусть в ячейке A2 введен первый элемент последовательности — значение 1 . В ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже (см. файл примера ).
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.
Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.
Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))
Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.
Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);»00-00″) в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.
Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность n2+1 ((n в степени 2) +1) создадим формулой =(СТРОКА()-СТРОКА($A$1))^2+1 начиная с ячейки А2 .
Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .
Примечание . Для выделения повторов использовано Условное форматирование .
Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.
Наиважнейшая формула Excel — Формулы рабочего листа — Excel — Каталог статей — Perfect Excel
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
Выделение ячеек, содержащих и НЕ содержащих формулы в EXCEL
Представим, что в части ячеек листа имеются формулы, а в других – значения. Нужно определить, что в каких находится.
Выделить ячейки, которые содержат формулы можно воспользовавшись стандартным инструментом EXCEL Выделение группы ячеек… или через меню: на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить , а затем выберите в списке пункт Формулы .
Выделить ячейки, которые содержат НЕ формулы, т.е. содержат константы можно аналогичным образом, только вместо Формулы нужно выбрать Константы .
Если в ячейке введено =11 , то это выражение считается формулой, хотя оно и не может быть изменено. Если у ячейки установлен текстовый формат, то введенная в нее формула будет интерпретирована как текст, т.е. константа.
Вышеуказанный подход требует вмешательства пользователя, т.е. необходимо вручную выбирать пункты меню. Можно настроить Условное форматирование так, чтобы после ввода формулы происходило автоматическое выделение, содержащей ее ячейки.
Допустим значения вводятся в диапазон A1:A10 (см. файл примера ) . Для настройки Условного форматирования для этого диапазона необходимо сначала создать Именованную формулу , для этого:
- выделите ячейку A1 ;
- вызовите окно Создание имени из меню Формулы/ Определенные имена/ Присвоить имя ;
- в поле Имя введите название формулы, например Формула_в_ячейке ;
- в поле Диапазон введите =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1)
- нажмите ОК.
- выделите диапазон A1:A10 ;
- вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило );
- выберите Использовать формулу для определения форматируемых ячеек;
- в поле « Форматировать значения, для которых следующая формула является истинной » введите =Формула_в_ячейке ;
- выберите требуемый формат, например, красный цвет фона;
Теперь все ячейки из диапазона A 1: A 10 , содержащие формулы, выделены красным.
В этом примере мы использовали макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ() . Это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL 2007, а можно использовать только в качестве Именованной формулы , что мы и сделали.
Чтобы, наоборот, выделить все непустые ячейки, содержащие константы (или НЕ содержащие формулы), нужно изменить формулу на =И(НЕ(ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1));НЕ(ЕПУСТО(Лист1!A1)))
Совет : Чтобы показать все формулы, которые имеются на листе нужно на вкладке Формулы в группе Зависимости формул щелкните кнопку Показать формулы .
Чтобы выделить все ячейки, содержащие формулы, нужно на вкладке Главная , в группе Редактирование выбрать команду Формулы .
Чтобы найти все ячейки на листе, имеющие Условное форматирование необходимо:
Выделение ячеек, содержащих и НЕ содержащих формулы в EXCEL. Примеры и описание
Добрый день. Такой вопрос. Два файла ексель и мне нужно в файле1 сделать ссылку на одну ячейку с файла2, но проблема в том, что в файле2 постоянно добавляются строки над той ячейкой на которую ссылаюсь и тем самым номер строки меняется и ссылка сбивается. Как сделать в файле1 чтобы при добавлении строки в файле2 формула автоматически переходила на следующий строку?
