Как облегчить файл эксель чтобы не зависал

Как уменьшить размер файла и ускорить его

Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам — возможно один или несколько из них укоротят вашего «переростка» до вменяемых размеров и разгонят его «тормоза» 🙂

Проблема 1. Используемый диапазон листа больше, чем нужно

Выделите первую пустую строку под вашей таблицей
Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
Повторите то же самое со столбцами.
Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

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

Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и — ваши файлы:

XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
XLSM — то же самое, но с поддержкой макросов.
XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Проблема 3. Избыточное форматирование

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

Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить — Очистить форматы (Clear — Clear Formats) на вкладке Главная (Home) :

Особенно «загружают» файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств — кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :

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

Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик — Надстройки COM (Developer — COM Addins) .

Проблема 4. Ненужные макросы и формы на VBA

Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:

нажмите Alt+F11, чтобы войти в редактор Visual Basic
найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
удалите все модули и все формы (правой кнопкой мыши — Remove — дальше в окне с вопросом о экспорте перед удалением — No):

Также код может содержаться в модулях листов — проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов — все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.

Проблема 5. Именованные диапазоны

Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :

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

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :

Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .

Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:

Sub Delete_All_Pictures() 'макрос для удаления всех картинок на текущем листе Dim objPic As Shape For Each objPic In ActiveSheet.Shapes objPic.Delete Next objPic End Sub

Проблема 7. Исходные данные сводных таблиц

По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) — вкладка Данные (Data) — снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):

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

В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную — на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Если в вашем файле включен общий доступ на вкладке Рецензирование — Доступ к книге (Review — Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.

Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:

Проблема 9. Много мусорных стилей

Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку «Слишком много форматов ячеек». Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home — Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо — и для размера вашего файла Excel и для его быстродействия.

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

Проблема 10. Много примечаний

Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная — Очистить — Очистить примечания (Home — Clear — Clear Comments) .

Как уменьшить размер файла и ускорить его

Проблема 1. Используемый диапазон листа больше, чем нужно

Выделите первую пустую строку под вашей таблицей
Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
Повторите то же самое со столбцами.
Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
XLSM — то же самое, но с поддержкой макросов.
XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Проблема 3. Избыточное форматирование

Проблема 4. Ненужные макросы и формы на VBA

нажмите Alt+F11, чтобы войти в редактор Visual Basic
найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
удалите все модули и все формы (правой кнопкой мыши — Remove — дальше в окне с вопросом о экспорте перед удалением — No):

Проблема 5. Именованные диапазоны

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:

Sub Delete_All_Pictures() 'макрос для удаления всех картинок на текущем листе Dim objPic As Shape For Each objPic In ActiveSheet.Shapes objPic.Delete Next objPic End Sub

Проблема 7. Исходные данные сводных таблиц

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Проблема 9. Много мусорных стилей

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

Проблема 10. Много примечаний

Как уменьшить размер файла и ускорить его

Проблема 1. Используемый диапазон листа больше, чем нужно

Выделите первую пустую строку под вашей таблицей
Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
Повторите то же самое со столбцами.
Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а «в пустоту» ниже и/или правее.
Сохраните файл (обязательно, иначе изменения не вступят в силу!)

Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.

Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB

XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
XLSM — то же самое, но с поддержкой макросов.
XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Проблема 3. Избыточное форматирование

Проблема 4. Ненужные макросы и формы на VBA

нажмите Alt+F11, чтобы войти в редактор Visual Basic
найдите окно Project Explorer’а (если его не видно, то выберите в меню View — Project Explorer)
удалите все модули и все формы (правой кнопкой мыши — Remove — дальше в окне с вопросом о экспорте перед удалением — No):

Проблема 5. Именованные диапазоны

Проблема 6. Фотографии высокого разрешения и невидимые автофигуры

Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:

Sub Delete_All_Pictures() 'макрос для удаления всех картинок на текущем листе Dim objPic As Shape For Each objPic In ActiveSheet.Shapes objPic.Delete Next objPic End Sub

Проблема 7. Исходные данные сводных таблиц

Проблема 8. Журнал изменений (логи) в файле с общим доступом

Проблема 9. Много мусорных стилей

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

Проблема 10. Много примечаний

Excel. Борьба со вздутием и тяжестью. ⁠ ⁠

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

Если в один прекрасный момент вы осознаете, что ваш рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, связать шапочку или развязать войну, то посмотрите возможные причины ниже- возможно помогут вернуть вашего «откормыша» до вменяемых размеров «:)

1. Диапазоны

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

Дело в том, что вы когда-то могли использовать какие-то ячейки на этом листе, а теперь они автоматически попадают используемый диапазон (он же Used Range), который excel и запоминает при сохранении.

Нажатие Ctrl+End перенесет вас на последнюю используемую ячейку, и если это будет фактическая последняя ячейка с данными в вашей таблице, то ура-ура, а если сильно далеко вправо/вниз — то вот вся эта пустота паразитирует и жрет мегабайты

Как избавиться от паразитов:

1. Встаем на первую пустую строку под таблицей

2. Нажмите Ctrl+Shift+стрелка вниз – (таким шагом выделим все пустые строки до конца листа)

3. Удаляем нещадно любым любимым способом (Ctrl+знак минус /Главная – Удалить – Удалить строки с листа./ через контекстное меню)

4. Та же операция для столбцов

5. Помните, что это стоит проверить на каждом листе.

6. Сохраните (обязательно, иначе волшебство не произойдет!)

Очень часто вижу, что клиенты по привычке используют формат — XLS. Да- классика, да -меньше проблем с совместимостью, да — привычка. Но устарел он крепко.

Что есть вкусненького, облегчающего жизнь и ваши файлы:

• XLSX — по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.

• XLSM — то же самое, но с поддержкой макросов.

• XLSB — двоичный формат, т.е. по сути — что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер — минимален.

Вывод: всегда и везде, где можно, переходите от старого формата XLS (возможно, доставшегося вам «по наследству» от коллег) к новым форматам.

Мой любимый формат – XLSB, советую.

Имеем небольшой файл такого вида

Excel. Борьба со вздутием и тяжестью. Microsoft Excel, На заметку, Длиннопост, Скриншот

Сохраняем тремя разными способами

Excel. Борьба со вздутием и тяжестью. Microsoft Excel, На заметку, Длиннопост, Скриншот

Даже на таком небольшом файле разница очевидна.

3. Автоматический пересчет формул

Если у вас в файле много формул, которые пытаются себя пересчитать при каждом минимальном изменении, то вам может быть полезно зайти в настройки (Файл – Параметры — Формулы) и поставить галочку на ручном пересчете формул:

Excel. Борьба со вздутием и тяжестью. Microsoft Excel, На заметку, Длиннопост, Скриншот

Да, вариант не самый изящный, но действенный.

Дальше важно помнить, что теперь файл будет пересчитываться только при сохранении файла и нажатии F9 на клавиатуре. (Так что при отправке файла кому-то, лучше вернуть настройку, чтобы не было мучительно больно.)

4. Избыточное форматирование

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

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

Сильно «загружают» файл залитые целиком строки и столбцы, т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов)

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

5. Журнал изменений (логи) в файле с общим доступом

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

Отсюда вывод: без необходимости не пользуемся общим доступом или сокращаем/отключаем количество дней хранения данных журнала (вкладка Подробнее в окне Доступ к книге.)

Excel. Борьба со вздутием и тяжестью. Microsoft Excel, На заметку, Длиннопост, Скриншот

Так же на скорость работы могут повлиять следующие вещи (не рассказываю подробно, так как они встречаются реже, и если вы этим пользуетесь, то имеете представление об их укрощении):

• Ненужные макросы, VBA. Большие макросы на Visual Basic, особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу.

• Фотографии и невидимые автофигуры

Всем добра и уютного Excel.

4 года назад

Для пробы попробовал 3.5 мегабайтный xlsx сохранить в xlsb.

Получился 4 мегабайтный файл.

Э — экстраполяция на основе нерепрезентативной выборки

4 года назад

сохранять в xlsb.. Не, когда файл только для себя и своей организации, особенно если офис купленный — без проблем, но вот если его отправлять в другую организацию — обязательно в стандартном xls. Лучше скачать 2-3 лишних мегабайта, чем устраивать пляски с бубном «какой из вариантов openoffice это сможет сожрать».

p.s. а за «общий доступ» спасибо, не знал о такой фиговине

раскрыть ветку
4 года назад

Это как с сурком

раскрыть ветку
4 года назад

Иллюстрация к комментарию

Был тут в книжном

раскрыть ветку
4 года назад

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

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

а ещё бывают таблицы в ворде. и мне интересно, когда наконец микрософт смогут сделать так, чтобы при работе с таблицей на 100+ строк он перестал грузить проц как десять майнеров.

раскрыть ветку
Похожие посты
2 года назад

Многоуровневые подписи в Excel⁠ ⁠

Для более красивой визуализации данных, например при составлении отчёта, можно сделать многоуровневые подписи. Для этого необходимо лишь немного переструктурировать данные. Затем выделяем весь диапазон таблицы и на вкладке Вставка → Диаграмма выбираем Гистограмму с группировкой.

Теперь вы сможете реализовать несколько групп подписей на одной диаграмме.

Многоуровневые подписи в Excel Microsoft Excel, Диаграмма, Визуализация, Полезное, На заметку

2 года назад

Как выбрать тип диаграммы?⁠ ⁠

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

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

В зависимости от типа данных, диаграммы на схеме разделены на 4 основные группы:

• Сравнение (характеристики: сравнение величин, много периодов, неповторяющиеся данные);

• Распределение (характеристики: распределение величин, обычно две переменных);

• Состав (характеристики: структура величин, статика);

• Отношение (характеристики: отношение величин, обычно три переменных).

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

Как выбрать тип диаграммы? Microsoft Excel, Диаграмма, График, Визуализация, Полезное, На заметку

Показать полностью 1
2 года назад

Проверка текста и таблицы в Excel⁠ ⁠

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

Первое – это проверка орфографии при нажатии клавиши F7, которая сразу отобразит все слова с ошибками и предложит варианты исправлений.

Второе – это проверка читаемости, которая поможет выявить объединённые ячейки там, где их не должно быть. Для этого перейдите на вкладку «Рецензирование» и выберите пункт «Проверить читаемость».

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

Конечно же содержание таблицы вам придётся проверять самим, но это уже совсем другая история)))

Проверка текста и таблицы в Excel Microsoft Excel, Таблица, Орфография, Проверка, Полезное, На заметку

Показать полностью 1
2 года назад

Поваренная книга Экселиста #3 — Визуальное форматирование согласно условиям⁠ ⁠

Для тех, кому надо сделать не только функционально, но и ~~красиво~~ читаемо и наглядно — в экселе (да и во всех табличных редакторах в принципе) придумано достаточно много всяких удобностей, но, готов спорить, многие их не используют. О самых простых, наверное, не будем, а вот как ~~нарисовать солнышко~~ выделить цветом/ форматированием строки, подходящие под какое-либо ваше условие — с удовольствием расскажу.
Для начала как это выглядит (строка 124, столбцы T ,V):

Поваренная книга Экселиста #3 - Визуальное форматирование согласно условиям Microsoft Excel, Таблица, Полезное, На заметку, Данные, Длиннопост

То есть, если есть какое-то условие (которое вы задаете сами) строка/столбец/любая выбранная (вами) область меняет форматирование (и тут тоже все можно настроить).

Теперь о том, как это сделать:

— Переходим на вкладку «Home» (тут прошу заранее извинить — русского Экселя под рукой нет и никогда не было, но, по логике — первая или вторая вкладка).

— Находим раздел Styles (картинка выше, самый правый), нажимаем на первый значок Conditional Formatting (что в вольном переводе «Условное форматирование»), видим вот такое выпадающее меню

— Видим набор функций, которые как раз таки и отвечают за визуальное форматирование таблицы согласно условиям. Если будет интересно — расскажу обо всех подробно, но сейчас переходим сразу к пункту Manage Rules (на русском, наверное, Редактировать правила, но не уверен). Почему сразу сюда? Создать новое правило можно и из раздела Редактирование, как и удалить тоже + вы увидите все правила форматирования, которые применены на вашей таблице. Открывается редактор правил, и вот тут начинается простор для творчества.

Для начала — общие правила применения/ отображения (ДО таблицы, пункты Show formatting rules for / Change rule order)
Show formatting rules for — выбор правил для заданой области. Показывает все правила форматирования для выбранной области. Для примера оставьте заданное значение, далее, опять же, будут вопросы — расскажу подробнее.

Change rule order — порядок применения правил. Правила применяются по очереди, по старшинству. То есть правило #1 будет применено ПОСЛЕДНИМ, поверх правил #2 и #3.

Теперь сам набор правил:

Первый столбец — само правило (опять-же, вариантов тьма, рассмотрим ТОЛЬКО правила на основе формул. Смотрим на первую формулу

Разбираемся:
«=$I2» — указывает на расположение данных. В данном случае:
— столбец безусловно $I, оператором $ указываем на абсолютный стиль при выборе столбца (это важно)
— строка 2 (тут стиль ОТНОСИТЕЛЬНЫЙ, при этом условии Эксель будет перебирать значения построково, что нам собственно и нужно)
» Тут опять-же ньюансы на отдельную статью. В двух словах — работает ТОЛЬКО если значение цифровое (то есть 89 — сработает, а вот 89i — не сработает). Снова — будет интересно — углублюсь и объясню разницу.

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

Четвертый столбец — Применить только для первого подходящего условия. Поясню — Эксель отформатирует только первую строку, подходящую под условие, для дальнейших данное правило НЕ СРАБОТАЕТ.

Создадим новое правило.

Для начала — выберем условие. Допустим нам нужно выделить все строки, с условием — значение столбца J в строке N БОЛЬШЕ 1

Переходим Home> Styles > Conditional Formatting > Manage Rules, снизу слева видим кнопку +, нажимаем — открывается интерфейс создание нового правила.

Style — Classic, Use a formula to. (опять-же, об остальных вариантах — отдельный разговор)
В поле вводим формулу (напомню значение столбца J в строке N БОЛЬШЕ 1)

В подменю Format wit — выбираем стиль. Можно сразу переходить к последнему пункту Customised Format и дать волю фантазии.

Выбрали? Супер. Нажимаем OK, видим созданный стиль в меню управления стилями:

Задаем область применения (в моем случае вот такую):

Нажимаем OK, еще раз открываем Manage Rules и.. в большинстве случаев ловим следующий «глюк» — значение поля Formula изменилось с =$J2>1 на =$J1048576>2. Нажимаем на формулу, исправляем на =$J2>1, нажимаем OK, и еще раз OK в редакторе правил, и получаем желаемый результат:

Естественно это очень поверхностно, иначе формат статьи перерос бы в формат учебника, но суть, я думаю, более чем понятна.
Если есть вопросы / пожелания / что-то непонятно / что-то описать подробнее — в комменты)

Показать полностью 9
3 года назад

Как определить, от чего зависит или на что ссылается формула?⁠ ⁠

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

Рядом с кнопкой Влияющие ячейки находится кнопка Зависимые ячейки, работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

3 года назад

Как обрезать изображение по кругу⁠ ⁠

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

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

Чтобы это реализовать, выберите нужную картинку и перейдите в раздел Формат → Обрезать → Обрезать по фигуре.

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

3 года назад

Массовая замена текста формулами в Excel⁠ ⁠

У Николая Павлова вышла новая статья как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Другими словами — как привести в единый порядок разношёрстый текст в таблице Excel.

Предположим, что у вас имеется список, в котором с разной степенью «пряморукости» записаны исходные данные — например, адреса или названия компаний:

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

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

Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко «Найти и заменить» или нажимая Ctrl+H?

Первое, что приходит в голову в подобной ситуации — произвести массовую замену по составленному заранее справочнику соответствия неправильных и правильных вариантов — вроде такого:

К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения «тяжелой артиллерии» в виде макросов на VBA или Power Query.

Случай 1. Массовая полная замена

Начнём с относительно простого случая — ситуации, когда нужно заменить старый кривой текст на новый полностью.
Предположим, что у нас есть две таблицы:

В первой — исходные разномастные названия компаний. Во второй — справочник соответствия. Если находим в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое название на правильное — из столбца Заменить второй таблицы-справочника.
Для удобства:
Обе таблицы преобразованы в динамические («умные») с помощью сочетания клавиш Ctrl+T или командой Вставка — Таблица (Insert — Table).

На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные, а второй таблице-справочнику — Замены.

Чтобы объяснить логику формулы зайдём чуть издалека.
Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND):

Эта функция определяет входит ли заданная подстрока (первый аргумент — все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должна вывести на выходе либо порядковый номер символа, начиная с которого текст был найден, либо ошибку если подстрока не обнаружена.

Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений — эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов.

Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:

Если же у вас предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#VALUE!).

Пугаться не стоит 🙂 На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9 (только не забудьте потом нажать Esc, чтобы вернуться обратно к формуле):

Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко), причём начиная с 4-го по счёту символа.

Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP):

У этой функции три аргумента:
Искомое значение — можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
Просматриваемый_вектор — тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив
Вектор_результатов — диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.

Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.

Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы «из коробки», то нам не придётся вводить эту формулу как классическую формулу массива — с помощью сочетания клавиш Ctrl+Shift+Enter. Достаточно будет простого Enter.
Вот и всё. Надеюсь вы ухватили логику.

Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено — и наша задача решена!

Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):

Случай 2. Массовая частичная замена

Этот случай чуть похитрее. Снова имеем две «умных» таблицы:

Первая таблица с криво записанными адресами, которые нужно исправить (я назвал её Данные2). Вторая таблица — справочник, по которому нужно произвести частичную замену подстроки внутри адреса (я назвал эту таблицу Замены2).

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

Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt+Enter):

Основную работу здесь выполняет стандартная Excel’евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE), у которой 3 аргумента:
Исходный текст — первый кривой адрес из столбца Адрес
Что ищем — тут мы используем трюк с функцией ПРОСМОТР (LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
На что заменить — аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.

Вводить эту формулу с Ctrl+Shift+Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.

И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула, при всей её элегантности, обладает и парой недостатков:
— Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому «Спб» в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE), либо предварительно привести обе таблицы к одному регистру.
— Если текст изначально правильный или в нём нет ни одного фрагмента на замену (последняя строка), то наша формула выдает ошибку. Этот момент можно нейтрализовать перехватом и заменой ошибок с помощью функции ЕСЛИОШИБКА (IFERROR):

— Если в исходном тексте встречается сразу несколько фрагментов из справочника, то наша формула заменяет только последний (в 8-й строке Лиговский «проспект» заменился на «пр-т», а вот «С-Пб» на «Санкт-Петербург» уже нет, т.к. «С-Пб» стоит выше в справочнике). Эту проблему можно решить повторным прогоном нашей же формулой, но уже по столбцу Исправлено:

Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? 🙂

Похожие публикации:

Где в mi браузере посмотреть сохраненные пароли

Где взять сертификат для криптопро csp

Как улучшить качество шрифта в windows 10

Согласно чертежа или чертежу как правильно