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

Умные Таблицы Excel – секреты эффективной работы

Умные Таблицы Excel – секреты эффективной работы

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

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

Умные таблицы в Excel

Видео

Постановка задачи

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

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

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

  • В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data) ).
  • При добавлении новых строк в них автоматически копируются все формулы.
  • При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  • При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

    Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

  • К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
    • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
    • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
    • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
    • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
    • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

    В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

    2. Основные функции

    2.1. Функция ВПР (англ. VLOOKUP) — как получить количество посетителей у позиций, находящихся в ТОП-10

    У нас есть список запросов с позициями и данные по трафику из Яндекс.Метрики. Мы хотим знать, какие из запросов, находящихся в ТОПе, приносят нам трафик.

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

    Синтаксис функции: ВПР (значение_которое_ищем; таблица_в_которой_ищем; номер_столбца_нужного_значения; [интервальный просмотр])

    Применение в SEO

    1. Кликаем по ячейке, в которую должны подтягиваться данные по трафику (в нашем примере это ячейка D3). Вставляем формулу = ВПР(B3;$F$2:$G$12559;2;0 ).

    B3 – ключевое слово, которое необходимо найти в таблице «Трафик»;
    ;$F$2:$G$12559 – диапазон таблицы «Трафик». Знак «$» вставлен для закрепления диапазона, чтобы при протягивании формулы он не сдвигался;
    2 – второй столбец таблицы «Трафик». Именно в этом столбце находятся данные по трафику;
    – интервальный просмотр, может принимать значение «0» (поиск точного совпадения) или «1» (поиск приблизительного);

    2. Нам нужен трафик запросов из ТОП-10. Отфильтруем данные столбца «Позиции» по условию меньше или равно 10.

    В итоге получаем:

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

    Синтаксис функции: ЕСЛИОШИБКА (проверяемое_значение; значение_если_ошибка).

    В нашем случае функция будет выглядеть следующим образом: ЕСЛИОШИБКА(ВПР(B3;$F$2:$G$12559;2;0);0)

    2.2. Функция ЕСЛИ — проверка соответствия продвигаемой страницы и той, что находится в поиске

    Функция «ЕСЛИ» проверяет выполнение условия. При его выполнении функция возвращает одно значение и другое, если условие не выполняется.

    Синтаксис функции: ЕСЛИ (Условие (логическое_выражение); Значение_если_истина; Значение_если_ложь)

    Применение в SEO

    С помощью функции «ЕСЛИ» мы можем посмотреть, соответствует ли продвигаемая страница той, что ранжируется в поиске.

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

    В ячейке «C2» добавляем функцию =ЕСЛИ(A1=B1;1;0)

    A1=B1 – проверяем, равен ли целевой URL релевантному;
    1 – выводим «1», если равен;
    – выводим «0», если не равен.

    2.3. Функция СЧЁТ ЕСЛИ — подсчёт количества запросов в ТОП-5, ТОП 5-10 и ТОП-10-50

    Считает количество ячеек, удовлетворяющих условию.

    Синтаксис функции: СЧЕТЕСЛИ (диапазон_значений; условие)

    Применение в SEO

    Эта функция поможет посчитать количество запросов из ТОП-5, ТОП-5-10 и ТОП-10-50.

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

    Для ТОП-5 функция будет выглядеть так: = СЧЁТЕСЛИ($C$3:$C$423;» ,

    где $C$3:$C$423; — закреплённый диапазон с позициями, « =СЧЁТЕСЛИ($C$3:$C$423;»

    Для ТОП-10-50 аналогично: =СЧЁТЕСЛИ($C$3:$C$423;»

    2.4. Объединение ячеек — как найти данные нужного значения

    В работе достаточно часто требуется объединить несколько ячеек в одну. Это может понадобиться при формировании файла на отклонение ссылок в Disavow Tools или при формировании шаблонов для Title.

    Есть несколько способов объединить ячейки:

    Вариант 1:
    Функция «СЦЕПИТЬ» (англ.CONCATENATE)
    Синтаксис: СЦЕПИТЬ (текст1; текст2; …)

    Вариант 2:
    Использование &
    Синтаксис: A1&B1, здесь &сцепить ячейки A1 и B1

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

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

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

    Используйте сводные таблицы, чтобы узнать тренды данных

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

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

    Если вы хотите расширить свои возможности Excel, ознакомьтесь с этими формулами Excel, которые делают удивительные вещи !

    Несколько советов по работе с VBA в Excel


    Добрый день!

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

    Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

    Visual Basic

    Опции

    Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

    Так же рекомендуется прописать:

    В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
    — VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
    — иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

    Ещё одним важным оператором является ON ERROR. Привожу варианты:

    Возможности языка

    Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):

    Ускорение работы макросов

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

    По порядку:
    1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
    2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
    3. Не обрабатывать события.
    4. Отображение границ страниц, тоже почему-то помогает.
    5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
    6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

    Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:

    Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
    Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

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

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

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

    Загрузка книги и события

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

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

    Защита

    Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

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

    Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
    — выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
    — выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
    — а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

    Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
    Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
    1. Сняли защиту.
    2. Включили группировку.
    3. Поставили защиту, при этом:
    — защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
    — разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
    — DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

    Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

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

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

    Теперь процедура будет вызываться при нажатии shift+delete.
    Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

    Заключение

    VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

    Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

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

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

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

    Самая первая функция, которую вы могли уже видеть в шаблонах рекламных кампаний,– подсчет количества символов в ячейке: =ДЛСТР(A6).

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

    Следующая, не менее полезная функция склеивания текстовых строк: =СЦЕПИТЬ(A7;B7). Упрощенная ее формула через амперсанд: =A4&B4. Если между склеиваемыми значениями нужен пробел, ставим его в кавычках: =A5&» «&B5.

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

    Преобразование букв из строчных в прописные и наоборот. За это отвечают следующие функции:

    =СТРОЧН(A9) – преобразует все буквы в строчные;
    =ПРОПИСН(A11) – преобразует все буквы в прописные;
    =ПРОПНАЧ(A10) – преобразует первую букву каждого слова в заглавную, остальные в строчные.

    Для чего нужны данные функции? Одна из них пригодится при создании рекламных объявлений по схеме «1 фраза = 1 объявление». Копируем запросы в столбец с заголовками и применяем к новым заголовкам функцию ПРОПНАЧ.

    Но если вы обратили внимание, функция преобразует в прописные символы первые буквы каждого слова в ячейке. Такая схема приемлема только для создания объявлений под систему Google Adwords. Для объявлений для Яндекс.Директ воспользуемся усложненной формулой:
    =ЗАМЕНИТЬ(B4;1;1;СИМВОЛ(КОДСИМВ(ПРОПНАЧ(B4))))

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

    Кстати, в OpenOffice есть встроенный функционал написания с прописной буквы только первого слова ячейки. Для этого необходимо перейти в меню «Формат» → «Регистр» и выбрать формат «Как в предложениях».

    При работе с рекламными кампаниями и анализе статистики может возникнуть необходимость в логических функциях. Рассмотрим пример с самой распространенной логической функцией ЕСЛИ:

    =ЕСЛИ(C6>33;»Превышен лимит»;»Все ок»).

    В примере функция применяется для проверки длины заголовка. Если значение в ячейке больше 33, выводится сообщение «Превышен лимит», если меньше – «Все ок».

    Напоминаем, для выхода из режима редактирования формул надо нажать Esc.

    10. ВПР: Поиск значений

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

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

    Аргументами ВПР являются искомое значение, таблица, номер столбца, из которого надо вытащить информацию, и интервальный просмотр. В последнем аргументе необходимо задать ЛОЖЬ, если вы ищете точное значение, и ИСТИНА для поиска приблизительного.

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

    Читать еще:  Как скрыть исполнение bat-файла ?
    Ссылка на основную публикацию
    Статьи c упоминанием слов:
    Adblock
    detector