Excel нумерация строк по условию
Сформируем последовательность 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.
Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .
Чтобы сформировать последовательность 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, . Это пример последовательности с периодически повторяющимися элементами.
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем правую клавишу мыши
Пусть в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши появится контекстное меню, в котором нужно выбрать пункт Заполнить . Получим последовательность чисел 1, 2, 3, 4 …
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3, . можно следующим образом:
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .
Быстро пронумеровать список фамилий, расставить людей по рейтингу и присвоить участникам викторины порядковый номер — задачи, которые можно автоматизировать. Зачем проставлять номера каждому участнику списка отдельно, если можно воспользоваться силой Excel и сделать всё быстро, и без мучений.
Способ 1 — простая нумерация
Ставим в первую ячейку цифру 1, во вторую — цифру 2. Затем выделяем обе ячейки и тянем вниз, чтобы заполнить всё до конца.
Если строк много, можно воспользоваться быстрым способом. Наводим мышку на правый нижний угол ячейки и делаем двойной клик по крестику — это сразу заполнит весь диапазон до последней строки.
Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.
Способ 2 — автоматическая последовательная нумерация
Здесь мы уже используем формулу, которая будет увеличивать каждую предыдущую ячейку на единицу. Этот способ удобен тем, что такая формула помогает быстро расширять список. Итак, в первую ячейку ставим 1, а во вторую вписываем формулу:
Далее точно так же тянем всё вниз и заполняем диапазон.
Этот способ прост в использовании, потому что можно не выделять несколько ячеек, а просто протянуть формулу вниз и быстро заполнить все нужные значения.
Как это работает. Наша формула образно звучит так: «Возьми ячейку над текущей и увеличь результат на единицу». А так как формула тянется вниз, то всё значение каждой ячейки увеличивается на один — и происходит автоматическая нумерация.
Способ 3 — через функцию =СТРОКА()
Если ваш список увеличивается строго вниз, то способ через функцию СТРОКА будет оптимальным.
Функция =СТРОКА() возвращает порядковый номер строки для текущей ячейки, начиная со строки №1
Зная это, мы можем найти закономерность и здесь. Введём формулу в ячейку E2:
Как видите, результат работы функции — это число 2. Строка вторая, значит в результате будет 2.
Чтобы нам получить правильный результат, нужно подкорректировать формулу и уменьшить результат на количество строк шапки, то есть на одну строку. Для этого просто вычитаем единицу из самой функции.
Способ удобен тем, что если нам нужно пронумеровать каждый элемент списка отдельно. Мы можем скопировать формулу в любое место таблицы и получить мгновенный результат.
Как это работает. Механика функции простыми словами: «у ячейки с таким адресом вот такой номер строки». И нам, чтобы начать список с первого пункта, нужно этот номер строки уменьшить на количество строк шапки нашей таблицы. Так мы и получаем желаемый результат.
Способ 4 — нумерация через инструмент «Прогрессия»
Нумеровать можно не только формулами и арифметическими действиями, но и встроенными инструментами. Нам нужен инструмент «Прогрессия».
Арифметическая прогрессия — это последовательность чисел, где каждое следующее число больше предыдущего на какой-то шаг
В нашем случае прогрессия самая простая — мы увеличиваем каждое число на единицу, прямо как в способе 2, но только без лишних действий руками.
Вводим в ячейку E1 число 1, выделяем эту единицу и весь диапазон, который нужно пронумеровать, а затем переходим на вкладку «Главная» → область «Редактирование» → кнопка «Заполнить» → «Прогрессия…»
Открывается диалоговое окно, в котором мы выбираем расположение «По столбцам», поскольку нумеруем вниз. Затем выбираем тип «Арифметическая» и ставим шаг — 1.
Если мы знаем, сколько всего людей в списке, то мы можем поставить предельное значение, например, 25, и тогда инструмент остановит свою работу, как только дойдёт до определённого числа. Нажимаем «Ок» и видим как Excel делает автоматическую нумерацию строк.
Как это работает. Прогрессии могут быть разными, но в случае с арифметическими — они ведут себя достаточно предсказуемо, просто увеличивая наше число на какое-то одинаковое число — шаг. В нашем случае шаг равняется единице, что полностью решает нашу задачу.
Способ 5 — функция МАКС()
Иногда бывает, что список людей идёт с разрывами и нам нужно продолжать нумерацию после пропуска. В этом случае нам подойдёт способ через функцию МАКС и работает он следующим образом.
Функция МАКС ищет наибольшее число в указанном диапазоне
В первую ячейку ставим число 1, а во вторую вводим формулу: =МАКС($E$2:E2)+1
Начало диапазона мы указали строго от $E$2, то есть первая граница не меняется ни при каких условиях. Концом диапазона будет последняя ячейка после протягивания. При протягивании такой формуле в строке 26 мы получим формулу: =МАКС($E$2:E25)+1
А там где нам нужно пропустить значения автоматической нумерации, мы просто удаляем формулу из ячейки и всё, что идёт ниже, автоматически пересчитывается.
При этом формулу можно просто скопировать и вставить в любом месте сверху и снизу нашего списка нумерации и все числа автоматически пересчитаются.
Как это работает. Так как диапазон увеличивается на единицу =МАКС() + 1, то у нас каждая ячейка будет больше другой и функция МАКС будет выбирать её.
Способ 6 — адаптивная автоматическая нумерация через =СЧЁТЗ() и =ЕСЛИ()
Если в предыдущем способе нам всё ещё нужно работать с ячейками вручную, то в этом способе мы всё автоматизируем ещё больше. Нам понадобится комбинация функций СЧЁТЗ и ЕСЛИ, чтобы фильтрация происходила по условию.
Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие
Итак, формула: =ЕСЛИ(B2=»»;»»;СЧЁТЗ($B$2:B2))
Вставляем формулу в ячейку B2, тянем вниз и формула автоматически пропускает пустые строки. Заметьте, что в части формулы, где СЧЁТЗ, мы закрепили первую часть, потому что мы не хотим, чтобы диапазон состоял из одной строки. Нам нужно, чтобы он постоянно увеличивался.
Ещё важно обратить внимание, что две двойные кавычки «» рядом означают, что ячейка должна быть пустой, то есть не содержать в себе никаких данных.
Как это работает. Функция ЕСЛИ отвечает за такое условие: если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе — посчитать общее количество заполненных ячеек.
Мы разобрались, как создать автоматическую нумерацию в Excel. Эти же способы работают и в гугл-таблицах. Желаем приятного применения! Пробуйте и делитесь впечатлениями.
Приходите на курсы по обучению работе с Excel-таблицами — они помогут освойте таблицы, даже если сейчас знаний ноль.
Подскажите, пожалуйста, возможно ли решить макросами одну задачу?
В настоящее время решаю вопрос нумерации стандартными формулами. Но столкнулся с таким вопросом, что когда добавляю строку или переношу строку в другое место (выше или ниже) нумерация сбивается.
При добавлении строки внутри таблицы и занесении информации со второй колонки в любую ячейку необходимо чтобы в колонке "А" менялась нумерация
Если строка пустая со второго столбца пусть будет 100 столбцов то нумерация не меняется.
Возможно ли сделать автоматическую нумерацию без ручного запуска макроса?
P.S. кросса не будет, обещаю
Подскажите, пожалуйста, возможно ли решить макросами одну задачу?
В настоящее время решаю вопрос нумерации стандартными формулами. Но столкнулся с таким вопросом, что когда добавляю строку или переношу строку в другое место (выше или ниже) нумерация сбивается.
При добавлении строки внутри таблицы и занесении информации со второй колонки в любую ячейку необходимо чтобы в колонке "А" менялась нумерация
Если строка пустая со второго столбца пусть будет 100 столбцов то нумерация не меняется.
Возможно ли сделать автоматическую нумерацию без ручного запуска макроса?
P.S. кросса не будет, обещаю Anis625
Подскажите, пожалуйста, возможно ли решить макросами одну задачу?
В настоящее время решаю вопрос нумерации стандартными формулами. Но столкнулся с таким вопросом, что когда добавляю строку или переношу строку в другое место (выше или ниже) нумерация сбивается.
При добавлении строки внутри таблицы и занесении информации со второй колонки в любую ячейку необходимо чтобы в колонке "А" менялась нумерация
Если строка пустая со второго столбца пусть будет 100 столбцов то нумерация не меняется.
Возможно ли сделать автоматическую нумерацию без ручного запуска макроса?
P.S. кросса не будет, обещаю Автор - Anis625
Дата добавления - 30.01.2018 в 18:33
Как вариант добавил пользовательскую формулу СЧСТРОК().
Возможно ли сделать автоматическое проставление формулы при заполнении строки любыми значениями без ручного запуска макроса (чтобы не протягивать формулу)?
Как вариант добавил пользовательскую формулу СЧСТРОК().
Возможно ли сделать автоматическое проставление формулы при заполнении строки любыми значениями без ручного запуска макроса (чтобы не протягивать формулу)? Anis625
Возможно ли сделать автоматическое проставление формулы при заполнении строки любыми значениями без ручного запуска макроса (чтобы не протягивать формулу)? Автор - Anis625
Дата добавления - 31.01.2018 в 08:32
Но с этим вариантом не соблюдается главное условие - нумерация ставится только там где есть не пустые строки со второго столбца
Но с этим вариантом не соблюдается главное условие - нумерация ставится только там где есть не пустые строки со второго столбца Anis625
В примере такого не было, а условия не внимательно прочитал.
Тогда усложним формулу, правда с летучей СМЕЩ
[vba]
В примере такого не было, а условия не внимательно прочитал.
Тогда усложним формулу, правда с летучей СМЕЩ
[vba]
Вам нужно превратить Вашу таблицу в умную таблицу.
Затем вставьте формулу в первый столбец в первую строку. Формула должна автоматически заполнить весь столбец.
Умная таблица должна называться "Таблица1".
Заголовки у столбцов указаны здесь:
[Задача]:[Комментарий]
Вместо $A$3 укажите ячейку, в которой находится первая ячейка шапки таблицы.
Здесь указан столбец с порядковыми номерами. Если у Вас другой, то измените:
ИНДЕКС(A:A
Вам нужно превратить Вашу таблицу в умную таблицу.
Затем вставьте формулу в первый столбец в первую строку. Формула должна автоматически заполнить весь столбец.
Умная таблица должна называться "Таблица1".
Заголовки у столбцов указаны здесь:
[Задача]:[Комментарий]
Вместо $A$3 укажите ячейку, в которой находится первая ячейка шапки таблицы.
Здесь указан столбец с порядковыми номерами. Если у Вас другой, то измените:
ИНДЕКС(A:A Karataev
Удалено. Нарушение Правил форума вкладка действующие.
---
Суть вопроса и крик о помощи примерно звучит так . система 1с формирует отчёт после бригада операторов корректирует удаляет то что им не нужно так же нумерация сбивается и требуется занова пронумеровать ( нумеровать нужно с условием колонки (D) договор)
нужно пробежаться по колонке ("Д") и если договор не совпал с предыдущим тогда
записать в этой строке колонки ("А") максимальное значение +1
Предварительно список будет сортироваться по названию и договорам (ручками).
--------------
С языком и формулами экселя знаком на уровне сложить и отнять .
Так что прошу помощи в данном вопросе .нужно на вчера . спасибо всем не равнодушным.
[moder]Сделайте файл-пример на несколько строк и положите сюда. Зачем заставляете качать кучу ненужной инфы?[/moder]
Удалено. Нарушение Правил форума вкладка действующие.
---
Суть вопроса и крик о помощи примерно звучит так . система 1с формирует отчёт после бригада операторов корректирует удаляет то что им не нужно так же нумерация сбивается и требуется занова пронумеровать ( нумеровать нужно с условием колонки (D) договор)
нужно пробежаться по колонке ("Д") и если договор не совпал с предыдущим тогда
записать в этой строке колонки ("А") максимальное значение +1
Предварительно список будет сортироваться по названию и договорам (ручками).
--------------
С языком и формулами экселя знаком на уровне сложить и отнять .
Так что прошу помощи в данном вопросе .нужно на вчера . спасибо всем не равнодушным.
[moder]Сделайте файл-пример на несколько строк и положите сюда. Зачем заставляете качать кучу ненужной инфы?[/moder] roman457
нужно пробежаться по колонке ("Д") и если договор не совпал с предыдущим тогда
записать в этой строке колонки ("А") максимальное значение +1
Предварительно список будет сортироваться по названию и договорам (ручками).
--------------
С языком и формулами экселя знаком на уровне сложить и отнять .
Так что прошу помощи в данном вопросе .нужно на вчера . спасибо всем не равнодушным.
[moder]Сделайте файл-пример на несколько строк и положите сюда. Зачем заставляете качать кучу ненужной инфы?[/moder] Автор - roman457
Дата добавления - 03.09.2014 в 11:53
Пусть в ячейке A2 введено значение 1 (номер первой строки). В следующую строку таблицы, в ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже.
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Другим вариантом является использование формулы =СТРОКА()-СТРОКА($A$1) . В ячейке А1 должен находиться заголовок столбца.
Для чрезстрочной нумерации используйте формулу (см. файл примера ) =ЕСЛИ(ЕЧЁТН(СТРОКА());"";A7+1)
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то пронумеровать строки можно следующим образом :
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
Читайте также: