Что означает, что формула не закрывает соседние ячейки в Excel

Обновлено: 20.11.2024

В Excel 2010 или любой другой версии, вероятно, если я ввожу в ячейку длинный однострочный текст, длина которого превышает ширину ячейки, Excel иногда отображает текст в следующих соседних ячейках; в других случаях он обрезается на границе с соседней ячейкой справа.

Я хотел бы знать, как Excel решает, что делать, чтобы я мог лучше контролировать свои макеты. Обратите внимание, что я не хочу использовать ячейки слияния, так как иногда это неуместно. Кроме того, я уже пробовал форматирование «Очистить все» для всех затронутых ячеек, но, похоже, мало что показывает.

Есть идеи? Спасибо.

Если что-то появится после 20 апреля 2012 года, на этот вопрос все еще нет ответа. Я не нашел последовательного поведения к этому. Если вы знаете ответ, пожалуйста, напишите, и я поставлю галочку на него. Спасибо.

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

13 ответов 13

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

В ячейке не включена функция "Перенос текста".
Ячейка не является объединенной.
Ячейка содержит значение, превышающее ширину ячейки
Смежная ячейка пуста* и не является объединенной ячейкой

Ячейка имеет любое из следующих горизонтальных выравниваний:

Общие
Слева (отступ)
Центр
Вправо (отступ)
Отцентрировать по выделенному
(Право перекрывает ячейку слева; центр перекрывается с обеих сторон.)

Содержимое ячейки не поворачивается (т. е. для ориентация установлено значение 0°) _{(только для Excel 2010?)}

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

_{*В определенных обстоятельствах соседняя ячейка может казаться пустой, но не быть таковой, и в этом случае текст не будет перетекать в эту ячейку, потому что это не так. действительно пустой. Например, если соседняя ячейка содержит формулу, преобразующуюся в "" , она не пуста.}

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

Спасибо :) Я наткнулся на вопрос из поиска Google и был удивлен: А) количеству ответов, Б) сколько ответов немного отличается от того, что было задано, включая поздний ответ с наивысшей оценкой, C) что ни один из ответов не был полным, в контексте вопроса «полное объяснение того, как excel определяет, следует ли переполнять ячейку».

Кажется, я только что обнаружил еще одно условие: текст вообще нельзя поворачивать, чтобы он перетекал в следующую ячейку. Как только ориентация отличается от 0°, текст обрезается по границам ячейки. (т. е. без вертикального переполнения текста)

@MarcusMangelsdorf В Excel 2016, если я установил ориентацию на 1°, текст не обрезается на границе ячейки.

@MetaEd: Вы абсолютно правы, я могу воспроизвести переполнение текста с повернутым текстом в Excel 2013. Это еще более безумно, потому что текст будет переполняться, даже если соседняя ячейка не будет пустой, если ориентация != 0° . Подводя итог: этот ответ может быть полностью правильным только для Excel 2010.

Я столкнулся с такой же проблемой, и она применима только тогда, когда значение в ячейке представляет собой все числовые символы (включая десятичную точку). Если значение в ячейке содержит какие-либо буквенные символы, оно правильно расширяется в соседние ячейки. , но когда значение только числовое, это не так. Это справедливо даже в том случае, если вы измените формат ячеек на Текст после того, как число было введено в ячейку.

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

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

В дополнение к случаям, отмеченным в других ответах, следует отметить, что Excel всегда будет обрезать переполнение текста, если на листе активен параметр «Показать формулы» ( Ctrl + ` ). Если вы регулярно используете сочетание клавиш Ctrl + 1 для доступа к диалоговому окну «Формат ячеек», очень легко случайно активировать «Показать формулы», и вы можете не заметить, что это произошло. Это особенно верно, если у вас на самом деле нет никаких формул на активном листе.

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

Кнопка «Очистить все» в «Excel 2010 > домашняя страница > группа редактирования > раскрывающийся список «очистить» > очистить все» также очищает содержимое. т.е. все очищенные ячейки должны быть пустыми, но все же иногда не переполняется.

Я не рекомендую использовать очистить все (если вы не хотите потерять свою работу!). Excel (по крайней мере, 2013 г. - не уверен насчет более ранних версий - хотя я почти уверен, что 2000 г. не действовал таким образом) распознает пустую строку (т.е. "") как значение, и поэтому даже если у вас есть "пустая" соседняя ячейка , ваши данные не будут перенесены в эту ячейку.

В ячейке требуется значение NULL. Думаю, это было принято в Excel, чтобы лучше соответствовать базам данных, где пустые строки и значения NULL — это очень разные вещи.

Если выделить ячейку и нажать кнопку "Удалить", в нее фактически будет помещено нулевое значение. Однако если у вас большая электронная таблица или по какой-то причине ячейки вашей электронной таблицы часто заполняются пустыми строками, то быстрое решение — написать макрос, который проверяет пустые строки ("") и заменяет их нулями.

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

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

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

<р>1. Выберите пустую ячейку, введите формулу =СРЗНАЧ(ЕСЛИ(ОСТАТ(СТОЛБЦ(C2:G2)-СТОЛБЦ(C2),2)=0,ЕСЛИ(C2:G2,C2:G2))) в строку формул, а затем одновременно нажмите клавиши Ctrl + Shift + Enter.

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

Быстрый выбор интервальных (несмежных) строк или столбцов в указанном диапазоне в Excel:

Функция Kutools for Excel «Выбрать интервальные строки и столбцы» может помочь вам легко выбрать интервальные строки или столбцы в указанном диапазоне в Excel, как показано на снимке экрана ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная пробная версия)

Среднее значение несмежных ячеек, исключая нули, с помощью Kutools for Excel

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

<р>1. Выберите таблицу, которая начинается с данных первого несмежного столбца, который вы включите в свой расчет. В данном случае мы выбираем диапазон C2:G7. Затем нажмите Kutools > Выбрать > Выбрать интервальные строки и столбцы. Смотрите скриншот:

<р>2. В диалоговом окне "Выбор строк и столбцов интервала" выберите "Столбцы" в разделе "Выбрать" и в разделе "Параметры" введите число 1 отдельно в поле "Интервал и столбцы", а затем нажмите кнопку "ОК".

Примечание. В поле "Интервал" введите номер интервала, основанный на интервале ваших несмежных ячеек.

<р>3. После нажатия кнопки «ОК» все несмежные ячейки в выбранном диапазоне сразу выделяются. Чтобы исключить нулевые ячейки при расчете среднего, нажмите Kutools > Выбрать > Выбрать определенные ячейки. Смотрите скриншот:

<р>4. В диалоговом окне «Выбор определенных ячеек» выберите параметр «Ячейка» в разделе «Тип выбора», затем выберите параметр «Не равно» и введите число 0 в следующее текстовое поле в разделе «Особый тип». И, наконец, нажмите кнопку ОК.

<р>5. Затем появится диалоговое окно, чтобы сообщить вам, сколько ячеек выбрано, нажмите кнопку «ОК».Теперь выбираются все несмежные ячейки в указанном диапазоне, исключая все нулевые ячейки, и вы можете увидеть средний результат, отображаемый в строке состояния книги.

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

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

Или, если вы работаете с клавиатурой, вы можете удерживать клавишу Shift и перемещать курсор, чтобы сделать выбор.

Но оба эти метода работают, когда вы хотите выбрать непрерывный диапазон ячеек.

Но что, если вы хотите выделить несмежные ячейки в Excel?

Вы тоже можете сделать это... это не так просто, как выделение соседних ячеек, но все же достаточно просто.

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

Итак, приступим!

Это руководство охватывает:

Выделение несмежных ячеек с помощью мыши

Проще всего выделить несмежные диапазоны с помощью мыши (вместе с клавиатурой).

Ниже показано, как выбрать два несмежных диапазона ячеек:

Нажмите на первую ячейку, которую хотите выделить. Теперь она станет активной ячейкой.
Удерживайте клавишу Control на клавиатуре.
Щелкните левой кнопкой мыши и перетащите, чтобы сделать выбор.
Оставьте щелчок мыши. На этом этапе у вас будет выбор. Продолжайте удерживать клавишу Control.
Поместите курсор на вторую ячейку/диапазон, который вы хотите выделить.
Щелкните левой кнопкой мыши и перетащите, чтобы сделать выбор.
Оставьте щелчок мыши. Это выберет два несмежных диапазона ячеек.
Отпустите клавишу Control

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

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

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

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

Выберите несмежные ячейки/диапазоны с помощью поля имени

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

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

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

Например, предположим, что вы хотите выбрать следующие ячейки — A1, C12, D20, K14

Ниже показано, как выбрать эти несмежные ячейки с помощью поля имени:

Нажмите на поле имени. Это поместит курсор в поле имени.
Введите различные ячейки/диапазоны, которые вы хотите выбрать (через запятую при выборе нескольких диапазонов).
Нажмите клавишу Enter.

Это мгновенно выделит все указанные ячейки.

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

Вы также можете использовать поле имени для выбора несмежных диапазонов.

Например, предположим, что вы хотите выбрать следующие три диапазона — A1:A20,C1:C20,F1:F20, вы можете использовать это в поле имени.

Одна из областей, в которой использование окна имени для выбора отлично работает, — это когда у вас есть именованные диапазоны. Например, предположим, что у вас есть диапазоны с именами Data1, Data2 и Data3. Вместо того, чтобы запоминать диапазоны, просто создайте именованный диапазон и введите этот именованный диапазон в поле имени.

Выбрать несмежные ячейки (с определенным значением) с помощью функции «Найти и заменить»

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

Например, если у вас есть данные о продажах нескольких торговых представителей, вы можете выбрать все ячейки с именем "Майк".

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

Ниже приведены инструкции по использованию функции «Найти и заменить» для выбора несмежных ячеек:

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

Теперь, когда все это выбрано, вы можете изменить, отредактировать или отформатировать их.

Совет: если вы вносите какие-либо изменения в значение выбранных несмежных ячеек и хотите, чтобы это изменение отображалось во всех ячейках, введите значение в активной ячейке, а затем нажмите Control + Enter.< /p>

Например, если вы хотите изменить имя с Майка на Михаил, сначала выполните описанные выше шаги, чтобы выбрать все ячейки с именем Майк. После выбора введите имя Михаил (при этом имя будет введено только в активную ячейку), а затем, удерживая клавишу Control, нажмите клавишу Enter.

Это обеспечит ввод одного и того же текста во все выбранные ячейки.

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

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

Надеюсь, этот учебник по Excel был вам полезен.

Вам также могут понравиться следующие советы и руководства по Excel:

Как усреднить несмежные ячейки (ячейки, которые не расположены рядом друг с другом), если эти ячейки содержат числа и больше 0.

Обычная функция СРЗНАЧЕСЛИ() в этом случае работать не будет; попробуйте, если не верите мне!

Среднее число несмежных ячеек больше 0

Это предполагает, что в электронной таблице есть несколько ячеек, которые не входят в один диапазон.

Итак, допустим, мы хотим усреднить ячейки A1, A5 и B2, если каждая ячейка содержит число, и это число больше 0.

Используйте эту формулу:

Чтобы это работало на вас, просто замените ссылки на ячейки A1, A5, B2 ячейками, которые вы хотите усреднить.

Нет смысла объяснять эту функцию; это просто работает.

Вот функция в Excel:

Примечания

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Усреднение ячеек, исключая нули в Excel
Учебник: Исключение нулей при усреднении ячеек в Excel. Этот метод удаляет все нули из уравнения.

Связывание ячеек в Excel
Учебное пособие. В этом учебном пособии я расскажу, как связать ячейки вместе. Это полезная функция .

Именование ячеек в Excel для упрощения использования формул/функций
Учебное пособие. В этом учебном пособии я собираюсь представить идею именованных ячеек. Именованная ячейка — это ячейка w.

Выделение ячеек с текстом или формулами (непустые ячейки)
Макрос: этот макрос выделяет все непустые ячейки Excel. Это означает, что если цел.

Предотвращение ввода пробелов в ячейки в Excel
Учебное пособие: Как запретить пользователю вводить пробелы в тексте в Excel. Это позволяет вам.

ActiveCell и Selection VBA для Excel
Учебное пособие: разница между ActiveCell и Selection в Excel VBA и когда их использовать. Оба пр.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

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