Servisneva.ru

Сервис Нева
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в Экселе (MS Excel) настроить автофильтр?

Как в Экселе (MS Excel) настроить автофильтр?

Не работает предварительный просмотр файлов Excel в проводнике и Outlook

Добрый день! Уважаемые читатели и гости одного из крупнейших IT блогов в рунете по системному администрированию Pyatilistnik.org. В прошлый раз мы с вами исправили ошибку с оборудованием «Сбой запроса дескриптора устройства», двигаемся дальше и вновь поговорим по офисный продукт от компании Microsoft. Сейчас в компании идет процесс миграции с Windows Server 2012 R2, на более свежие редакции и на одном из новых серверов люди стали жаловаться, что у них перестала работать область просмотра при выборе файлов Exel, как в проводнике, так и в Outlook, выскакивало сообщение «Нет данных для предварительного просмотра» . Ниже я вам покажу, как это исправить.

Интеграция MS Excel и Python

В сегодняшней статье я хотел бы, как можно подробнее, рассмотреть интеграцию приложений Python и MS Excel. Данные вопрос может возникнуть, например, при создании какой-либо системы онлайн отчетности, которая должна выгружать результаты в общепринятый формат ну или какие-либо другие задачи. Также в статье я покажу и обратную интеграцию, т.е. как использовать функцию написанную на python в Excel, что также может быть полезно для автоматизации отчетов.

Работаем с файлами MS Excel на Python

Для работы с Excel файлами из Python мне известны 2 варианта:

Использование библиотек

Итак, первый метод довольно простой и хорошо описан. Например, есть отличная статья для описания работы c xlrd, xlwt, xlutils. Поэтому в данном материале я приведу небольшой кусок кода с их использованием.

Для начала загрузим нужные библиотеки и откроем файл xls на чтение и выберем
нужный лист с данными:

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

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

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

Из примера выше видно, что библиотека xlrd отвечает за чтение данных, а xlwt — за запись, поэтому нет возможности внести изменения в уже созданную книгу без ее копирования в новую. Кроме этого указанные библиотеки работают только с файлами формата xls (Excel 2003) и у них нет поддержки нового формата xlsx (Excel 2007 и выше).

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

Для начала загрузим библиотеку и выберем нужную книгу и рабочий лист:

Как видно из вышеприведенного листинга сделать это не сложно. Теперь посмотрим как можно считать данные:

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

Теперь посмотрим как нам произвести запись и сохранить данные:

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

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

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

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

Работа с com-объектом

В своих отчетах я предпочитаю использовать второй способ, а именно использование файла Excel через com-объект с использованием библиотеки win32com. Его преимуществом, является то, что вы можете выполнять с файлом все операции, которые позволяет делать обычный Excel с использованием VBA.

Проиллюстрируем это на той же задаче, что и предыдущие примеры.

Для начала загрузим нужную библиотеку и создадим COM объект.

Теперь мы можем работать с помощью объекта Excel мы можем получить доступ ко всем возможностям VBA. Давайте, для начала, откроем любую книгу и выберем активный лист. Это можно сделать так:

Давайте получим значение первой ячейки и последовательности:

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

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

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

Однако, внимательный читатель, обратит внимание на переменную i, которая инициализируется не 0, как принято python, а 1. Это связано с тем, что мы работаем с индексами ячеек как из VBA, а там нумерация начинается не с 0, а с 1.

На этом закончим разбор способов работы с excel файлами в python и перейдем к обратной задаче.

Вызываем функции Python из MS Excel

Может возникнуть такая ситуация, что у вас уже есть какой-либо функция, которая обрабатывает данные на python, и нужно перенести ее функциональность в Excel. Конечно же можно переписать ее на VBA, но зачем?

Читать еще:  Defraggler что это за программа

Для использования функций python в Excel есть прекрасная надстройка ExcelPython. С ее помощью вы сможете вызывать функции написанные на python прямо из Excel, правда придется еще написать небольшую обертку на VBA, и все это будет показано ниже.

Итак, предположим у нас есть функция, написанная на python, которой мы хотим воспользоваться:

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

Сохраним функцию в файле plugin.py и положим его в ту же директорию, где будет лежать наш excel файл, с которым мы будем работать.

Теперь установим ExcelPython. Установка происходит через запуск exe-файла и не вызывает затруднений.

Когда все приготовления выполнены, открываем тестовый файл excel и вызовем редактор VBA (Alt+F11). Для работы с вышеуказанной надстройкой необходимо ее подключить, через Tools->References, как показано на рисунке:

Ну что же, теперь можно приступить к написанию функции-обертки для нашего Python-модуля plugin.py. Выглядеть она будет следующим образом:

Итак, что же происходит в данной функции?

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

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

  1. Объект модуля, полученный на предыдущем шаге
  2. Имя вызываемой функции
  3. Параметры, передаваемые функции (передаются в виде списка)

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

Как видно из рисунка все отработало правильно.

Надо отметить, что в данном материале используется старая версия ExcelPython, и на GitHub’e автора доступна новая версия.

Заключение

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

Также хочу заметить, что указанные пакеты не являются единственными и в статье опущено рассмотрение, таких пакетов как xlsxwriter для генерации excel файлов или xlwings, который может работать с Excel файлами «на лету», а также же PyXLL, который выполняет аналогичные функции ExcelPython.

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

Фильтры и сортировка в Microsoft Excel

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

Функционал фильтрации и сортировки в Microsoft Excel

У Microsoft Excel несколько реализаций функционала фильтрации и сортировки. Если вы создадите форматированную таблицу, т.е. сделаете обычную таблицу с данными, потом выделите её и форматируете как таблицу,

Фильтры и сортировка в Microsoft Excel

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

Фильтры и сортировка в Microsoft Excel

Это не всегда удобно т.к. часто нужно компактное размещение таблицы на листе для вывода на печать. И если вы не будете пользоваться фильтрацией, чтобы эти кнопки не занимали место в названиях таблицы и не отвлекали внимание, их можно убрать. Делаем клик на любой ячейке таблицы, идём в меню «Данные», жмём «Фильтр». И всё – кнопки фильтрации и сортировки убраны из таблицы. Этой же кнопкой «Фильтр» мы можем при необходимости вернуть интеграцию фильтрации и сортировки в таблицу. Либо же можно реализовать её для простой неформатированной таблицы. Если вам необходимо только сортировать данные таблицы, можно сделать это с помощью кнопок для сортировки в меню MS Excel «Данные».

Фильтры и сортировка в Microsoft Excel

Или в главном меню, всё это один и тот же функционал. Здесь также есть кнопка «Фильтр» для включения/отключения интеграции фильтрации и сортировки в таблицу.

Фильтры и сортировка в Microsoft Excel

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

Сортировка данных в Microsoft Excel

Данные таблицы можно сортировать по возрастанию или убыванию значений ячеек: текстовые — по алфавиту, числовые – по последовательности из чисел, временные – по временной последовательности. Нажимаем на любую из ячеек столбца с данными, по критерию которых мы хотим с вами выстроить таблицу. И жмём в главном меню или меню «Данные» Microsoft Excel кнопку сортировки данных – по возрастанию или убыванию. Если это столбец, например, с ценами, вся таблица выстроится в последовательности увеличения или уменьшения цен в указанном столбце. Если это столбец, скажем, с названиями товаров, таблица выстроится по алфавиту названий товаров в обычном или обратном порядке.

Фильтры и сортировка в Microsoft Excel

Бóльшие возможности может предложить функция настраиваемой сортировки. Нажмём на данную кнопку.

Фильтры и сортировка в Microsoft Excel

И здесь мы в качестве критериев для сортировки можем выбрать :

  • «Столбец» — тип данных таблицы, т.е. столбцы с названиями данных нашей таблицы ;
  • «Сортировка» – значения ячеек, выделения цветным маркером или шрифтом, условное форматирование ;
  • «Порядок сортировки» – убывание или возрастание.
Читать еще:  Где скачать и как установить игровой Центр Mail.ru

Например, у нас в таблице есть особые позиции товара, которые надо постоянно мониторить, мы их отметили жёлтым маркером. Чтобы отсортировать таблицу по строкам с жёлтым маркером, оставляем первый критерий для сортировки по умолчанию, в нашем случае это столбец наименования товара. Второй критерий сортировки выставим цвет ячейки. Для третьего критерия порядка сортировки указываем жёлтый цвет ячеек. И также указываем, что маркированные данные должны быть сверху списка. Жмём «Ок».

Фильтры и сортировка в Microsoft Excel

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

Фильтры и сортировка в Microsoft Excel

Фильтрация данных в Microsoft Excel

Для фильтрации данных, как упоминалось, нужны интегрированные в таблицу функции фильтрации и сортировки. Если в вашей таблице их нет, повторимся, в главном меню или меню «Данные» Microsoft Excel нужно нажимать на кнопку «Фильтр». Если у вас таблица с большим объёмом данных, с помощью фильтра в таблице Excel можете убрать какие-то строки и оставить лишь нужные. И таким образом сделать информацию более удобной для анализа или печати.

Работает фильтр очень просто: в выпадающем списке столбца с данными, где мы хотим убрать отдельные из них, нажимаем на выпадающий список и работаем с фильтром. Например, вот тот же случай, когда у нас есть отмеченные маркером особо важные позиции таблицы. И чтобы отобразить у нас в таблице только их, в выпадающем списке столбца жмём «Фильтр по цвету». Укажем цвет маркера, в нашем случае жёлтый. И в таблице отобразятся только маркированные данные. Чтобы убрать фильтр, в выпадающем списке этого же столбца нажмём «Удалить фильтр из столбца…».

Фильтры и сортировка в Microsoft Excel

В столбцах с числовыми данными нам будут доступны числовые фильтры. С их помощью можем убрать из таблицы, например, данные с маленькими суммами. Нажмём на выпадающий список столбца с интересующими нас цифрами, жмём «Числовые фильтры», выбираем «Больше».

Фильтры и сортировка в Microsoft Excel

И укажем цифру, скажем, 100. Т.е. мы хотим скрыть товарные позиции с сумами меньше 100 руб. Вписываем данную цифру или можем выбрать варианты сумм из выпадающего списка фильтра. Жмём «Ок».

Фильтры и сортировка в Microsoft Excel

И всё: позиции таблицы с сумами менее 100 руб. теперь скрыты. Чтобы вернуть их назад, удаляем фильтр в этом же столбце.

Фильтры и сортировка в Microsoft Excel

Работа с фильтром текстовых данных аналогична. В столбце с наименованиями позиций товара нажмём выпадающий список, выберем «Текстовые фильтры», жмём «Содержит».

Фильтры и сортировка в Microsoft Excel

И укажем слово-фильтр. Например, мы хотим видеть в таблице только позиции по кофе. Вписываем слово «кофе», жмём «Ок».

Фильтры и сортировка в Microsoft Excel

И таблица отфильтрована по этому слову в названиях товаров.

Фильтры и сортировка в Microsoft Excel

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

Скрыть/отобразить ленту

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

  • Чтобы скрыть ленту, щелкните правой кнопкой мыши в ​​свободном месте ленты и выберите в контекстном меню команду Свернуть ленту (Minimize the Ribbon) или дважды щелкните левой кнопкой мыши по заголовку активной вкладки.
  • Чтобы при скрытой ленте воспользоваться каким-либо инструментом, щелкните по заголовку нужной вкладки, а затем на появившейся ленте выберите инструмент.

Excel Autofit

Функция AutoFit в Excel обычно предоставляется в Microsoft Excel, которая предлагает автоматическое изменение ширины или высоты ячеек на листе. Это в основном используется для столбцов и строк, так что оно автоматически вмещает данные разных размеров. Функция автоподбора в Excel предоставляется для столбцов и строк. Опцию AutoFit можно использовать, используя 3 различных метода, которые упомянуты ниже:

  • С помощью двойного щелчка для автоматической подгонки столбцов и строк.
  • С помощью ленты для автоматической подгонки столбцов и строк.
  • Использование сочетания клавиш для автоматической подгонки столбцов и строк.

Рассмотрим приведенный ниже пример, как показано на рисунке 1

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

Как использовать Autofit в Excel?

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

Вы можете скачать этот шаблон Excel Autofit здесь — Шаблон Excel Autofit

Метод 1 — с помощью двойного щелчка для автоматической подгонки ширины столбца и высоты строки

Этот метод является одним из самых простых и быстрых способов настройки ширины столбца и высоты строки. Шаги для реализации этого метода следующие:

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

На рисунках ниже подробно описаны вышеописанные шаги.

Шаг 1: Дважды щелкните по заголовку, как показано на рисунке ниже. При этом ширина столбца регулируется. И измененную ширину можно увидеть на шаге 2.

Шаг 2: Дважды щелкните заголовок, как показано на рисунке ниже.

Шаг 3: На рисунке ниже значение не отображается в ячейке B2, так как размер столбца невелик. Поэтому для отображения значения ширина столбца должна быть увеличена.

Читать еще:  Как пользоваться инструментом "Перо" в Photoshop

Шаг 4: Дважды щелкните между столбцами, как показано на рисунке ниже. Это автоматически подгонит ширину столбцов.

Шаг 5: Автоподбор, реализованный для ширины столбца и высоты строки с помощью параметра двойного щелчка, показан ниже.

Способ 2 — Использование ленты для автоматической подгонки ширины столбца и высоты строки

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

  • Для настройки ширины одного или нескольких столбцов выберите столбцы. После выбора всех столбцов перейдите на вкладку «Главная», после чего под опцией «Ячейки» выберите опцию «Формат». В списке параметров под форматом выберите «Ширина столбца AutoFit».
  • Аналогично, для регулировки высоты одного или нескольких рядов выберите ряды. После выбора всех строк перейдите на вкладку «Главная», после чего под опцией «Ячейки» выберите опцию «Формат». Из списка опций под форматом выберите «AutoFit Row Height».

На рисунках ниже подробно описаны вышеописанные шаги.

Шаг 1: На этом шаге, после выбора столбцов, для которых вам нужно настроить ширину столбцов, нажмите Home-> Format-> AutoFit Column Width.

Шаг 2: На этом шаге, после выбора строк, для которых вам нужно отрегулировать высоту строки, нажмите Home-> Format-> AutoFit Row Height.

Способ 3 — Использование сочетания клавиш для AutoFit ширины столбца и высоты строки

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

  • Для автоматической подгонки ширины столбца: выберите количество столбцов в соответствии с вашим выбором, затем нажмите «Alt + H», затем «O», а затем «I».
  • Для автоматической подгонки высоты строки: выберите количество строк в соответствии с вашим выбором, затем нажмите «Alt + H», затем «O», а затем «A».
  • Если вам нужно автоматически подогнать весь лист, нажмите Ctrl + A.

Alt + H открывает домашнюю вкладку; «О» открывает меню формата; «I» выбирает параметр ширины столбца автозаполнения; «A» выбирает параметр высоты строки автоподбора.

Преимущества Autofit в Excel

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

Недостатки автофита в Excel

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

Что нужно помнить об автоподборке в Excel

  • Максимальная ширина столбца составляет 255, то есть он может вместить максимум 255 символов, но увеличение размера шрифта или изменение стиля шрифта может уменьшить это число.
  • Строки имеют максимальную высоту 409 точек, где 1 точка эквивалентна 0, 035 см.
  • Если введенный вами текст является переносом текста в ячейке, обязательно нажмите «Обтекание текстом», иначе высота ячейки будет высотой шрифта.
  • В случае, когда ширина столбца или высота строки установлена ​​в 0, обычно эта конкретная строка или столбец не видна, то есть скрыта.

Рекомендуемые статьи

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

Сортировка в Excel по нескольким столбцам

Если возникла необходимость отсортировать данные в редакторе Эксель по двум или более столбцам, следует так же, как и в предыдущем случае выбрать диапазон данных и открыть окно «Настраиваемая сортировка». Далее в первой группе следует отметить заголовок столбца, данные в котором нужно упорядочить в первую очередь. Вторая группа остается без изменений, а в третьей необходимо указать желаемый тип сортировки.

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

Начиная с Excel 2010 на вкладку «Разработчик» добавлена новая группа кнопок «Надстройки». По умолчанию эта вкладка на ленте Excel отсутствует, для её отображения необходимо зайти в меню Файл/Параметры/Настройка ленты и в правой части диалогового окна установить флажок перед полем «Разработчик». После этого на ленте приложения появляется одноименная вкладка с группой кнопок «Надстройки». При помощи одной из них вызывается менеджер надстроек, в котором можно произвести выбор файла устанавливаемой надстройки.

ustanovka nadstroek v excel 2013

При помощи кнопки, расположенной рядом, можно управлять COM-надстройками.

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector