У Excel есть ограниченное количество элементов

Обновлено: 21.11.2024

У меня есть подписка на Office 365, поэтому я использую последнюю версию программного обеспечения MS Office. У меня есть электронная таблица Excel, и я создал раскрывающийся список, но он работает неправильно.

1) У меня есть 100 000 имен, перечисленных в столбце раскрывающегося списка. Таким образом, есть 100 000 возможных вариантов, но когда я пытаюсь выбрать имя из списка в столбце с помощью селектора списка, я могу прокрутить вниз только до первых 32 000 имен. Остальная часть списка не видна, и я больше не могу прокручивать, чтобы увидеть больше записей. Эта проблема вызвана максимальным количеством имен в 32 000 в Excel? Или есть лучший способ создать список, из которого можно выбирать в других ячейках, чтобы у вас были сотни тысяч потенциальных вариантов?

2) Другая проблема с этой электронной таблицей Excel заключается в том, что я могу назначить этот раскрывающийся список только одному столбцу на одном листе. Я попытался создать второй столбец на другом листе той же книги Excel, но он не работает. Итак, мой вопрос: есть ли ограничение в один столбец для раскрывающихся списков? Или эта проблема также вызвана тем, что список из 100 000 имен превышает максимальное значение для 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 дней!

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Симптомы

При работе с файлом Microsoft Excel вы получаете одно из следующих сообщений:

  • Excel не может выполнить эту задачу с доступными ресурсами. Выберите меньше данных или закройте другие приложения.
  • Недостаточно памяти
  • Недостаточно системных ресурсов для полного отображения
  • Недостаточно памяти для выполнения этого действия. Попробуйте использовать меньше данных или закройте другие приложения. Чтобы увеличить доступность памяти, рассмотрите:
    • Использование 64-разрядной версии Microsoft Excel.
    • Добавление памяти на ваше устройство.

    Причина

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

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

    Если вы не сталкиваетесь с ограничением ресурсов, это наиболее распространенные решения.

    Разрешение

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

    Способ 1. Содержимое электронной таблицы

    • Вставка строки или столбца
    • Сортировка
    • Выполнение расчетов
    • Копировать и вставлять
    • Открытие и закрытие книги
    • Запуск VBA

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

    Расчеты

    Если вы вставляете строки и столбцы, выполняете вычисления или копируете, вставляете и получаете сообщение, оно может быть привязано к формулам, требующим пересчета.

    Рассмотрите следующий сценарий:

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

    В следующих статьях рассказывается, как оптимизировать производительность с помощью вычислений:

    Другие элементы электронной таблицы

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

    Пользовательские представления в общей книге

    Если вы используете функцию «Общая рабочая книга» («Лента просмотра» > «Поделиться рабочей книгой»), очистка пользовательских представлений может помочь с доступной памятью. Для этого:

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

    Если проблема не решена после очистки файла, перейдите к способу 2.

    Способ 2. Проверка/установка последних обновлений

    Возможно, вам придется настроить Центр обновления Windows на автоматическую загрузку и установку рекомендуемых обновлений. Установка любых важных, рекомендуемых и необязательных обновлений часто может исправить проблемы, заменив устаревшие файлы и устранив уязвимости. Чтобы установить последние обновления Office, щелкните ссылку, относящуюся к вашей версии Windows, и следуйте инструкциям в этой статье.

    Обновления операционной системы:

    Обновления Office:

    Дополнительную информацию об обновлениях Office см. в разделе Загрузка и обновления Office.

    Если проблема не решена после установки обновлений, перейдите к способу 3.

    Способ 3. Надстройки мешают

    Проверьте работающие надстройки и попробуйте отключить их, чтобы убедиться, что Excel работает правильно. Следуйте инструкциям в следующей статье, чтобы отключить надстройки.

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

    Если проблема не решена после удаления надстроек, перейдите к способу 4.

    Способ 4. Проверьте отключение панели предварительного просмотра/деталей в Windows 7

    Если вы используете Windows 7, попробуйте отключить панели предварительного просмотра и сведений в Windows. Вам придется отключить их в трех местах.

    1. Нажмите правой кнопкой мыши кнопку "Пуск".
    2. Нажмите «Открыть проводник».
    3. Нажмите Организовать | Макет.
    4. Снимите флажки с панели сведений и панели предварительного просмотра.
    1. Нажмите Файл | Открыть.
    2. Нажмите Организовать | Макет.
    3. Снимите галочки с панели сведений и панели предварительного просмотра Outlook.
    4. Откройте новое электронное письмо.
    5. В группе "Включить" на ленте нажмите "Прикрепить файл".
    6. Нажмите Организовать | Макет.
    7. Снимите флажки с панели сведений и панели предварительного просмотра.

    Если проблема не решена после отключения панелей предварительного просмотра и сведений, перейдите к способу 5.

    Способ 5. Проверьте другой принтер по умолчанию

    При запуске Excel использует принтер по умолчанию для отображения файла. Попробуйте протестировать с помощью «Microsoft XPS Document Writer» в качестве принтера по умолчанию и посмотрите, будет ли ошибка по-прежнему появляться. Для этого выполните следующие действия:

    1. Закройте Excel.
    2. Откройте окно "Принтер и факсы" (XP) или "Устройства и принтеры" (Vista, 7, 8, 10).
    3. Щелкните правой кнопкой мыши принтер "Microsoft XPS Document Writer".
    4. Нажмите "Установить по умолчанию".

    Если проблема не решена после смены принтера по умолчанию, перейдите к способу 6.

    Способ 6. Проверка без антивируса

    Антивирус иногда может вызывать проблемы, постоянно пытаясь сканировать файл Excel или что-то в этом файле. Во многих случаях ошибка памяти будет случайной и будет обнаружена в любой электронной таблице. Проверьте это, временно отключив антивирус или не сканируя файлы Excel. В некоторых случаях антивирус необходимо удалить.

    Если проблема не устранена после отключения антивируса, перейдите к способу 7.

    Способ 7. Проверка с 64-разрядной версией Excel

    При работе с большими файлами Excel может использоваться память, доступная 32-разрядному приложению Excel. В любом 32-разрядном приложении существует ограничение в 2 ГБ.

    Если ваши процессы должны использовать более 2 ГБ, вам следует тщательно рассмотреть вопрос о переходе на 64-разрядную версию Microsoft Excel. 64-разрядная версия позволит использовать всю доступную физическую оперативную память на машине. Если вы заинтересованы в изучении 64-разрядных версий, см. 64-разрядные версии Office 2013.

    Если ваша проблема не решена после тестирования на 64-разрядной версии, перейдите к способу 8.

    Способ 8. Другие приложения используют память компьютера, а для Excel выделяется недостаточно

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

    Дополнительная информация

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

    В этом видеоруководстве показано, как использовать функцию СЧЕТЕСЛИ для подсчета количества ячеек, содержащих определенную строку текста, например "Перо". Вы также можете найти текст, который является частью ячейки — сколько заказов было размещено на какой-либо вид ручек, например «гелевая ручка», «ручка» или даже «карандаш»?

    Под видео есть письменные инструкции, а расшифровку видео можно прочитать внизу этой страницы.

    Функция СЧЁТЕСЛИ в Excel

    В Microsoft Excel вы можете использовать функцию СЧЕТЕСЛИ для подсчета ячеек, соответствующих одному критерию.

    Примечание. Чтобы подсчитать количество ячеек по нескольким критериям, используйте функцию Excel СЧЁТЕСЛИМН.

    Аргументы СЧЁТЕСЛИ

    В синтаксисе функции СЧЁТЕСЛИ есть 2 обязательных аргумента:

    1. диапазон — ячейки для проверки критериев
    2. criteria – критерии для соответствия – введите формулу или укажите ссылку на ячейку.

    Предупреждения СЧЁТЕСЛИ

    Вот несколько предупреждений об ограничениях функции СЧЁТЕСЛИ, прежде чем использовать эту функцию в электронной таблице Excel.

    • Числа в числах. Функция СЧЁТЕСЛИ не может считать числа в числах.
      • Например, он не может подсчитать число 123, если значением ячейки является число 91 236, даже если это действительное число содержит строку 123.
      • Вместо этого используйте одну из других формул, которые будут подсчитывать числа внутри чисел.
      • СЧЕТЕСЛИ сопоставляют текстовые числа, например "00123", с действительными числами, например 123.
      • Это может привести к ложному подсчету дублирующегося значения.
      • Посмотрите другую формулу, использующую функцию СУММПРОИЗВ, которая поможет избежать этой проблемы.

      Точное соответствие определенному тексту

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

      Выполните следующие действия, чтобы создать формулу СЧЁТЕСЛИ для подсчета ручек:

      1. Выберите ячейку для формулы (в данном примере это ячейка D4).
      2. Введите знак равенства (=), чтобы начать формулу.
      3. Тип: СЧЁТЕСЛИ(
      4. Выберите диапазон ячеек, в которых перечислены элементы — в этом примере ячейки A2:A10
        • ПРИМЕЧАНИЕ. Если ваш список находится в именованной таблице Excel, в формуле будут отображаться имя таблицы и имя столбца -- =COUNTIF(tblExact[Item]
      5. Введите запятую, чтобы разделить аргументы
      6. Введите значение, которое вы хотите сопоставить. В этом примере значением является текст, заключенный в двойные кавычки: "Pen"
        • Примечание. Критерии можно использовать как в верхнем, так и в нижнем регистре – СЧЁТЕСЛИ регистр не учитывается
      7. Введите закрывающую скобку и нажмите Enter.

      Завершенная формула СЧЁТЕСЛИ

      Для именованной таблицы заполненная формула показывает имя таблицы (tblExact) и имя столбца (Item):

      и для списка рабочих листов формула показывает ссылку на ячейку:

      На этом снимке экрана показан результат приведенной выше формулы в ячейке D4. Есть 4 заказа с точным соответствием по критерию "ручка".

      Сопоставить любой текст в ячейке

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

      • Примечание. СЧЁТЕСЛИ не может найти числа среди действительных чисел.

      Как и в предыдущем примере, мы будем считать заказы "Pen".На этот раз формула будет содержать подстановочные знаки звездочки (*) до и после текстовой строки. Этот подстановочный знак означает любое количество неизвестных символов или отсутствие символов.

      В результате будут учитываться все заказы на ручки, гелевые ручки и карандаши, поскольку они содержат строку «ручка».

      1. Выберите ячейку для формулы (в данном примере это ячейка D4).
      2. Введите знак равенства (=), чтобы начать формулу.
      3. Тип: СЧЁТЕСЛИ(
      4. Выберите ячейки, в которых перечислены элементы — в этом примере ячейки A2:A10
        • ПРИМЕЧАНИЕ. Если ваш список находится в именованной таблице Excel, в формуле будут отображаться имя таблицы и имя столбца -- =COUNTIF(tblExact[Item]
      5. Введите запятую, чтобы разделить аргументы
      6. В двойных кавычках введите значение, которое вы хотите сопоставить, со звездочкой (*) подстановочным знаком до и после текста: "*Pen*"
        • Примечание. Можно использовать как верхний, так и нижний регистр — СЧЁТЕСЛИ регистр не учитывается
      7. Введите закрывающую скобку и нажмите Enter.

      Завершенная формула с подстановочными знаками

      Для именованной таблицы заполненная формула показывает имена таблицы и столбца:

      и для списка рабочих листов формула показывает ссылку на ячейку:

      Использовать ссылки на ячейки

      Чтобы сделать ваши формулы более гибкими и простыми в обслуживании, вы можете ввести текст критерия, который вы хотите подсчитать, в ячейку листа Excel.

      Затем в формуле укажите ячейку, в которую вы ввели текст критерия.

      Например, вот формула для сопоставления определенного элемента со ссылкой на ячейку E7, где был введен текст критерия "ручка".

      Вы также можете использовать ссылку на ячейку с подстановочными знаками. Используйте оператор & (амперсанд), чтобы соединить подстановочные знаки со ссылкой на ячейку.

      Расшифровка видео – подсчет определенных элементов

      ТЕХНОЛОГИЯ ВИДЕО

      В Excel у нас есть список заказов на продажу, и мы хотели бы подсчитать, сколько заказов было ручкой в ​​заказе. мой список от A1 до A10, и я хотел бы посчитать перо в этом списке.

      для этого я могу использовать функцию СЧЁТЕСЛИ. в этой ячейке я начинаю со знака равенства, набираю СЧЁТЕСЛИ и открывающую скобку.

      Первый аргумент

      Первый аргумент — это диапазон, который я хочу проверить.

      Я выберу от A1 до A10.

      Я поставлю запятую, чтобы закончить этот аргумент.

      Второй аргумент

      Следующий аргумент — это критерии. Что именно я хочу, чтобы он учитывал?

      Я ищу текстовую строку, поэтому в двойных кавычках я набираю двойную кавычку, а затем перо, закрывающую кавычку и закрывающую скобку.

      Когда я нажал Enter, в этом списке было найдено 4 элемента пера.

      Итак, 1, 2, 3, 4, он нашел все 4 элемента пера.

      Название элемента содержит «ручка»

      Если у нас есть на другом листе, тот же список. Вот у меня ручка, карандаш, гелевая ручка.

      Возможно, я хотел бы найти что-нибудь, в названии которого есть ручка.

      Поэтому не совсем ручка, я не хочу считать только ручки, но я хотел бы включить гелевую ручку и даже карандаш.

      Запустить формулу СЧЁТЕСЛИ

      Здесь снова я буду использовать СЧЁТЕСЛИ.

      • Я начну со знака равенства, СЧЕТЕСЛИ и открытой скобки, а затем диапазон будет от A1 до A10, введите запятую.
      • Для этого я хочу, чтобы он включал все, что содержит перо.
      • Я наберу свою двойную кавычку, а затем звездочку.
      • На моей клавиатуре это был Shift 8.
      • Это подстановочный знак, который представляет любое количество символов или отсутствие символов.
      • Затем текст, который я ищу. Я могу использовать верхний или нижний регистр здесь
      • Еще одна звездочка, еще одна двойная кавычка и закрывающая скобка.

      Он будет искать все, что содержит буквы P-E-N в строке.

      • До этого может быть что угодно или ничего.
      • И после этих трех букв может быть что угодно или ничего.

      Результат формулы

      И когда я нажимаю Enter, мы получаем 6. Итак, один, два, три, четыре, пять, шесть.

      Он нашел все, что содержало эту строку символов, P-E-N и регистр не имели значения.

      Получить образец файла

      Чтобы просмотреть все примеры СЧЁТЕСЛИ, загрузите пример книги "Подсчёт конкретных элементов". Заархивированный файл имеет формат xlsx и не содержит макросов.

      Читайте также: