Как увеличить количество строк в Excel
Обновлено: 21.11.2024
Я работаю в телекоммуникационной отрасли, где я использую Excel почти на 90% своих
обязанностей, и я сталкиваюсь с проблемой, когда хочу перенести информацию
из биллинговой системы в Excel , я говорю о 1 000 000 + записей
(строк), где excel имеет только 65 000 строк, очень сложно разделить
данные на разные листы, это также требует времени для запуска запроса
при выставлении счетов, учитывая ограничение Excel, выполнять все строки,
извлекаемые из выставления счетов. т. е. если мне нужны данные с 1-го по 31-е, то я
разбил их на 3 группы (по десять дней каждая), я бы предложил увеличить
количество строк по крайней мере до 2 000 000 строк. кроме того, CDR, извлеченный
из биллинговой системы, содержит более 2 000 0000 записей.
----------------
Этот пост является предложением для Microsoft, и Microsoft отвечает на
предложения, набравшие наибольшее количество голосов. Чтобы проголосовать за это предложение, нажмите кнопку "Я
согласен" на панели сообщений. Если вы не видите кнопку, перейдите по этой
ссылке, чтобы открыть предложение в веб-программе чтения новостей Microsoft, а затем
нажмите «Я согласен» в области сообщений.
Excel 2007 будет иметь 1 048 576 строк.
Пока я могу предложить вам импортировать ваши данные в MS Access, а затем из
Access вы можете экспортировать в Excel нужную вам сводку
--
(Я не уверен в названиях меню, опций и команд, потому что
перевожу с итальянской версии Excel. )
Надеюсь, я помог вам.
Заранее благодарим за отзыв.
Франц Верга из Италии
И сколько времени, как мы все думали, должно было пройти, прежде чем кто-нибудь потребовал увеличить
новое ограничение в 1 миллион строк в Excel 2007
wise_man в новой версии Excel ограничения увеличены до 1
миллиона строк и 16 000 столбцов.
В настоящее время он не выпущен как окончательный продукт, но доступен для загрузки в виде бета-версии
с веб-сайта Microsoft.
Возможно, вы могли бы попробовать его, чтобы узнать, поможет ли он вам, но имейте в виду,
что это все еще БЕТА-версия.
Это было неизбежно. Что меня беспокоит, так это то, что при 65 000 строк и 256 столбцах
любые SUMIF, VLOOKUP и т. д. приводят к остановке книги. Конечно, в XL2007 есть
новый многопоточный механизм вычислений, который поможет, но такое количество данных
просто не должно быть в Excel, это крайне неэффективно и требует
нормализованной базы данных. , с Excel в качестве внешнего интерфейса для составления сводных отчетов.
На мой взгляд, мы пожалеем о большей сетке!
Вполне согласен.
Возможно, было глупо с моей стороны не указать в своем посте, что решение с базой данных
было бы гораздо предпочтительнее.
Я до сих пор не загрузил свою копию XL2007, чтобы хотя бы посмотреть, как ведет себя
вычисление в одном столбце из 1 миллиона строк — просто
черт возьми, а не потому, что я бы рассмотрите возможность использования его таким образом.
У нас на работе есть рабочая книга, расчет которой занимает около 30 секунд в 2003 году
и около 20 секунд в 2007 году, и это здорово, но это
связь между рабочими листами, а не совсем с использованием большого количества строк и столбцов.
Существует огромная разница между 16777216 ячеек (97>2003) и 17179869184
ячеек (2007). Я даже не уверен, что вы можете ввести 1 в каждое из них,
не достигнув предела памяти. пошли пробовать!
3 минуты и отображается как (не отвечает)
а вот и снова, у меня есть файл с более чем 65 536 строк, который
не подходит для размещения на одном листе, Excel может автоматически открывать
его или разбивать на разные листы в одном рабочая тетрадь.
--
Надеюсь, я помог вам.
Заранее благодарим за отзыв.
Франц Верга из Италии
Во-первых, спасибо, это сработало, однако у меня все еще есть одна проблема:
текстовый файл, экспортируемый в excel, содержит такие символы, как запятые и табуляции,
разделитель, следовательно, я также хочу процесс экспорта учитывать эти
символы и разделять их соответственно на разные столбцы вместо всех
записей, размещенных в одном столбце
Я все еще ищу вашей помощи.
Вы можете использовать утилиту "Текст в столбцы" (меню "Данные", "Текст в столбцы"), чтобы
разделить данные. Вы также можете использовать средство записи макросов (при использовании утилиты "Текст в
столбцы") для записи макроса, разделяющего импортированные данные.
--
(Я не уверен в названиях меню, опций и команд, потому что
перевожу с итальянской версии Excel. )
Надеюсь, я помог вам.
Заранее благодарим за отзыв.
Франц Верга из Италии
Небольшое примечание, чтобы передать мою признательность за вашу помощь, это действительно сработало
Информация о теме
Пользователи, просматривающие эту тему
В настоящее время эту тему просматривают 1 пользователь. (0 участников и 1 гость)
Как ограничить количество строк и столбцов на листе в Excel?
При совместном использовании диапазона рабочего листа с другими пользователями может потребоваться запретить другим пользователям просматривать другие строки и столбцы. Здесь я расскажу о некоторых хитрых способах легко ограничить количество строк и столбцов на листе в Excel.
- Ограничение количества строк и столбцов с отключением прокрутки в Excel
- Ограничение количества строк и столбцов со скрытыми строками и столбцами
- Ограничьте количество строк и столбцов с помощью замечательного инструмента
Ограничить количество строк и столбцов с отключенной прокруткой в Excel
В Excel пользователи могут установить область прокрутки, чтобы предотвратить прокрутку к другим строкам и столбцам.
Шаг 1. Щелкните правой кнопкой мыши имя рабочего листа на панели вкладок «Лист», а затем выберите «Просмотр кода» в контекстном меню.
Шаг 2. Теперь, когда вы попадаете в окно Microsoft Visual Basic для приложений, вам нужно открыть окно свойств, щелкнув Вид > Окно свойств.
Примечание. Если окно свойств уже открыто, просто пропустите этот шаг.
Шаг 3. В окне "Свойства" найдите ScrollArea на левой панели, а затем в следующем поле введите диапазон, которым хотите поделиться с другими.
Шаг 4. Закройте окно Microsoft Visual Basic для приложений.
Тогда вы увидите, что и вертикальная, и горизонтальная полосы прокрутки зафиксированы, и вы не можете прокручивать до других строк и столбцов на текущем листе.
Один щелчок, чтобы скрыть все, кроме выбранного диапазона (скрыть неиспользуемые столбцы/строки/ячейки)
В большинстве случаев мы можем использовать только часть рабочего листа с числовыми пустыми ячейками/диапазоном. Утилита Kutools for Excel «Установить область прокрутки» может помочь вам одним щелчком мыши скрыть все, кроме выбранного диапазона, или легко скрыть все неиспользуемые ячейки / строки / столбцы / диапазоны. Полнофункциональная бесплатная пробная версия на 30 дней!
Ограничение количества строк и столбцов со скрытыми строками и столбцами
Помимо настройки области прокрутки, мы также можем ограничить количество строк и столбцов, скрыв в Excel строки и столбцы, к которым не предоставлен общий доступ.
Шаг 1. Помимо общего диапазона, вам нужно выбрать левые диапазоны и скрыть их. Выберите первый столбец справа от общего диапазона, а затем одновременно нажмите клавиши Ctrl + Shift + стрелка вправо, чтобы выбрать все правые столбцы.
Шаг 2. Щелкните правой кнопкой мыши выбранные столбцы и выберите пункт «Скрыть» в контекстном меню.
Шаг 3. Выберите первую строку под общим диапазоном, нажмите одновременно клавиши Ctrl + Shift + клавиши со стрелкой вниз, чтобы выбрать все строки под общим диапазоном, и затем скройте их.
Шаг 4. Выберите первую строку вверх по общему диапазону, одновременно нажмите Ctrl+Shift+стрелка вверх, чтобы выбрать все строки вверх по общему диапазону, а затем скройте их.< /p>
Шаг 5. Выберите первый столбец слева от общего диапазона, одновременно нажмите Ctrl + Shift + стрелка влево, чтобы выбрать все столбцы слева от общего диапазона, а затем скройте их. .
Тогда вы увидите, что все строки и столбцы скрыты, кроме общего диапазона на листе.
Ограничьте количество строк и столбцов с помощью утилиты KTE Set Scroll Area
Горячие клавиши кажутся непростыми для многих пользователей Excel. Не беспокойтесь об этом, вы можете попробовать Kutools for ExcelУтилита «Установить область прокрутки», которая поможет вам скрыть все строки и столбцы, кроме общего диапазона, одним щелчком мыши в Excel.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
Выберите общий диапазон, которым вы хотите поделиться с другими, и нажмите Kutools > Показать / скрыть > Установить область прокрутки.
Тогда вы сразу увидите, что все строки и столбцы, кроме общего диапазона, скрыты на листе.
Установить область прокрутки - это одна из утилит Kutools for Excel, которая предназначена для одного щелчка, чтобы легко скрыть все невыбранные строки и столбцы на активном листе. Нажмите, чтобы получить 30-дневную бесплатную пробную версию без ограничений!
Демонстрация: ограничение количества строк и столбцов одним щелчком мыши
Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!
Переключите отображение/скрытие столбцов: один щелчок, чтобы показать все скрытые столбцы, и один щелчок, чтобы восстановить скрытые столбцы
Утилита Kutools for Excel's (Показать) Столбцы - это хороший переключатель, чтобы показать все скрытые столбцы одним щелчком мыши, и щелчок по этой кнопке столбца во второй раз снова сделает эти столбцы скрытыми. Полнофункциональная бесплатная пробная версия на 30 дней!
При работе с Excel есть небольшие задачи, которые необходимо выполнять довольно часто. Знание «правильного пути» может сэкономить вам много времени.
Одной из таких простых (но часто необходимых) задач является нумерация строк набора данных в Excel (также называемая порядковыми номерами в наборе данных).
Теперь, если вы думаете, что один из способов — просто ввести эти серийные номера вручную, что ж, вы правы!
Но это не лучший способ.
Представьте, что у вас есть сотни или тысячи строк, для которых вам нужно ввести номер строки. Это было бы утомительно и совершенно не нужно.
Существует множество способов нумерации строк в Excel, и в этом руководстве я расскажу о некоторых из них, которые я рекомендую и часто использую.
Конечно, их будет больше, и я буду ждать — с кофе — в области комментариев, чтобы услышать от вас об этом.
Это руководство охватывает:
Как пронумеровать строки в Excel
Наилучший способ нумерации строк в Excel зависит от типа имеющегося у вас набора данных.
Например, у вас может быть непрерывный набор данных, начинающийся со строки 1, или набор данных, начинающийся с другой строки. Или у вас может быть набор данных с несколькими пустыми строками, и вы хотите пронумеровать только заполненные строки.
Вы можете выбрать любой из методов, которые работают на основе вашего набора данных.
1] Использование маркера заполнения
Маркер заполнения определяет шаблон из нескольких заполненных ячеек и может легко использоваться для быстрого заполнения всего столбца.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги для быстрой нумерации строк с помощью маркера заполнения:
Обратите внимание, что дескриптор заполнения автоматически определяет шаблон и заполняет оставшиеся ячейки этим шаблоном. В этом случае закономерность заключалась в том, что числа увеличивались на 1.
Если в наборе данных есть пустая строка, дескриптор заполнения будет работать только до последней непрерывной непустой строки.
Кроме того, обратите внимание: если у вас нет данных в соседнем столбце, двойной щелчок маркера заполнения не сработает. Однако вы можете поместить курсор на маркер заполнения, удерживать правую клавишу мыши и тянуть вниз. Он заполнит ячейки, охваченные перетаскиванием курсора.
2] Использование серий заполнения
Хотя дескриптор заполнения — это быстрый способ нумерации строк в Excel, функция "Заполнить ряд" дает гораздо больше контроля над вводом чисел.
Предположим, у вас есть набор данных, как показано ниже:
Вот как использовать Fill Series для нумерации строк в Excel:
Это мгновенно пронумерует строки от 1 до 26.
Использование «Заполнить серию» может быть полезно, если вы начинаете с ввода номеров строк.В отличие от указателя заполнения, он не требует, чтобы соседние столбцы уже были заполнены.
Даже если на листе ничего нет, функция «Заполнить серию» все равно будет работать.
Примечание. Если в середине набора данных есть пустые строки, функция "Заполнить ряд" все равно заполнит номер этой строки.
3] Использование функции ROW
Вы также можете использовать функции Excel для нумерации строк в Excel.
В приведенных выше методах Fill Handle и Fill Series вставляемый серийный номер является статическим значением. Это означает, что если вы переместите строку (или вырежете и вставите ее в другое место в наборе данных), нумерация строк не изменится соответствующим образом.
Этот недостаток можно устранить с помощью формул в Excel.
Вы можете использовать функцию СТРОКА, чтобы получить нумерацию строк в Excel.
Чтобы получить нумерацию строк с помощью функции СТРОКА, введите следующую формулу в первую ячейку и скопируйте ее для всех остальных ячеек:
Функция ROW() возвращает номер текущей строки. Поэтому я вычел из него 1, поскольку начал со второй строки и далее. Если ваши данные начинаются с 5-й строки, вам нужно использовать формулу =СТРОКА()-4.
Лучшее в использовании функции ROW то, что она не испортит нумерацию, если вы удалите строку в своем наборе данных.
Поскольку функция ROW не ссылается ни на одну ячейку, она автоматически (или, лучше сказать, автоматически) подстроится, чтобы дать вам правильный номер строки. Примерно так, как показано ниже:
Обратите внимание, что как только я удаляю строку, номера строк автоматически обновляются.
Опять же, это не будет учитывать пустые записи в наборе данных. Если у вас есть пустые строки, все равно будет отображаться номер строки.
Вы можете использовать следующую формулу, чтобы скрыть номер строки для пустых строк, но она все равно не будет корректировать номера строк (таким образом, что следующий номер строки будет назначен следующей заполненной строке).
4] Использование функции COUNTA
Если вы хотите пронумеровать строки таким образом, чтобы только заполненные строки получали порядковый номер, то этот метод подходит вам.
Он использует функцию COUNTA, которая подсчитывает количество непустых ячеек в диапазоне.
Предположим, у вас есть набор данных, как показано ниже:
Обратите внимание, что в показанном выше наборе данных есть пустые строки.
Вот формула, которая будет нумеровать строки без нумерации пустых строк.
Функция ЕСЛИ проверяет, пуста ли соседняя ячейка в столбце B. Если он пуст, он возвращает пустое значение, но если это не так, он возвращает количество всех заполненных ячеек до этой ячейки.
5] Использование ПРОМЕЖУТОЧНЫХ ИТОГОВ для отфильтрованных данных
Иногда у вас может быть огромный набор данных, в котором вы хотите отфильтровать данные, а затем скопировать и вставить отфильтрованные данные на отдельный лист.
Если вы до сих пор использовали любой из методов, показанных выше, вы заметите, что номера строк остались прежними. Это означает, что при копировании отфильтрованных данных вам придется обновить нумерацию строк.
В таких случаях функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может автоматически обновлять номера строк. Даже если вы отфильтруете набор данных, номера строк останутся без изменений.
Позвольте мне показать вам, как это работает, на примере.
Предположим, у вас есть набор данных, как показано ниже:
Если я отфильтрую эти данные на основе продаж Продукта А, вы получите нечто, показанное ниже:
Обратите внимание, что серийные номера в столбце A также фильтруются. Так что теперь вы видите только числа для видимых строк.
Хотя это ожидаемое поведение, если вы хотите получить порядковую нумерацию строк, чтобы можно было просто скопировать и вставить эти данные куда-нибудь еще, вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Вот функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая гарантирует, что даже отфильтрованные данные имеют непрерывную нумерацию строк.
3 в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ указывает на использование функции СЧЁТЗ. Второй аргумент — это диапазон, к которому применяется функция COUNTA.
Преимущество функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ заключается в том, что она динамически обновляется при фильтрации данных (как показано ниже):
Обратите внимание, что даже при фильтрации данных нумерация строк обновляется и остается непрерывной.
6] Создание таблицы Excel
Таблица Excel — отличный инструмент, который необходимо использовать при работе с табличными данными. Это значительно упрощает управление данными и их использование.
Это также мой любимый метод среди всех методов, показанных в этом руководстве.
Позвольте сначала показать вам, как правильно нумеровать строки с помощью таблицы Excel:
Обратите внимание, что в приведенной выше формуле я использовал Table2, так как это имя моей таблицы Excel. Вы можете заменить Table2 именем имеющейся у вас таблицы.
Есть некоторые дополнительные преимущества использования таблицы Excel при нумерации строк в Excel:
- Поскольку таблица Excel автоматически вставляет формулу во весь столбец, она работает, когда вы вставляете новую строку в таблицу. Это означает, что когда вы вставляете/удаляете строки в таблице Excel, нумерация строк будет автоматически обновляться (как показано ниже).
- Если вы добавите к данным дополнительные строки, таблица Excel автоматически расширится, чтобы включить эти данные как часть таблицы. А поскольку формулы автоматически обновляются в вычисляемых столбцах, будет вставлен номер строки для новой вставленной строки (как показано ниже).
7] Добавление 1 к номеру предыдущей строки
Это простой метод, который работает.
Идея состоит в том, чтобы добавить 1 к номеру предыдущей строки (число в ячейке выше). Это гарантирует, что последующие строки получат число, увеличенное на 1.
Предположим, у вас есть набор данных, как показано ниже:
Вот шаги для ввода номеров строк с помощью этого метода:
- В ячейке в первой строке введите 1 вручную. В данном случае это ячейка A2.
- В ячейку A3 введите формулу =A2+1
- Скопируйте и вставьте формулу во все ячейки столбца.
Вышеуказанные шаги позволят ввести серийные номера во все ячейки столбца. Если есть какие-либо пустые строки, для них все равно будет вставлен номер строки.
Также обратите внимание, что если вы вставите новую строку, номер строки не обновится. Если вы удалите строку, во всех ячейках ниже удаленной строки будет отображаться ошибка ссылки.
Вот несколько быстрых способов вставки серийных номеров в табличные данные в Excel.
Если вы используете какой-либо другой метод, поделитесь им со мной в разделе комментариев.
Как увеличить значение, число, процент и т. д. через каждые несколько строк в Excel.
В этом учебном пособии вы узнаете, как построить формулу, необходимую для увеличения значения или, на самом деле, сделать что-нибудь еще для каждого такого количества строк в Excel.
Раздел:
Формула, которая определяет, в какой строке вы находитесь в Excel
Это суть процесса, позволяющая узнать, что вы находитесь в каждой 3-й, 5-й или 25-й строке набора данных. В приведенном ниже примере мы хотим воздействовать на каждую 3-ю строку.
Это функция, которую мы будем использовать для этого:
На данный момент это будет просто выводить "Привет" в каждой третьей строке, и это выглядит так:
Чтобы изменить это на свой набор данных, просто измените 3 на любое количество строк, которое вы хотите для приращения. Убедитесь, что ваш набор данных начинается с строки 1, иначе это не сработает. Если его нет в строке 1, скопируйте набор данных на новую вкладку, где он может начинаться с строки 1 (если вы не можете этого сделать, я покажу вам, как настроить его ниже).
Как это работает
Давайте разберем формулу, если хотите понять, как она работает. Лучший способ начать работу с Excel — с нуля.
ROW() эта функция сообщает вам текущую строку, в которой находится формула. Если мы находимся в строке 1, она возвращает 1, строка 2, она возвращает 2 и т. д.
MOD() Мы используем функцию MOD, чтобы сказать нам, находимся ли мы в третьей строке или нет, путем деления номера текущей строки, полученного из функции ROW, на число 3, которое является вторым аргументом в функции MOD. . Мы можем изменить 3 на что угодно: 5, чтобы делать что-то каждые 5 строк, 10, чтобы делать что-то каждые 10 строк и т. д.
Когда функция MOD выполняет это деление, она возвращает остаток, если он существует. Если вы разделите число на три и остатка не будет, функция ОСТАТ вернет ноль, и вы знаете, что мы снова находимся в третьей строке. Если функция ОСТАТ возвращает что-либо, кроме нуля, мы не находимся в третьей строке. Это может показаться запутанным, но это основная математическая концепция; если номер текущей строки делится на три без каких-либо оставшихся десятичных знаков, то функция вернет ноль.
Затем мы используем оператор IF, чтобы проверить, равна ли функция MOD нулю или нет. Если он равен 0, то мы знаем, что находимся в правильном ряду, чтобы что-то сделать.
Если все это звучит запутанно, не волнуйтесь, потому что это ДЕЙСТВИТЕЛЬНО запутанно! Это довольно сложная формула, основанная на паре редко используемых функций и некоторых более сложных приемах в Excel. Чем больше вы используете Excel и вложенные функции, тем легче вам будет понимать такие формулы.
Как увеличить значение в зависимости от того, в какой строке вы находитесь в Excel
Теперь, когда у нас есть формула, которая сообщит нам, когда нам нужно сделать приращение, нам нужно сделать приращение.
В этом примере я просто увеличу число на единицу, чтобы проиллюстрировать, как вы это делаете. Однако вы можете использовать любой тип приращения, математического уравнения или функции, чтобы делать все, что вам нужно.
Вот что мы получим:
Столбец C содержит окончательную формулу.
Во-первых, вам нужно заполнить первую ячейку столбца числом или значением, которое вы вводите вручную. Здесь я поставил 1. Мы делаем это, потому что в противном случае потребуется гораздо более сложная формула для учета первой строки в наборе данных, когда мы можем просто ввести 1 вручную и готово.
Теперь нам нужно понять, что мы хотим сделать. Я хочу увеличить это значение, которое находится в первой ячейке, на единицу каждые три строки. Если мы не в «третьем ряду», то я хочу повторить последний номер.
Для этого мы заполняем последние два аргумента оператора IF, который мы сделали выше; вот результат:
Помните, что это идет во вторую строку.
В приведенной выше формуле мы заменили "Hi" на C1+1 и "" на C1.
Это базовая часть оператора IF, и на самом деле здесь нет ничего слишком сложного.
Измените эти ссылки на ячейки, чтобы они работали с вашими данными, и все.
Как упоминалось ранее, вы можете делать здесь все, что захотите; вам не нужно просто увеличивать значение; выполнить любые расчеты, которые вам нужно сделать здесь.
Набор данных не в строке 1
Если ваш набор данных не начинается в строке 1, вам нужно будет изменить приведенную выше формулу следующим образом:
Обратите внимание на -2. Вы должны вычесть значение из функции ROW(), чтобы заставить ее работать, когда ваш набор данных не начинается в строке 1.
В этом случае набор данных начинается в строке 3, поэтому мы вычитаем 2; если бы он был в строке 5, мы бы вычли 4. Вы в основном смещаете расчет, поэтому он будет действовать так, как будто он начинается в строке 1.
Окончательная формула, которую мы использовали выше, будет выглядеть так:
Вот как результат выглядит в Excel:
Примечания
Это может показаться запутанным, но вам не нужно знать, как все работает, чтобы иметь возможность использовать это для своей электронной таблицы; просто следуйте приведенным выше инструкциям, и вы сможете настроить его под свои нужды, внеся всего несколько изменений. Однако здесь нет ничего чрезмерно сложного, просто вложите несколько функций, которые вы, возможно, раньше не видели, и проверьте, равны ли они нулю, и поместите все это в оператор IF.
Обязательно загрузите электронную таблицу, прилагаемую к этому руководству, чтобы увидеть эти примеры в действии и изменить их в соответствии со своими потребностями.
Читайте также: