Как сделать автоматическую нумерация позиций и подпунктов в google sheets?
Какова суть. Нужно, чтобы в поле «№» автоматически подтягивался номер в зависимости от заполнения таблицы.
Целые числа, если заполняется строка «Ресурс», при этом строка «Артикул» не должна влиять на нумерацию.
Но если же «Ресурс» остается пустым, а «Артикул» заполнен, то должны образоваться подпункты, как в примере.
После пропуска нескольких строк в Ресурсе в «№» должно затянуться целое число.
Как пронумеровать строки в Google Таблицах (добавить серийные номера)
Если вы ведете записи в Google Таблицах и управляете ими, вам может понадобиться столбец с серийными номерами. Хотя вы можете легко создать столбец с серийными номерами, введя их вручную, это неэффективный способ.
В этом уроке я покажу вам несколько способов пронумеровать строки в Google таким образом, чтобы он автоматически добавлялись серийные номера.
Это легко сделать с помощью формул и других простых приемов.
Как пронумеровать строки в Google Таблицах
В этом уроке я покажу вам четыре способа добавления серийных номеров в Google Таблицы:
- Использование маркера заливки
- Использование функции СТРОКА (ROW)
- Использование функции СЧЕТ (COUNTA)
- Добавление 1 к предыдущему серийному номеру
Выбранный вами метод нумерации строк будет зависеть от того, как структурированы ваши данные или как вы хотите, чтобы это работало.
Например, если между ними есть пустые строки, и вы хотите добавить серийный номер только к непустым записям, вы не можете использовать все методы (пример рассматривается далее в этом руководстве).
Число строк с помощью маркера заполнения
Если у вас есть набор данных без пустых строк, это самый простой способ добавить серийные номера.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите добавить серийные номера в столбец A.
Ниже приведены шаги по использованию «Дескриптора заполнения» для ввода серийных номеров:
- Вставьте столбец слева от столбца Имя. Для этого щелкните правой кнопкой мыши любую ячейку в столбце A и выберите «Вставить столбец».
- [Необязательно] Дайте новому столбцу заголовок и отформатируйте его, как другие столбцы.
- В ячейке A2 введите 1
- В ячейке A3 введите 2
- Выберите обе ячейки
- Поместите курсор в нижний правый угол выделения. Курсор изменится на значок плюса (это называется ручкой заливки).
- Дважды щелкните мышью (или щелкните левой кнопкой мыши и перетащите, пока не захотите цифры)
Приведенные выше шаги дадут вам серийные номера в дополнительном столбце, который вы вставили. Затем вы можете отформатировать этот столбец, чтобы он выглядел лучше.
Вышеупомянутые шаги работают, когда у вас есть непрерывный набор данных (т.е. Нет пустых строк). В случае, если в наборе данных есть пустая запись, двойной щелчок по дескриптору заполнения заполнит только числа до заполненной записи перед пустой строкой.
Примечание: результат, который вы получаете от этого метода, статичен. Если вы добавите больше записей в набор данных, вам придется снова сделать это, чтобы заполнить новые строки.
Использование функции ROW (СТРОКА) для нумерации строк
Функция СТРОКА — это Google Таблицы, которые дают вам номер строки ссылки на ячейку, которую вы ей даете. И если вы не дадите ему никакого входного аргумента, он вернет номер строки ячейки, в которой он используется.
Вы можете использовать функцию СТРОКА (немного изменив ее), чтобы получить серийные номера в столбце.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите добавить серийные номера в столбец A.
Ниже приведены шаги для этого:
- Вставьте столбец слева от столбца Имя. Для этого щелкните правой кнопкой мыши любую ячейку в столбце A и выберите «Вставить столбец».
- [Необязательно] Дайте новому столбцу заголовок
- В ячейке A2 введите формулу: = ROW () — 1
- Скопируйте и вставьте все ячейки, в которые вы хотите ввести серийный номер.
Вышеупомянутая формула использует функцию ROW для получения номера текущей строки, а затем вычитает из него 1, поскольку мы начинаем со второй строки и далее. Если вы начинаете с первой строки, вы можете просто использовать функцию СТРОКА (ROW).
Если вы хотите показать серийный номер только для заполненных записей, вы можете использовать следующую формулу:
Вы можете использовать эту формулу для расширенного числа ячеек в столбце (даже если в нем пока нет записей). Как только вы добавите новую запись, номер появится автоматически.
Вышеупомянутое использует функцию ЕСЛИ (IF), чтобы проверить, есть ли текст в соседней ячейке или нет. В случае отсутствия текста возвращается пустое значение, в противном случае возвращается номер строки.
Преимущество использования функции СТРОКА (ROW) заключается в том, что в случае удаления строки из этого набора данных функция будет настраиваться, чтобы дать вам правильный номер строки.
Использование функции COUNTA
Другой способ пронумеровать строки в Google Таблицах — использовать функцию СЧЁТ (COUNTA).
Функция COUNTA будет подсчитывать количество непустых ячеек, прилегающих к ячейкам, и возвращать серийный номер только тогда, когда строка заполнена (в отличие от метода функции ROW, который все равно дает серийный номер, если строка пуста).
Предположим, у вас есть набор данных, как показано ниже, и вы хотите присвоить строке номер (серийный номер) только в том случае, если эта строка заполнена.
Следующая формула может сделать это:
В приведенной выше формуле функция ЕСЛИ проверяет, пуста ли соседняя ячейка (с помощью функции ISBLANK). Если он пуст, формула ЕСЛИ вернет пустое значение, а если нет, она подсчитает все заполненные строки в указанном диапазоне и выдаст это число.
Обратите внимание, что я использую $ B $ 2: B2 в качестве диапазона, в котором я заблокировал первый B2 (со знаком доллара перед алфавитом столбца и номером строки), но не вторым. Это позволяет мне расширять диапазон, когда я копирую его в другие ячейки. Например, если я скопирую это в ячейку B5, она расширится, и диапазон станет $ B $ 2: B5.
Добавление 1 к предыдущему серийному номеру
Это быстрый и простой способ добавить серийные номера в столбец. Это лучше всего подходит, когда в наборе данных нет пустых строк. Предположим, у вас есть набор данных, показанный ниже, в который вы хотите вставить серийные номера в столбец A.
Ниже приведены шаги для этого:
- Введите 1 в ячейку A2
- В ячейке A3 введите формулу = A2 + 1
- Скопируйте и вставьте эту формулу для всех ячеек в столбце, где вы хотите, чтобы числа
Приведенная выше формула просто добавляет 1 к числу в ячейке над ней.
Хотя это простой и понятный метод, есть несколько недостатков, которые вы должны знать, прежде чем использовать этот метод:
- Это присвоит номер независимо от того, есть ли пустые строки или нет.
- Если вы удалите строку, все значения под ней вернут ссылочную ошибку (#REF!)
- Когда вы вставляете новую строку, она не обновляет номер строки автоматически. Вам нужно будет скопировать и вставить формулу во все ячейки в этой новой строке и во все ячейки под ней.
Итак, это четыре метода, которые вы можете использовать для нумерации строк в Google Таблицах. Вы можете выбрать любой из этих методов в зависимости от того, как структурированы ваши данные. Если в вашем наборе данных есть пустые строки, лучше всего использовать метод COUNTA.
Как сделать автоматическую нумерацию в гугл таблицах?
Вариант I. Когда требуется пронумеровать все строки, в независимости есть ли в них какой-либо текст.
Первый способ.
Первый шаг. В ячейке «А1» поставим цифру один. А в ячейке «А2» напишем простую формулу: =А1+1.
Второй шаг. Выделяем ячейку «А2» и нажимаем на клавиатуре клавиши «Ctrl+C», дальше выделяем диапазон ячеек с «А3» по «А7» и нажимаем на сочетание клавиш «Ctrl+V». В итоге у всех строк есть номера.
Второй способ.
Первый шаг. В ячейку «А1» ставим цифру один, а в ячейке «А2» ставим цифру два. После выделяем ячейки «А1» и «А2». После обращаем внимание на нижний правый угол, где есть маленький синий квадратик.
Вариант II. Когда требуется пронумеровать только строки, которые имеют какие-либо значения.
Первый шаг. В ячейке «А1» пишем простую формулу =ЕСЛИ(B2<>"";СЧЁТЗ($B$2:B2);""), где:
- B2<>"" – проверяет, если в ячейки какое-либо значение.
- СЧЁТЗ($B$2:B2) – значение, которые выходит, если ячейка не пустая и данная функция подсчитывает количество значений в наших ячейках.
- "" – значение выходит, когда ячейка пустая, т.е. в нашем случае будет выходить пустая ячейка.
Второй шаг. Выделяем ячейка «А1» нажимаем на сочетание клавиш «Ctrl+C», дальше выделяем диапазон ячеек с «А2» по «А7» и нажимаем на сочетание клавиш «Ctrl+V». После номера покажется только там, где ячейки не пустые.
Сквозная нумерация в смете в Google Таблицах. Функция СЧЁТЗ(COUNTA)
Рассмотрим такую несложную, но все же не самую тривиальную задачу: сделать сквозную нумерацию пунктов (операций) в смете.
Так, чтобы нумерация каждого раздела, каждой группы работ не начиналась с единицы, а продолжалась по всей смете. И при этом чтобы это происходило автоматически при любом количестве операций и разделов:
Ход мыслей следующий. Каждая операция характеризуется какими-то параметрами: единицей измерения, объемом работ, ценой или чем-то еще. А заголовки разделов не имеют никаких параметров. Этим и воспользуемся: наша задача в каждой строке, в которой нет параметров, ничего не указывать в столбец с номером, а в тех строках, где есть данные (то есть имеется операция, а не заголовок или пустота) — вводить порядковый номер. И этот номер — это количество операций от начала таблицы до текущей строки.
Количество непустых ячеек в диапазоне от первой строки сметы до текущей будем определять с помощью функции COUNTA/СЧЁТЗ. Она возвращает количество непустых ячеек в диапазоне. Главное — правильно указать диапазон, закрепив первую ячейку и не закрепляя последнюю. Таким образом, в каждой строке в формуле будет использоваться диапазон от начала сметы до текущей строки.
И не забудем про ячейки, в которых не нужно указывать номер — там, где нет данных в одном из столбцов (давайте работать со столбцом C, хотя подошел бы и любой из трех следующих).
В десятой строке формула будет выглядеть так:
Такую формулу можно смело протягивать до конца таблицы и с запасом. Она будет выдавать либо корректный номер операции, либо ничего (пустую текстовую строку).
Давайте на десерт еще немного автоматизируем смету. Сделаем условное форматирование, чтобы строки с заголовками разделов заливались серым цветом автоматически. Такие строки характеризуются тем, что в них есть данные в столбце B, но нет в столбце C. Воспользуемся этим и создадим правило условного форматирования с такой формулой:
То есть нужно выполнение двух условий: пустота в столбце C и наличие чего-то в столбце B, чтобы залить строку.