Как работать с большими таблицами в excel

Работа с большими таблицами.

Основным документом в рассматриваемом программном продукте являются электронные таблицы. Для удобства работы с большими таблицами в EXCEL имеется возможность замораживать часть экрана (обычно в этом месте располагается “шапка” таблицы), а другую часть экрана с данными просматривать с помощью шкалы прокрутки.

Чтобы зафиксировать часть окна EXCEL, следует:

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

В меню ОкновыбратьФиксировать подокна.

Раздел рабочего листа над выбранной строкой замораживается.

Аналогичным образом фиксируются столбцы. Для этого следует выделит столбец справа от фиксируемого, а затем в меню ОкновыбратьФиксировать подокна. Чтобы разморозить окно, следует в менюОкно выбратьОтменить фиксацию, Рис. 7.

Составьте таблицу размером 7 столбцов на 10 строк на интересующую вас тематику. Произведите закрепление “шапки” таблицы для вертикальной прокрутки. Произведите закрепления части таблицы для горизонтальной прокрутки незакрепленной области.

«Замораживание» — удобная возможность, но иногда возникает необходимость независимой прокрутки в каждом из двух или более фрагментов. Для этого следует разделить экран. Если экран разделить на 4 части, то левая верхняя часть будет постоянно на экране, левая нижняя часть будет иметь возможность только для вертикальной прокрутки, правая верхняя часть экрана будет иметь возможность только для горизонтальной прокрутки, правая нижняя часть экрана будет иметь возможность как горизонтальной так и вертикальной прокрутки.

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

Фиксировать подокна. Для отмены разбиения таблицы выполните последовательно пункты менюОкно/Отменитьфиксацию/Удалить разбиение.

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

Сохранение результатов работы.

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

разрешен только для чтения;

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

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

Создайте четыре книги и сохраните каждую из них соответственно в режимах:

Только с паролем на открытие файла.

С паролями на открытие и разрешение записи информации.

Только с паролем на разрешение записи в книгу.

Только с флажком напротив позиции Рекомендовать доступ только для чтения.

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

Более интересным представляется использование возможности EXCEL защиты книги, листа или отдельной его ячейки. Пройдите по цепочке главное меню Сервис /Защита /Защита книги. Эта позиции меню позволяет защитить книгу в целом. Используя пароль, можно повысить уровень защиты. Защита книги не позволяет удалять или копировать листы, но не мешает менять их содержимое.

Защитите структуру вашей рабочей книги. Попробуйте удалить первый лист книги. Что при этом произойдет? Отметьте это в тетради.

Позиция меню Сервис/Защита/Защитить лист предоставляет возможность защитить отдельный лист рабочей книги. При этом пользователь сам выбирает параметры защиты: будет ли это содержимое листа, объекты на листе или сценарий.

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

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

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

Выберите позиции менюФормат/Ячейки/Защита и сбросьте флажок Защищаемая ячейка.

Выполните позиции меню Сервис/Защита/Защита листа .

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

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

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

Финансы в Excel

Главная Статьи Формулы Обработка больших объемов данных. Часть 1. Формулы

Обработка больших объемов данных. Часть 1. Формулы

Содержание
Описание примеров
Применение метода
Суммирование по одному ключевому полю
Суммирование по нескольким критериям
Поиск по одному критерию
Поиск по нескольким критериям
Выборка по одному критерию
Выборка вариантов
Заключение

Вложения:

nwdata_sums.xls	[Обработка данных (формат 97-2003)]	2725 kB
nwdata_sums.xlsx	[Обработка данных (формат 2007)]	732 kB

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

Методы переноса данных в Excel могут быть различны:

• Копирование-вставка результатов запросов
• Использование стандартных процедур импорта (например, Microsoft Query) для формирования данных на рабочих листах
• Использование программных средств для доступа к базам данных с последующим переносом информации в диапазоны ячеек
• Непосредственный доступ к данным без копирования информации на рабочие листы
• Подключение к OLAP-кубам

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

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

1. Обработка данных стандартными средствами интерфейса Excel
2. Анализ данных при помощи сводных таблиц и диаграмм
3. Консолидация данных при помощи формул рабочего листа
4. Выборка данных и заполнение шаблонов для получения отчета
5. Программная обработка данных

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

В данной статье будут рассмотрены способы консолидации и выборки данных при помощи стандартных формул Excel.

Описание примеров

Примеры к статье построены на основе демонстрационной базы данных, которую можно скачать с сайта Microsoft

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_sums.xls используется для версий Excel 2000-2003

Файл nwdata_sums.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

Применение метода

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

Проблема при консолидации данных при помощи сводных таблиц появляются, если предполагается дальнейшая работа с этими агрегированными данными. Например, сравнить или дополнить данные из двух разных сводных таблиц (как вариант: объемы продаж и прайс листы). В таком случае обычно прибегают к методу копирования значений из сводных таблиц в промежуточные диапазоны с дальнейшим применением формул поиска (VLOOKUP/HLOOKUP). Очевидно, что проблема возникает при обновлении исходных данных (например, при добавлении новых строк) – требуется заново копировать результаты консолидации из сводной таблицы. Другим, с нашей точки зрения, не совсем корректным методом решения является применение функций поиска непосредственно к диапазонам, которые занимают сводные таблицы. Это может привести к неверному поиску при обновлении не только данных, но и внешнего вида сводной таблицы.

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

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

Суммирование по одному ключевому полю

Таблицы с формулами на листе SUM показывают вариант решения задачи консолидации данных по одному ключевому значению.

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

Нижние таблицы показывают возможности другой редко используемой функции DSUM

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

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

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

В принципе, функции типа DSUM являются устаревшим методом работы с данными, в подавляющем большинстве случаев лучше использовать SUMIF, SUMPRODUCT или формулы обработки массивов. Но иногда их применение может дать хороший результат, например, при совместном использовании с интерфейсной возможностью «расширенный фильтр» – в обоих случаях используется одинаковое описание условий через дополнительные диапазоны.

Суммирование по нескольким критериям

Таблицы с формулами на листе SUM2 показывают вариант суммирования по нескольким критериям.

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

Операция «&» используется для соединения строк. Можно также вместо этого оператора использовать функцию CONCATENATE. Промежуточный символ «;» (или любой другой служебный символ) необходим для обеспечения уникальности сцепленных строковых значений.

Пример: Есть, если два поля с перечнем слов. Пары слов «СТОЛ»-«ОСЬ» и «СТО»-«ЛОСЬ» дают одинаковый ключ «СТОЛОСЬ». Что соответственно даст неверный результат при консолидации данных. При использовании служебного символа комбинации ключей будут уникальны «СТОЛ;ОСЬ» и «СТО;ЛОСЬ», что обеспечит корректность вычислений.

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

Второй пример – это популярный вариант использования функции SUMPRODUCT с проверкой условий в виде логического выражения:

Обрабатываются все ячейки диапазона (data!$M$2:$M$3000), но для тех ячеек, где условия не выполняются, в суммирование попадает нулевое значение (логическая константа FALSE приводится к числу «0»). Такое использование этой функции близко по смыслу к формулам обработки массива, но не требует ввода через Ctrl+Shift+Enter.

Третий пример аналогичен, описанному использованию функций DSUM для листа SUM, но в нем для диапазона условий использовано несколько полей.

Четвертый пример – это использование функций обработки массивов.

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

Пятый пример содержится только в файле формата Excel 2007 (xlsx). Он показывает возможности новой стандартной функции

Поиск по одному критерию

Таблицы с формулами на листе SEARCH предназначены для поиска по ключевому полю с выборкой другого поля в качестве результата.

Первый вариант – это использование популярной функции VLOOKUP.

Во втором вариант использовать VLOOKUP нельзя, так как результирующее поле находится слева от искомого. В данном случае используется сочетание функций MATCH+OFFSET.

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

Поиск по нескольким критериям

Таблицы с формулами на листе SEARCH2 предназначены для поиска по нескольким ключевым полям.

В первом варианте используется техника использования служебного столбца, описанная в примере к листу SUM2:

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

Четвертый и пятый параметр в функции OFFSET используется для образования массива и определяет его размерность в строках и столбцах.

Выборка по одному критерию

Таблица на листе SELECT показывает вариант фильтрации данных через формулы.

Предварительно определяется количество строк в выборке:

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

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

Результат выдается через функцию вычисляемой адресации:

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

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

Выборка вариантов

Самый сложный вариант выборки по ключевому полю представлен на листе SELECT2. Формулы сами определяют все доступные ключевые значения второго критерия.

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

Третий служебный столбец проверяет значение второго ключа на уникальность:

Результирующий столбец второго ключа ProductName ищет уникальные значения в служебном столбце C:

Столбец Quantity просто суммирует данные по двум критериям, используя технику, описанную на листе SUM2.

Заключение

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

Как упростить работу с цифрами: 5 инструментов Excel

Евгений Довженко о том, как можно эффективно работать даже с огромными массивами данных.

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

Excel — незаменимый помощник для достижения этих целей. Мы импортируем информацию, "подтягиваем" ее, систематизируем. На ее основе строим диаграммы, сводные таблицы, планируем, прогнозируем.

Однако в Excel до недавнего времени было 2 важных ограничения:

Мы не могли разместить на рабочем листе Excel более миллиона строк (а наши данные о продажах за 2 года занимают, например, 10 млн строк).

Мы знали, как создать и настроить интерактивные и обновляемые отчеты, но это отнимало много времени.

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

С другой стороны, есть категория пользователей, которые работают со сложными BI-системами. Это системы бизнес-аналитики (business intelligence), которые дают возможность быстро визуализировать, "крутить" данные и извлекать из них ценную информацию (data mining). Однако внедрение и поддержка таких систем требует значительного участия IT-специалистов и больших финансовых вложений.

Начиная с версии 2010, в Excel добавили инструменты, в названиях которых присутствует слово power: Power Query, Power Pivot и Power View. Они позволили сгладить грань между пользователями Excel и комплексных BI-систем.

Power Query

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

Для этого и необходим Power Query. До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft.

В версии 2016 это уже встроенный в программу инструментарий, находящийся на вкладке "Данные" (Data) в разделе "Скачать и преобразовать" (Get and Transform).

Перечень источников информации, к которым можно подключаться — огромный: от баз данных (их в последней версии 10) до Facebook и Google таблиц (рис. 1).

Рис 1. Выбор источника данных в Power Query

Вот некоторые возможности Power Query по подготовке и преобразованию данных:

отбор строк и столбцов, создание пользовательских (вычисляемых) столбцов

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

транспонирование таблицы, разворачивание по столбцам (Pivot) и наоборот — сворачивание данных, организованных по столбцам, в построчный вид (Unpivot)

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

Рис 2. Окно редактора Power Query

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

Пример

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

Таблица на сайте непригодна для прямого использования (рисунок 2-1):

все валюты не нужны

в колонке "Курс" в качестве разделителя целой и дробной частей используется точка (в наших региональных настройках — запятая)

в колонке "Курс" отображается показатель за разное количество единиц валюты: за 100, за 1000 и т. д. (указано в отдельной колонке "Количество единиц")

Рис. 2-1. Так выглядит таблица с курсами валют на сайте Нацбанка.

С помощью Power Query мы подключаемся к таблице текущих курсов валют на сайте НБУ и в этом редакторе готовим запрос на извлечение данных:

В колонке "Курс" меняем точку на запятую (инструмент "Замена значений").

Создаем вычисляемый столбец, в котором курсы валют в колонке "Курс" делятся на количество единиц валюты из колонки "Количество единиц".

Удаляем лишние столбцы и оставляем только строки валют, с которыми работаем.

Выгружаем полученную таблицу на рабочий лист Excel.

Результат показан на рисунке 2-2.

Рис. 2-2. Так выглядит результирующая таблица в нашем Excel файле.

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

• Только нужная теория и практические таблицы
• Опросник по Data/Content Management
• Шаблон аудита текущей BI-стратегии
• План улучшения бизнес-процессов BI-департамента

Power Pivot

У вас данные находятся в разрозненных источниках? Некоторые таблицы содержат больше 1 млн строк? Вам нужно все это объединить в одну модель данных и анализировать с помощью, например, сводной таблицы Excel? Здесь понадобится Power Pivot — надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

В Power Pivot вы можете добавлять данные из разных источников, связывать таблицы между собой (рисунок 3). Таблицы при этом не обязательно должны находиться на рабочих листах Excel. Вместо этого они по-прежнему будут храниться в файле Excel, но просматривать их можно в окне Power Pivot (рис. 4). Поэтому нет ограничения на количество строк — в вашем файле Excel могут находиться таблицы и в сотни миллионов строк.

Рис. 3. Окно Power Pivot в представлении диаграммы

Рис. 4. Окно Power Pivot в представлении данных

Вот некоторые возможности Power Pivot, помимо описанных выше:

добавлять вычисляемые столбцы и поля (меры), в том числе основанные на расчетах из нескольких таблиц

создавать и мониторить в сводной таблице ключевые показатели эффективности (KPI)

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

И обрабатывать все это с помощью сводной таблицы Excel, построенной на модели данных.

Пример. У предприятия в базе данных (или отдельных файлах Excel) в 5 таблицах хранится информация о продажах, клиентах, товаре и его классификации, менеджерах по продажам и закупочных ценах продукции. Необходимо провести анализ по объемам продаж и маржинальности по менеджерам.

С помощью Power Pivot:

добавляем все 5 таблиц в модель данных

связываем таблицы по общим ключам (столбцам)

в таблице "Продажи" создаем вычисляемый столбец "Продажи в закупочных ценах", умножив количество штук из таблицы "Продажи" на закупочную цену из таблицы "Цена закупки"

создаем вычисляемое поле (меру) "Маржа"

с помощью инструмента "Ключевые показатели эффективности" устанавливаем цель по маржинальности и настраиваем визуализацию — как выполнение цели будет визуализироваться в сводной таблице

Теперь можно "крутить" эти данные в сводной таблице или в отчете Power View (следующий инструмент) и анализировать маржинальность по товарам, менеджерам, регионам, клиентам.

Power View

Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).

В отличие от сводной таблицы, в отчет Power View можно добавлять диаграммы и другие визуальные объекты. Здесь нет такого количества настроек, как в диаграммах Excel. Но в том то и сила инструмента — мы не тратим время на настройку, а быстро создаем отчет, визуализирующий данные в определенном разрезе.

Вот некоторые возможности Power View:

— быстро добавлять в отчет таблицы, диаграммы (без необходимости настройки)

организовывать срезы и фильтры

уходить на разные уровни детализации данных

добавлять карты и располагать на них данные

создавать анимированные диаграммы

Пример отчета Power View — на рисунке 5.

Рис. 5. Пример отчета Power View

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

Excel. Несколько советов по борьбе с размером глючно-больших файлов ⁠ ⁠

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

Реально не так давно счкинули расчетник по работе весом за 30 Мб. Таблица около 5000 строк. Скролл уходит в бездну Экселя. При этом файл иногда притормаживает.

Выделяем целиком строку чуть ниже таблицы кликнув на ее порядковом номере. Потом жмем Ctrl+Shift+стрелка вниз. Выделилось все. Правой кнопкой мыши кликаем и выбираем «Удалить«.

Может даже ругаться, что недостаточно памяти для операции и разрешить сделать ее без возможности отката. Соглашаемся. Удаляет. Обычно не быстро, а подумает. Сохраняемся. Закрываем и открываем файл и видим, что ползунок скролла теперь ведет к низу таблицы. А размер файла из 30 Мб, стал 3,5.

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

Второй случай — скрытые объекты. Типа рисунков прозрачных. Увидеть их невозможно, только если в нужном месте кликнуть, подсвечивается рамочка, как при работе с картинками.

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

После определенного роста количества этих объектов работать с файлом становится трудновато из-за тормозов. Да и размер файла растет.

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

Но избавиться от этого не сложно.

Сначала проверим есть ли такое на листе. И да, проверять надо на каждом листе.

Для отображения скрытых объектов необходимо вызвать в меню Главная/ Редактирование/ Найти и выделить команду Область выделения.

Появится окошко «Фигуры на этом листе» И если кроме Comment = примечаний ваших к ячейкам увидите кучу изображений или других объектов — то вот они ваши гады глюкодельные.

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

Выделить ВСЕ объекты можно с помощью инструмента Выделение группы ячеек (Главная/ Найти и выделить). Переключатель установить на Объекты. Потом просто жмем кнопку Delete и ждем пока оно все удалит. Процесс в зависимости от скорости компа и количества объектов может быть не моментальный.

Третий случай — скрытые имена.

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

Кстати не забываем через вкладку «Формулы» зайти в Диспетчер имен и удалить там все, что не вы назначили. Просто чтоб его не было. Буквально вчера в присланном файле было неработающее имя с ссылкой на файл в папке с названием «Отчеты_2003» . Т.е. оно там уже скоро как 10 лет висит бесцельно. Ладно хоть путь к файлу имел папки с приличными названиями, а не что-то типа «отчеты конченым заказчикам» или типа того.

Но скрытые имена через Диспетчер имен не удалить.

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

Макрос чтобы удалить скрытые имена в Excel

Создать макрос и запустить выполнение!

Порадовались , что 5000 скрытых имен было удалено. И файл на 1-2 Мб стал легче.

Инструкцию как пользоваться макросами давать не буду. Если не знаете — поисковик в помощь. Все просто — ваша бабушка разберется.

Четвертый случай.

Никаких глюков нет. Но надо сделать вес файла меньше. Ну мало-ли вдруг на дискету не влазит :)))

Файл — Сохранить как — Двоичная книга Эксель.

Хоп.. волшебство — файл получится с расширением .xlsb и на больших файлах может стать на порядок легче, если не в два раза, то на 30-40% вполне (ну если в нем картинок не напихали, тогда поможет только их сжатие). И вроде как должен чуть шустрее открываться.

Если есть еще способы — пишите в комменты.

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Вообще-то во всех Excel есть команда, которая эффективно убирает лишнее форматирование и прочий мусор, при этом нужные данные не теряются. Находится она в надстройке Inquire (по умолчанию скрыта) которая всегда поставляется с Excel и называется Clean Excess Cell Formatting.

Никогда не мог понять почему такую полезную фичу спрятали так далеко.

Для того чтобы включить надстройку: Файл -> Параметры -> Надстройки -> Надстройки COM и поставить нужную галочку.

Для чистки просто нажать кнопку Clean Excess Cell Formatting.

На порядок — это в 10 раз, а не на 40%

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

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

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

Если в документе уже слишком много лишнего форматирования, то выделяем весь лист и жмем Очистить — Формат. И затем форматируем нужные диапазоны

Читать ещё на Пикабу

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

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

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

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

Приступы рвоты с коричневым содержимым, напоминающим по виду кофейную гущу.

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

Внезапная потеря сознания или обморок.

Ощущение резкой и интенсивной боли в животе.

Внезапный приступ нарушения дыхания.

Сильная острая боль в области груди, сердца.

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

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

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

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

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

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

Интенсивная боль в области груди является симптомом потенциально смертельного инфаркта миокарда. Таких пациентов следует направить в отделение интенсивной терапии кардиологического профиля.

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

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

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

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

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

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

Когда умеешь работать с инструментом⁠ ⁠

Офисное⁠ ⁠

Любишь Excel — люби и #ССЫЛКА!

Ответ на пост «Чем не стоит заниматься после 30 лет?»⁠ ⁠

Насчет мед осмотров.
Мне 32, в прошлом году участвовал в гонке героев, пробежал 11 км с препятствиями. Мог в выходные за день проезжать более 100 км на велосипеде, гулять с раннего утра до ночи пешком.
Что имеем на данный момент:
— после 20 км на велосипеде правая нога перестает сгибаться и разгибаться в колене. К тому же жутко болит.
— после 10000 шагов за раз начинают сильно болеть ноги от колен и ниже.
— начальная стадия артроза, мать ее.

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

А что насчет моего артроза, на уколы теперь через день ходить, чувствую себя стариком 😁

Чем не стоит заниматься после 30 лет?⁠ ⁠

Материал был взят и переведен с Рэддита. Приятного прочтения!

1. Жить воспоминаниями о том, каким ты был двадцатилетним. Пусть эти воспоминания и останутся в том возрасте, а ты набирайся новых воспоминаний.

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

3. Влезать в споры в интернете. В 90% случаев вы будете спорить с болваном, который вчера закончил школу.

4. Париться о том, что, по мнению окружающих, вы должны или не должны делать в 30 лет. Мне сейчас 35, и я здоровее, чем был в 25, и спокойно делаю то, что не мог в 20. А если будешь делать, что от тебя ждут люди, никогда не станешь счастливым.

5. Бросить заниматься спортом. Вы не представляете, сколько у вас времени уйдет в этом возрасте на восстановление. Я в 29 восстанавливался очень быстро, а вот сейчас, в 36, на это уходит в пять раз больше времени.

6. Думать, что заправишься завтра утром, когда будешь ехать на работу. Заправляйся всегда с вечера, не откладывай это дело на завтрашнее утро. Где-то читал, что наш мозг устроен таким образом, что рассматривает нас завтрашних, как других людей. И если ты что-то откладываешь на завтра, то в подсознании это будет как проблема другого человека, а не твоя.

7. Пропускать плановые медосмотры. Мне 33, и у меня ничего не болит, но я уверен, что врачи что-нибудь найдут.

8. Чувствовать вину из-за работы. Нафиг это дерьмо. Ты просто винтик. Если ты умрешь в понедельник, уже в среду на твое место возьмут другого человека, а в четверг о тебе и не вспомнят. Мне 33, и сейчас работа это всего лишь то, что я делаю ради зарплаты, а коллектив никакая мне не семья.

9. Сидеть с плохой осанкой. У вас к 40 все будет болеть, не доводите себя до этого.

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

11. Устанавливать непонятные правила, как вы должны себя вести в зависимости от возраста. Можно все, если это не противоречит закону. В 46 я сделал себе ирокез. Теперь не надо париться по поводу постоянной укладки волос, да и ощущения офигенные.

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

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

14. Бесконтрольно набирать вес. Это не следует делать в любом возрасте, но чем старше вы становитесь, тем труднее будет вернуться в норму.

15. Чрезмерно пить. Это было весело, когда тебе 20, но сейчас я понимаю, что мне совсем не нравятся пьяные люди. Да и опыт, который ты приобретаешь в барах, часто бывает очень токсичным. Если уж так хочется выпить, делай это дома.

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

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

Похожие подборки без цензуры и купюр ежедневно выходят на моем канале https://t.me/realhistorys

Всем здоровья и добра!

Ответ на пост «Купил машину на льготника: сэкономил на растаможке или потерял авто»⁠ ⁠

По фиг на Белоруссию.
Летом прошлого года решил воспользоваться падением курсом доллара, купить машину в Японии, привезти и продать (ненуачо — бызнес ).

И нашел, что если везти машину на жителя с пропиской ДВФО, то можно не платить за кнопку. Ыкономия же.
А у меня супруга дражайшая с такой пропиской.
Думаю — вот щаз как наебу систему.
И взял небольшой аппарат за небольшие 560к (цена без учета дороги с владивостока )

Ну и что:
1. Плату за кнопку отменили в тот период (правда говорили, что не отменили, а отложили, но фиг знает)
2. Пока с женой гнали машину с Владивостока (устроили ну очень шикарное романтическое путешествие на втором десятке лет брака), машина понравилась и решили ее не продавать.

Так что бызнес не удался 😉

Автосигнализация-террорист⁠ ⁠

Столкнулся я с проблемой , которую пытаюсь решить. Может быть найдутся здесь люди, которые предложат решение или найдется здесь виновник происходящего). Суть в следующем. Кто-то где-то установил автосигнализацию с gsm модулем и в качестве одного из номеров для управления этой сигнализацией указал (надеюсь по ошибке) мой номер телефона. Номер телефона используется для работы на кнопочном телефоне. И теперь я знаю обо всем, что происходит с чьей-то машиной. Открытие дверей, сработка датчиков, автозапуска и прочее. Ну думаю это не надолго. Скоро владелец поймет, что кому то эти сообщения не приходят и исправит ситуацию. Ведь если указали мой номер ошибочно вместо кого-то, значит до кого-то эти сообщения не доходят.
но не тут то было. В течение месяца меня эта сигнализация терроризирует почти ежедневными звонками и смс. Перезвонить на этот номер я на тот момент не мог, так как данный номер у меня использовался по работе, был не оплачен, работал только на входящие. Я добавил этот номер в черный список, но это не особо помогает. Теперь телефон постоянно отображает пропущенные с этого номера. Закинул денег на этот номер, активировал тариф и решил перезвонить на номер сигнализации. Моя тактика была следующая. Так как кроме этого номера сигнализации у меня больше ничего нет и связаться с владельцем я никак не могу, я решил звонить на сигнализацию. И так как мой номер добавлен в базу этой сигнализации у меня есть доступ к основным ее функциям без ввода пароля. Впоследствии оказалось, что владелец не удосужился даже сменить пароль по умолчанию и доступ к некоторым функциям есть с любого телефона.
Начал я звонить на эту сигнализацию и создавать сложности для ее владельца в надежде, что он заедет в сервис и там все исправят. Ну либо сам догадается, а еще лучше когда будет разбираться увидит входящие и исходящие на мой номер и перезвонит мне сам лично. Сделать это не трудно, баланс Симки и звонки он наверняка контролирует в мобильном приложении оператора связи.
Но то ли владелец глуповат, то ли сервис куда он обратился с этой проблемой или еще что я не знаю, но проблема до сих пор не решена. Я почти каждый день звоню на этот номер, включаю автозапуск, запрашиваю гео локацию (на мой номер не приходит смс с геолокацией почему то, видимо приходит владельцу только), и мое любимое-включаю режим антиограбления. Особенно если машина в данный момент едет ). Открывать-закрывать двери и отключать сигнализацию не рискую во избежании серьезных проблем у владельца.
Владельца эта ситуация явно не устраивает, он пытается решить проблему, но двигается не в том русле. Не догадывается проверить номера и список входящих-исходящих. И так уже на протяжении месяца. Всего что он добился это смена номера и оператора связи, но понятное дело что это не то, что хотелось бы.
Поэтому если вдруг владелец автомобиля это читает, то теперь он знает что происходит с его автомобилем и почему «глючит» сигналка.
А от вас я жду комментарии и другие возможные решения проблемы.