Как импортировать данные из веб-страниц в таблицу Excel
В этом приеме описывается три способа получить данные, содержащиеся в веб-странице: вставить статическую копию информации; создать обновляемую ссылку на сайт; открыть страницу непосредственно в Excel.
Вставка статической информации
Один из способов получить данные из веб-страницы на лист — просто выделить текст в браузере, нажать Ctrl+C, чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал — в комплекте с настройками форматирования, гиперссылками и графикой.
Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная ► Буфер обмена ► Вставить, можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная ► Буфер обмена ► Вставить ► Специальная вставка, а затем пробовать различные варианты вставки.
Вставка обновляемой информации
Если вы хотите регулярно получать доступ к обновленным данным из веб-страницы, создайте веб-запрос. На рис. 176.1 показан сайт, который содержит курсы валют в таблице с тремя столбцами. Выполнив следующие шаги, можно создать веб-запрос, позволяющий извлекать эту информацию, а затем обновлять ее в любое время одним щелчком кнопкой мыши.
Рис. 176.1. Этот сайт содержит информацию, которая часто меняется
- Выберите Данные ► Получение внешних данных ► Из Интернета для открытия диалогового окна Создание веб-запроса.
- В поле Адрес введите URL сайта и нажмите кнопку Пуск. Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса, вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
- Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
примера я щелкну на стрелке рядом с таблицей курсов. - Нажмите кнопку Импорт для открытия диалогового окна Импорт данных.
- В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
- Нажмите кнопку ОК, и Excel импортирует данные (рис. 176.2).
Рис. 176.2. Данные, импортированные из веб-страницы
По умолчанию импортированные данные — это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.
Открытие веб-страницы напрямую
Еще один способ получить данные веб-страницы на лист — открыть URL-адрес напрямую, с помощью команды Файл ► Открыть. Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть. Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.
Парсинг нетабличных данных с сайтов
С загрузкой в Excel табличных данных из интернета проблем нет. Надстройка Power Query в Excel легко позволяет реализовать эту задачу буквально за секунды. Достаточно выбрать на вкладке Данные команду Из интернета (Data — From internet) , вставить адрес нужной веб-страницы (например, ключевых показателей ЦБ) и нажать ОК:
Power Query автоматически распознает все имеющиеся на веб-странице таблицы и выведет их список в окне Навигатора:
Дальше останется выбрать нужную таблицу методом тыка и загрузить её в Power Query для дальнейшей обработки (кнопка Преобразовать данные) или сразу на лист Excel (кнопка Загрузить).
Если с нужного вам сайта данные грузятся по вышеописанному сценарию — считайте, что вам повезло.
К сожалению, сплошь и рядом встречаются сайты, где при попытке такой загрузки Power Query «не видит» таблиц с нужными данными, т.е. в окне Навигатора попросту нет этих Table 0,1,2. или же среди них нет таблицы с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог — вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, Power Query пока не умеет распознавать такую разметку и загружать такие данные в Excel.
Тем не менее, есть способ обойти это ограничение 😉
В качестве тренировки, давайте попробуем загрузить цены и описания товаров с маркетплейса Wildberries — например, книг из раздела Детективы:
Загружаем HTML-код вместо веб-страницы
Сначала используем всё тот же подход — выбираем команду Из интернета на вкладке Данные (Data — From internet) и вводим адрес нужной нам страницы:
После нажатия на ОК появится окно Навигатора, где мы уже не увидим никаких полезных таблиц, кроме непонятной Document:
Дальше начинается самое интересное. Жмём на кнопку Преобразовать данные (Transform Data) , чтобы всё-таки загрузить содержимое таблицы Document в редактор запросов Power Query. В открывшемся окне удаляем шаг Навигация (Navigation) красным крестом:
. и затем щёлкаем по значку шестерёнки справа от шага Источник (Source) , чтобы открыть его параметры:
В выпадающием списке Открыть файл как (Open file as) вместо выбранной там по-умолчанию HTML-страницы выбираем Текстовый файл (Text file) . Это заставит Power Query интерпретировать загружаемые данные не как веб-страницу, а как простой текст, т.е. Power Query не будет пытаться распознавать HTML-теги и их атрибуты, ссылки, картинки, таблицы, а просто обработает исходный код страницы как текст.
После нажатия на ОК мы этот HTML-код как раз и увидим (он может быть весьма объемным — не пугайтесь):
Ищем за что зацепиться
Теперь нужно понять на какие теги, атрибуты или метки в коде мы можем ориентироваться, чтобы извлечь из этой кучи текста нужные нам данные о товарах. Само-собой, тут всё зависит от конкретного сайта и веб-программиста, который его писал и вам придётся уже импровизировать.
В случае с Wildberries, промотав этот код вниз до товаров, можно легко нащупать простую логику:
- Строчки с ценами всегда содержат метку lower-price
- Строчки с названием бренда — всегда с меткой brand-name c-text-sm
- Название товара можно найти по метке goods-name c-text-sm
Иногда процесс поиска можно существенно упростить, если воспользоваться инструментами отладки кода, которые сейчас есть в любом современном браузере. Щёлкнув правой кнопкой мыши по любому элементу веб-страницы (например, цене или описанию товара) можно выбрать из контекстного меню команду Инспектировать (Inspect) и затем просматривать код в удобном окошке непосредственно рядом с содержимым сайта:
Фильтруем нужные данные
Теперь совершенно стандартным образом давайте отфильтруем в коде страницы нужные нам строки по обнаруженным меткам. Для этого выбираем в окне Power Query в фильтре [1] опцию Текстовые фильтры — Содержит (Text filters — Contains) , переключаемся в режим Подробнее (Advanced) [ 2] и вводим наши критерии:
Добавление условий выполняется кнопкой со смешным названием Добавить предложение [ 3] . И не забудьте для всех условий выставить логическую связку Или (OR) вместо И (And) в выпадающих списках слева [4] — иначе фильтрация просто не сработает.
После нажатия на ОК на экране останутся только строки с нужной нам информацией:
Чистим мусор
Останется почистить всё это от мусора любым подходящим и удобным лично вам способом (их много). Например, так:
- Удалить заменой на пустоту начальный тег: <span > через команду Главная — Замена значений (Home — Replace values) .
- Разделить получившийся столбец по первому разделителю » > » слева командой Главная — Разделить столбец — По разделителю (Home — Split column — By delimiter) и затем ещё раз разделить получившийся столбец по первому вхождению разделителя » < » слева, чтобы отделить полезные данные от тегов:
Разбираем блоки по столбцам
Если присмотреться, то информация о каждом отдельном товаре в получившемся списке сгруппирована в блоки по три ячейки. Само-собой, нам было бы гораздо удобнее работать с этой таблицей, если бы эти блоки превратились в отдельные столбцы: цена, бренд (издательство) и наименование.
Выполнить такое преобразование можно очень легко — с помощью, буквально, одной строчки кода на встроенном в Power Query языке М. Для этого щёлкаем по кнопке fx в строке формул (если у вас её не видно, то включите её на вкладке Просмотр (View) ) и вводим следующую конструкцию:
= Table.FromRows(List.Split( #»Замененное значение1″ [Column1.2.1] , 3 ))
Здесь функция List.Split разбивает столбец с именем Column1.2.1 из нашей таблицы с предыдущего шага #»Замененное значение1″ на кусочки по 3 ячейки, а потом функция Table.FromRows конвертирует получившиеся вложенные списки обратно в таблицу — уже из трёх столбцов:
Ну, а дальше уже дело техники — настроить числовые форматы столбцов, переименовать их и разместить в нужном порядке. И выгрузить получившуюся красоту обратно на лист Excel командой Главная — Закрыть и загрузить (Home — Close & Load. )
Как переносить табличные данные с сайта в «Excel». Порядок действий для импорта таблицы из интернет-сайта
Если вы обладаете достаточным количеством времени и ресурсов перенести данные с сайта в таблицу «Excel» можно в «ручном режиме». Тот случай, когда таблица на сайте выделяется курсором мыши, копируется и вставляется в файл «Эксель». Естественно, этот способ долог и неудобен.
Я хочу рассказать Вам о другом, автоматизированном способе переноса данных с сайта в программу «Excel». Этот способ позволяет настроить процесс импорта обновленных актуальных данных с сайта прямо в таблицу одним нажатием кнопки мыши.
Для автоматизации импорта данных в «Эксель» из интернета потребуется «Excel» версии 2013 и выше, а так же надстройка Power Query.
Последовательность настройки скачивания данных:
Порядок действий для импорта таблицы из интернет-сайта в таблицу Excel.
- Шаг 1. Скопировать ссылку на страницу сайта, с которой планируете импортировать таблицу . Например: http://ruexcel.ru/zavisimost/ .
- Шаг 2. Перейти во вкладку Power Query .
- Шаг 3. В группе «Получение внешних данных» кликнуть по иконке «Из интернета»
- Шаг 4. В появившемся окне вставить в пустое поле, скопированный в шаге № 1 URL-адрес и нажать «Ок»
Программа Excel соединится с сайтом, обнаружит все опубликованные на странице сайта таблицы и предложит Вам выбрать, какую таблицу загрузить с сайта в Ваш документ Excel.
- Шаг 5. Выбрать нужную таблицу из предложенного списка и нажать «Правка» или «Загрузить»
Если нажать «Загрузить», таблица будет импортирована целиком. В режиме правки можно редактировать вид загружаемой таблицы, выбирать нужные столбцы и т.д.
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Правильный способ перенести таблицу с сайта в эксель
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Круто! Это работает, ругается на ошибки в сайте, но импортирует вообще все. В 2016 оффисе нет такого навигатора, поэтому всю страницу импортирует, но лишнее можно удалить.
Замечательный метод, только вот работает, в лучшем случае, с 1 из 10 сайтов, но тут уж не к экселю претензии.
Эксель чот мозгоебным стал, чтобы открыть обычный csv надо теперь через импорт данных это делать, сам он сука перестал догадываться что там строки и столбцы есть
Когда увольняешься с нелюбимой работы
— Кем работаешь? -Ну, как сказать. Жонглёром.
Служба вскрытия замков 47. Пластик
Занимаюсь вскрытием, заменой и установкой замок более 8 лет.
Во многих ситуациях важно определить какой замок (или какой элемент замка) закрыт.
Многие меня спрашивали в комментах
— Вот поставлю я замок без скважины и никто его не найдет.
На мои заявления о том что найти закрытый замок дело 1 минуты, заявляли про портативный рентген аппарат.
Все на много проще. Настолько проще что некоторые будут в шоке.
Слушать или определять тактильно это полный бред. Максимум вы получите точность 5-10см.
А вот такой пластик определяет ригели замка с точность. около 1.5мм.
Я создал группу замочных мастеров https://t.me/masterzamki
Группа создана с согласия пикабушников и по их просьбам.
В группе вы можете задать любой интересующий вас вопрос и получить ответ квалифицированного мастера. Так же можете найти контакты частного мастера в своем городе.
Список мастеров в закрепленном комменте
Группа уже работает и уже помогает людям.
Работает, не покладая лап
А ты все еще жалуешься на свою работу ?
Мгновенное заполнение в Excel — магия в чистом виде
Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.
Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg
Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:
Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.
Извлечение данных
Предположим, у нас есть вот такой столбец с текстом:
Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.
Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!
Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.
ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.
Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.
Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:
Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.
Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.
Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.
Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.
Извлечение только чисел из столбца
Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:
В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.
Работа с текстом
В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:
Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:
Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.
Извлечение части сплошного текста
Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:
С номером поступаем аналогично.
Сбор текста
В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:
На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.
В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)
На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube
Коллекция фигурок, которую мы заслужили
Моё рабочее состояние:
Мой магазин
Магазин авторских украшений, натуральных камней, в центре Севастополя)
Ответ Wixter в «Агностик»
Полезные трюки при работе в Excel (часть 2)
Друзья, всем привет.
Если честно, я не ожидал такого отклика на первый пост. Всем огромное спасибо за комментарии и идеи, про что ещё рассказать. Тут сразу почему-то вспоминается сцена из второго Дэдпула в вертолёте. Если немного перефразировать, то мы — сила ИКСэль.
Кстати, для тех, кто не хочет читать, а смотреть видео, можете переходить на мой канал на Ютубе. Там уже есть видео по первой статье. По текущим приёмам постараюсь видео выложить сегодня, но не обещаю (записать более менее нормальное видео оказалось куда сложнее, чем я думал). Ссылка на канал — (2) Андрей Митрохин — YouTube
Меняем столбцы местами с помощью Shift.
Если ты когда-нибудь сталкивался с тем, что нужно поменять столбцы (строки) в таблице местами, то, скорее всего, поступал следующим образом. Вставлял пустой столбец, вырезал нужный, вставлял в пустой. А можно проще. Выделяешь столбец (строку, диапазон), зажимаешь Shift, потом левой кнопкой мыши хватаешь столбец за границу, перетаскиваешь в нужное тебе место, отпускаешь левую кнопку мыши, отпускаешь Shift — готово:
Текстовое число в нормальный числовой формат.
Данная тема была поднята в комментариях. Там я рассказал про один из способов решить этот вопрос. Но, пожалуй, повторюсь. Зачастую, при выгрузке из различных корпоративных систем, числовые данные выгружаются в Excel в формате текста. С этими псевдо-числами ты не можешь производить никаких вычислений (кроме подсчёта их количества). Простое присвоение этим данным числового формата не даёт нужного результат, они всё равно остаются текстом. Что нужно сделать. Выделяем «кривые» данные (можно сразу весь столбец), присваиваем нужный формат. Не снимая выделения с диапазона, переходим на вкладку Данные — Работа с данными — Текст по столбцам:
Нажимаем на неё и, не вдаваясь в подробности, сразу жмём «Готово». Почему так происходит, расскажу в видео. Дополнительно покажу ещё пару способов, как это можно сделать.
Изменение поведения маркера автозаполнения (квадратик справа внизу).
Ещё один момент, который был навеян комментарием (вот она, сила ИКСэль). Маркер автозаполнения — это волшебная вещь. Ввёл в ячейку цифру 1, в следующую цифру 2, выделяешь их, тянешь за правый нижний угол вниз левой кнопкой мыши, вуаля! Excel воспринимает этот как арифметическую прогрессию с шагом 1 и заполняет тебе номера по порядку. С датами тоже чудесно работает. Вводишь 01.01.2023, тянешь вниз, он все дни подряд тебе прописывает. Кто посмеет сказать после этого, что Excel не милашка? Но что, если тебе не нужна прогрессия или дни подряд? Ты хочешь, чтобы во всех ячейках были именно «1» и «2» или «01.01.2023». Да, кто-то скажет, что можно воспользоваться смарт-тэгом «Параметры автозаполнения» и выбрать нужный тебе вариант:
И будет абсолютно прав. Это сработает. Но вопрос был «а можно ли скопировать значения без помощи вот этого меню?». Можно.
Тянем за правый нижний угол нужно с зажатым Ctrl.
Тянем за правый нижний угол не левой, а ПРАВОЙ кнопкой кнопкой мыши. В этом случае, правда, без дополнительного действия не обойтись, потому что как только правую кнопку мыши отпустишь, появится меню, где нужно будет выбрать, а что ты хочешь.
Магия Ctrl + Enter и инструмента «Выделить группу ячеек»
Бывало ли у вас такое, что нужно изменить значение/формулу в определённом столбце в некоторых отфильтрованных ячейках? Казалось бы, всё просто. Ставишь нужные тебе фильтры, прописываешь необходимые исправления, потом просто за правый нижний угол тащишь вниз (или два раза по нему щёлкаешь левой кнопкой мыши). Но, к сожалению, когда речь заходит про отфильтрованный диапазон, Excel довольно часто ведёт себя непредсказуемо, а именно, даже в скрытые фильтром ячейки вносит исправленное значение. Чтобы этого избежать, нужно провернуть следующее. Устанавливаем нужные фильтры, выделяем ячейки, в которых хотим поменять формулу, нажимаем Ctrl+G. Попадаем в диалоговое окно «Переход» и выбираем там «Выделить»:
Хочу отметить, что данная команда вообще способна на многое. Но нам сейчас от неё нужно одно единственное: только видимые ячейки:
Далее очень важный момент! Не нажимая ничего лишнего (не выделяйте никаких ячеек, не ставьте курсор в строку формул и т.д.), нажимаем на клавиатуре » story-block story-block_type_text»>
Признавайтесь честно, кого раздражали цифры и буквы, которые появляются на панели быстрого доступа, когда ты нажимаешь клавишу Alt?
А на самом деле, как по мне, безумно полезная штука. Почему? Потому что благодаря этому, мы любую команду можем вызвать с помощью «горячих» клавиш (условно их так назовём, потому что по сути они таковыми не являются). В моём случае, чтобы выделить только видимые ячейки в диапазоне, я должен нажать Alt + 09. Если мне нужно поменять представление в таблице, я нажму Alt + 9, потом на клавиатуре стрелку вниз, всё, выбирай нужное тебе представление. Сначала будет непривычно. Но со временем, когда привыкнешь, уже не глядя будешь нажимать. А буквы помогут без помощи мыши добраться до любой команды на вкладках. Признаюсь честно, буквами я никогда не пользовался, но знаю, что есть пользователи, которые принципиально презирают работу мышкой и вообще почти всё делают с помощью клавиатуры. Что ж, каждому своё.
Кто-то может сказать, что большинство инструментов, про которые я рассказал, не так уж и сильно работу ускоряют. Я не буду с этим спорить, потому что это правда. Но из таких вот мелочей и складывается весь наш рабочий день (да и жизнь в целом, чего уж). Сэкономил там пару секунд, там 10, там ещё чуть-чуть. В итоге, всё вместе, это даёт ощутимую экономию времени, которое ты можешь потратить на более приятные вещи (чтение постов на Пикабу, например).
На этом пока всё. Спасибо за уделённое время и внимание. Надеюсь, что-то пригодится вам в работе. Правда, если после первой статьи успехом для себя я считал помощь хотя бы одному человеку, то сейчас решил поднять планку до двух человек. Комментарии, само собой, приветствуются. Благодаря вам я вижу, что волнует пользователей, но про что во время занятий я банально не успеваю рассказать.
Полезные трюки при работе в Excel
Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.
Сомнения перед написанием
Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.
Почему я посчитал, что это будет полезно
Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).
Небольшое пояснение
Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:
Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).
Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.
F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)
Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).
Представления
Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.
И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:
Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:
В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:
Кстати, так можно добавить на панель быстрого абсолютно любую команду.
Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.
ПРЕДУПРЕЖДЕНИЕ!
Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).
После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.
Два окна одной книги.
Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:
Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.
Специальная вставка (пропускать пустые ячейки)
Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:
Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:
Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!
Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.
P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.
Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube
Вид из окна на работе
Наткнулся на пост про вид с рабочего места машиниста поезда, вспомнил как в начале месяца зашёл к себе в офис, отсутствовал весь январь. Вид из окна впечатлил)
Добавлю ещё фотку, но видно фигово
Место действия — Новосибирск. А у вас как с видом?)
Офисные будни
Сейчас состоялся диалог с коллегой.
К: DDlix, а как сложить значения нескольких ячеек в экселе? Каждый раз плюсы писать муторно.
Я: =СУММ и выделяешь нужные ячейки
*кряхтит ещё минут пять и выдаёт*
К: Не получается, пишет: «ИМЯ??»
Я: Ну так и пиши — Настя
Я: Подхожу и с каменным лицом показываю ошибку, выхожу из кабинета, чтобы просмеяться.
Вот до чего приводит коктейль из доверия и незнания офисных программ:)
Я не умею себя контролировать
Блаженное лузерство
Страхи – сильный рычаг воздействия на людей. Поэтому полезно знать самые распространенные из них. В прошлом посте “Возьми и спроси” мы затронули страх задавать вопросы. Сегодня поговорим про страх проигрыша в споре. Работа с руководителями подарила мне дергающийся глаз и множество отличных примеров, в том числе и на эту тему.
Один мой знакомый руководитель, назовем его Денис, попал в драматическую ситуацию. Он оказался неправ. И ладно бы он перепутал тональный крем и консилер, так нет. Его сотрудница Марина на общем совещании обнаружила неправоту Дениса в очень важном рабочем вопросе. В тот злополучный день Денис сбежал от позора на другую встречу. Однако новое совещание было не за горами, и Денис пришел ко мне за советом.
Он спросил меня, как победить в их споре с Мариной? Я попросил аргументы обеих сторон. Денис рассказал. Положение было ужасное – Марина была абсолютно права. И теперь столь важный для всей команды вопрос повис в воздухе из-за Марины. Во взгляде Дениса читалась тоска по временам, когда аргументом начальника был костёр. Я поспешно предложил простой и логичный выход: признать перед всеми правоту Марины, а сейчас обсудить что-нибудь более интересное, например наплыв баянов на Пикабу. Но не тут-то было.
Мое легкомыслие потрясло Дениса.
– Я не могу расписаться в своей неправоте! – почти кричал он.
– Распишись в правоте Марины – сострил я, но тут же пожалел.
– Ты что, не понимаешь?! – Денису явно было не до шуток. – Это конец моему авторитету!
– У твоей команды довольно высокие запросы, – сказал я. – Тебя уважают только при условии, что ты непогрешимее Папы Римского?
– Да причем тут непогрешимость! Я окажусь лузером, а лузеров никто не уважает.
Мне всё стало ясно. Среди ключевых понятий нашего мышления профессор когнитивной лингвистики Джордж Лакофф приводит метафору “спор – это война”. Поясню. Мы используем военную лексику для описания споров. “Она защищалась, как могла”, “Он нападал на каждый аргумент”, “Его критика била в цель”, “Его позиция была разгромлена” и так далее.
Понятие спора как войны настолько прочно укоренилось в нашем сознании, что признать правоту противника (снова военная лексика) – означает проиграть, то есть стать лузером. И Денис в ужасе представлял себя поверженным на глазах у всей команды. Я предложил иной подход.
– Скажи, Денис, – осторожно начал я. – ты же уверен, что Марина права?
– Да, – в отчаянии ответил он. – Мне нечего ей возразить!
– То есть, – продолжал я, – твоя изначальная позиция была ошибочной?
– Похоже на то, – вздохнул Денис так обреченно, что я почувствовал себя полицейским, штрафующим бабушку за переход в неположенном месте.
– Могу ли я заменить “ошибочная” на “ложная”? Твоя позиция была ложной?
– Ну замени, толку-то? – Денис начал раздражаться.
– Толк будет, – строго сказал я. – Если твоя позиция ложная, то позиция Марины какая? Дай антоним на “ложную”.
– Ну истинная, получается. У нас урок русского?
– Человеческого, не отвлекайся. Раз у нее истинная позиция, то, соглашаясь с ней, ты стал ближе к истине?
На лице Дениса отразилось замешательство. Ведь стать ближе к истине – это хорошо, а лузером – плохо. Денис размышлял минуты две. Почти вечность при нашем темпе жизни.
– Послушай, – наконец сказал он. – Это всё здорово, но мои сотрудники не будут думать о том, ближе к истине я стал или дальше. Марина будет победительницей в их глазах, а я – лузером. Еще и в таком важном вопросе.
– Твоим сотрудникам, – парировал я (да-да, опять военная лексика), – вообще не придется думать о ваших с Мариной ролях, если ты правильно всё подашь.
И мы разработали подачу. Денис на совещании оперирует понятиями “истинная”/”ложная” и не использует никаких военных слов. Он не признает поражение в споре, а соглашается. Аргументы Марины были не сильнее, а интереснее. Он не расстроен из-за проигрыша в споре, а очень рад стать ближе к истине. Ведь сам Сократ сказал, что в споре рождается истина, так что.
Денис был в восторге. Выход найден, выводы сделаны. Признавая правоту собеседника (не противника!), мы не проигрываем, а становимся ближе к истине. Если, конечно, у нас вообще есть такая цель. Случается так, что собеседник опроверг наши аргументы, но и его доводы не показались нам убедительными. Так и скажите, ничего страшного. Предложите изучить предмет глубже и вернуться к спору с новыми аргументами с обеих сторон.
На прощание я спросил Дениса, как он сыграл в теннисном турнире на прошедших выходных.
– Занял 4-е место, – сказал Денис.
– Лузер, – ответил я.
Возьми и спроси
Давно заметил, что у нас в культуре очень странное отношение к вопросам. Если ты спросил или, хуже того, переспросил, то значит ты – тупой. Очень интересны исторические и культурологические аспекты этого феномена, но об этом как-нибудь в другой раз. Сейчас о практических проблемах – случай из практики.
Одна начальница, назовем ее Ольга, жалуется мне на сотрудника, назовем его Сергей. Случай поистине вопиющий. Она ему сообщила о предстоящем повышении, а он не обрадовался. Вот прямо взял – и не обрадовался. “Как так? – вопрошала Ольга в полном расстройстве. – Почему?” “Возьми и спроси!”, – отвечаю. “Ну я не могу, мне как-то неудобно…”
Мы обсуждали эту ситуацию где-то с полчаса. Я искренне не понимал два момента: почему ее так оскорбила реакция Сергея и почему ей неудобно его переспросить. Она искренне не понимала, что мне непонятно. Не уверен, что я в итоге понял все правильно, но картина сложилась примерно так.
Ольга расстроилась, потому что ожидала другой реакции. Она от всей души желает успехов этому Сергею. Она приложила много усилий для его продвижения и чувствовала себя благодетельницей. Но вместо фонтана слез благодарности наткнулась на смесь равнодушия и досады. Она не могла этого понять, и причины ее расстройства ясны, хотя Сергея никто не спрашивал, нужно ли ему это повышение. Я попытался намекнуть на этот тонкий момент, но в ответ получил недоумение: “Как не нужно? А ради чего он вообще тогда работает?” Тут мы вернулись к моему изначальному предложению “возьми и спроси”.
“Ну как я его спрошу? Он же поймет, что меня это зацепило! Я не хочу показывать слабость!” – возмущалась она. Спустя пять попыток убедить, что в вопросе нет слабости, я не выдержал и ответил, что может спрашивать и слабость, но ходить теперь и возмущаться неизвестно на что – глупость, вот и выбирай. Потому что есть двадцать пять возможных объяснений реакции Сергея и сто двадцать пять возможных формулировок вопроса, которые не будут выглядеть как слабость.
Этот аргумент подействовал, хотя я неосторожно пообещал двадцать пять возможных объяснений. Ольга потребовала минимум десять. Я привел, и некоторые из них произвели сильное впечатление, потому что ей даже в голову такое не приходило. Что касается формулировки вопроса, то было решено выбрать: “Мне показалось, что новость о повышении вызвала твое беспокойство. Это так?”
Через пару дней мы снова созвонились. Ольга была крайне воодушевлена. Оказалось, что Сергея смутила не сама по себе новость, а одна-единственная фраза.. В искреннем порыве и стремлении поддержать Ольга сказала: “Не сомневаюсь, что ты справишься!” Осторожный Сергей почувствовал подвох. Он решил, что новая роль сопряжена с большими рисками, и он может не справиться. В итоге вместо радости от повышения он загрузился вопросами, с чем он может не справиться, и что будет, если он не справится.
Ситуация разрешилась благополучно. Ольга объяснила, что ничего плохого не имела в виду. Сергей, который, как оказался, тоже боялся спросить о своих сомнениях, получил ответы на ключевые вопросы о рисках. Весь их разговор уложился в 15 минут. Однако путь до этого простого “возьми и спроси” занял несколько дней и изрядно потрепал нервы Ольге, Сергею, мне и всем, кто был в курсе ситуации с обеих сторон. И единственный вопрос, который остался висеть в воздухе: “А что, так можно было?”
Ответ Soldatoff в «Удивленный работодатель»
Была такая ситуация на первой моей работе, где я трудилась «на портфолио». Зарплата у меня была смешная, но работа была первая, и я думала долгое время, что больше мне и не надо.
Собеседование произошло так: я со свежайшим дипломом, абсолютным нулем опыта и дрожью в коленях сижу в кабинете директора, далее диалог:
— Сколько вы бы хотели получать?
— Эээ. тысяч. восемь.
Никаких вопросов про навыки и опыт, сразу в бой.)) И вот я, всё ещё с дрожащими коленями, уже сажусь за СВОЙ рабочий компьютер, пытаясь сделать хоть что-то полезное.
Взяли меня «разработчиком макетов» в помощь ко второй женщине дизайнеру. Она сразу показалась мне странной, нервной, истерической. Она часто вскакивала с места, кричала и могла убежать из офиса с криками, как ее достали. Моя работа была подготавливать для нее маленькие картинки с рекламой по заказам от отдела продаж, а она заверстывала все это в телефонный справочник, который редакция и выпускала.
Шло время, я уже нормально погрузилась в работу, стала делать больше, намного лучше, но на зарплате это почти не сказывалось. А спросить про ее повышение я, конечно, стеснялась.
Старшая моя коллега же все больше выходила из себя. Каждый день у нее случались истерики со слезами, и в один раз она просто ушла и не вернулась. «Вот больная», — подумала я.
Так меня повысили до дизайнера-верстальщика, но в помощь никого не взяли, а зарплату повысили, но чисто символически. Так я отпахала с год, и начала понимать, что что-то не так: работу делаю за двоих, а получаю, как половинка. Да и нервишки начали портиться: никто ни за что отвечать не хочет, редактор (она же директор) вечно в отпуске со своими детьми-редакторами, заказчики и типография пьют кровь, открываются все новые проекты, а рук у меня все ещё две да и часов рабочих — восемь. Потребовала себе дизайнера в помощь, чтобы хоть рисовал рекламки, да не учла, каких сотрудников тут было принято брать — дешёвых. Взяли дизайнера на удаленку, но такого талантливого, что слово «реклама» без ошибок напечатать не мог, не то, чтобы прочитать тз и сделать по нему. Пришлось каждую работу проверять, практически переделывая заново. Спустя три месяца добилась увольнения «помощника», но брать нового не торопились — видите ли, очень я капризно отнеслась к предыдущему.
Тут моя чаша терпения переполнилась и я решила: пора добиваться нормальной зарплаты или уходить! Собрала свои соплячьи силы в кулак, зашла к директору и потребовала повысить оклад. до 20 тысяч рублей!))) Директор мягко улыбнулась, сказала не горячиться, и в целом послала меня в задницу с этим вопросом. Но я уже воспалилась, написала заявление на увольнение через месяц (чтобы сдать проекты, я же тупая ответственная), и поставила ультиматум.
Что тут началось! Целый месяц я наблюдала цирк, как директор пыталась уговорить меня остаться: ко мне подсылались менеджеры, задушевно рассказывающие, как они меня любят и нуждаются во мне, был подсунут бесплатный сертификат на массаж, чтобы я сбросила стресс, и главное каждый божий день директор вызывала меня к себе и объясняла мне, что мама неправильно меня воспитала, я капризничаю только из упрямства и чтобы показаться важной, а на самом деле не хочу никакой зарплаты — это семья давит на меня, чтобы я ее требовала. А уж она-то, директор, знает меня, как никто другой, и постигла все мои тайные желания.
Так месяц я билась в истерике, заработала трясущиеся руки и дергающийся глаз, а зарплату так и не повысили. Приближался день X. Вызвала меня директор к себе, посадила напротив себя и сказала:
— Ну, я рассмотрела твою просьбу и подумала. повысим тебе зарплату! Теперь ты будешь получать 19500!
И так я ушла оттуда в полной истерике, схватив сумку и уйдя с половины дня, ровно точно так же, как бывший дизайнер))