SignificantOtter

Добрый вечер!

Продолжаю делиться с вами тем, что знаю и люблю.

Сегодня вашему вниманию предлагаю небольшой прием, совсем не сложный, но полезный.

Очень часто прилетают заказы суть которых сводится к тому, что пользователя ставят в тупик отчеты, в которых не дублируются строки. Это может быть и скопированная сводная, и отчет из 1С. Примерно вот такой вид. (персиковый цвет для тревожности добавлен)

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

Если вам знакома такая проблема, то дальше вам может быть интересно:)

Как заполнить пустоту

1. Выделяем всю таблицу

(например так: выделяем любую ячейку в рамках таблицы и нажимаем Ctrl+*)

2. Нажимаем F5 -> Выделить-> Пустые ячейки

3. Ставим "="

4. Стрелочка вверх

5. Ctrl+Enter

6. Получившийся результат вставить значениями

Спасибо, что дочитали. Буду рада, если вам пригодится.

Всем добра!

P.S. @eakwarp , вам в прошлый раз не понравилось, что пост про excel и я пообещала котика. Котика в доме не нашлось, держите собаку-Чубаку.

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

Едем дальше.

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

1. Диапазоны

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

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

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

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

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

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

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

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

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

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

2. Форматы

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

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

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

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

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

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

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

Пример:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Именованные диапазоны

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

• Много примечаний

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

Редко нервничаю, а тем более жалуюсь, но сейчас аж руки трясутся от бешенства и беспомощности.
Есть у меня дома любимый робот - пылесос, да вот аккумулятор у него начал умирать. Заказала новый.
Полчаса назад курьер привёз. Радостно распаковала и обнаружила, что он мокрый и вздутый. Думаю "ой - вей, сторонние курьеры испортили, позвоню на фирму, они ж бедняги, не в курсе". Договорились с мужчиной из фирмы, что скину фото.
А далее чудеса сатурналии !
"это не наш аккумулятор, вы мошенница, не хочу из-за 4-х тысяч (дада это стоит 4к)с вами разговаривать, всего доброго! "

Пиксельная картинка, чтоб трясло больше.

Это было в 2012 году. Большой компанией ездили в Кению-Танзанию на фотосафари: увидеть большую пятёрку и погреть косточки на пляжах Занзибара.
Не буду сейчас расписывать как понравилась природа и как ужасно всё, что касается людей. (Например, на внутреннем рейсе мы смогли пройти паспортный контроль не по своим паспортам - перепутали пока стояли в очереди). Расскажу только про заявленую в заголовке тему.

Так вот.
Место действия - аэропорт Килиманджаро. Сдали багаж и проходим таможенный досмотр-там, где снимаешь ремень и вытаскиваешь из карманов всё, что дорого сердцу. В числе прочей скобянки у супруга обнаружили зажигалку с гравировкой, подаренную мной. Нам было сказано, что "вот нельзя с собой зажигалку" и мол "отдай мне, я выкину". Стоит отметить, что все остальные аэропорты ничего не имели против этого сувенира, а летали мы много.

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

Сдав багаж во второй раз снова оказались на таможенном контроле, где нас уже ждали с вопросом "ну и где моя Прелесть?". Пришлось соврать, что выкинули. Отвели в сторону- "показывай куда выкинули". Ок, вот вам правда.

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

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

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

И да, в ручную кладь теперь только дешёвый крикет, чтоб не жалко.

Спасибо, что прочитали.