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

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

Как сделать прайс в Excel с картинками

Есть два способа.

Прайс с постоянными картинками

Допустим, у нас есть небольшой подготовленный прайс.

В графе КАК ВЫГЛЯДИТ будем размещать картинки. На вкладке ВСТАВКА кликаем РИСУНОК. Появляется окно, предлагающее выбрать изображение с компьютера. Выбираем его. Появляется огромная картинка, которую можно уменьшить, потянув за уголок, а также переместить.

Делаем так, чтобы картинка аккуратно вписалась в нужную ячейку. Хотя, правильно будет говорить НА ячейку, потому что фактически, если кликнуть на С3, она будет пустой. Аналогично подгружаем остальные картинки. Получаем красивый прайс.

Прайс с появляющимися картинками

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

Стоя на ячейке с названием первого товара, правой кнопкой вызываем контекстное меню и выбираем ВСТАВИТЬ ПРИМЕЧАНИЕ. Появляется такое окошко.

Кликаем правой кнопкой по рамке примечание (именно по рамке, а не внутри окна), выбираем ФОРМАТ ПРИМЕЧАНИЯ – ЦВЕТА И ЛИНИИ. Открываем графу ЦВЕТ и выбираем самый нижний вариант: СПОСОБЫ ЗАЛИВКИ.

Появится еще одно окно, в котором во вкладке РИСУНОК надо выбрать рисунок из файлов компьютера.

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

Прайс в зависимости от курса валют

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

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

На вкладке ДАННЫЕ выбираем пункт ИЗ ИНТЕРНЕТА (в других версиях Excel может быть написано ИЗ ВЕБА).

В появившемся браузере вбиваем в поисковой строке www.cbr.ru/currency_base/dynamics.aspx (сайт Центробанка России). Открывается сайт, на котором нам нужно найти ссылку КУРСЫ ВАЛЮТ, кликнуть по ней. Откроется окно, в котором надо выбрать ДИНАМИКА ОФИЦИАЛЬНОГО КУРСА ЗАДАННОЙ ВАЛЮТЫ.

Выбираем нужную валюту и интервал, за который нам нужны данные. Т.к. нам важны только актуальные данные, можно поставить, например, с 27.02.2016 по 27.02.2016. Но для наглядности захватим одну неделю. Нажимаем ПОЛУЧИТЬ ДАННЫЕ. Появляется таблица, которую нужно выгрузить и синхронизировать. Чтобы это сделать, сначала надо эту таблицу выделить при помощи значка в виде черной стрелки на желтом фоне. Находим ту стрелочку, которая выделит нам нужную таблицу (в некоторых версиях Excel выделяется сразу вся страница), а затем щелкаем на СОХРАНИТЬ ЗАПРОС (в верхнем правом углу).

Сохраняем запрос в компьютере под именем, например, CBR с расширением .IQY.

Теперь заходим в эту папку (C:UsersMyUserNameAppDataRoamingMicrosoftЗапросы) и открываем запрос при помощи блокнота. Откроется код, в котором нам важно заменить конечную дату на плавающую (динамическую). Даем ей имя поля, в котором в будущем у нас будет записываться дата. Пусть оно так и называется – ДАТА.

Сохраняем изменения. Закрываем файл. Возвращаемся к Excel. На вкладке ДАННЫЕ выбираем СУЩЕСТВУЮЩИЕ ПОДКЛЮЧЕНИЯ. В появившемся окне нажимаем НАЙТИ ДРУГИЕ и выбираем запрос, который мы назвали cbr.iqy. Перед тем, как подключиться, программа спросит, куда помещать запрос (можно выбрать любую свободную ячейку) и попросит указать значение параметра, который мы изменили в блокноте (в нашем случае это ДАТА).

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

Нажимаем ОК, и на листе появляется информация с сайта cbr.ru. Удаляем все лишнее, оставляя только табличку с курсами евро. А в ячейку D1, где у нас должна быть динамическая дата, вбиваем формулу.

Т.к. в формуле используется функция СЕГОДНЯ, Excel будет автоматически обновлять запрос с сайта Центробанка каждый раз при смене даты. Т.е. ежедневно в таблице будут появляться новые данные. Т.к. 28.02.2016 – это воскресенье, новых данных у Центробанка нет, поэтому конечным значением является курс евро на 27.02.2016.

Как пользоваться прайсом

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

Теперь все готово для того чтобы, наконец, узнать цену товара в рублях. В ячейку D4 вводим формулу, которая умножает цену в евро на курс. Дополнительно используем команду ОКРУГЛ, чтобы округлить получившуюся стоимость до рублей (без копеек). Протягиваем на остальные товары.

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

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

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

Шаг 1. Создаем каталог с фото и даем ему имя

Создаем на Листе 1 каталог с наименованиями и фотографиями товаров, состоящий из двух столбцов (Модель и Фото):

Создаем каталог с фото и даем ему имя

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

Диспетчер имен (Name Manager) на вкладке

Формулы (Formulas). Создаем новый диапазон, вводим имя (например, Фотоальбом) и в качестве адреса указываем формулу:

=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1)
=OFFSET(Лист1!$A$1;1;0;COUNTA(Лист1!$A:$A)-1;1)

Создаем новый диапазон и в качестве адреса указываем формулу

Эта формула определяет последнюю занятую ячейку в столбце А и выдает на выходе диапазон с А2 до этой найденной ячейки. Такая относительно сложная конструкция нужна, чтобы впоследствии дописывать новые модели к нашему списку и не думать об исправлении диапазона. Если дописывать точно ничего не придется, то можете вместо ввода этой страшноватой формулы просто указать =A2:A5.

Шаг 2. Выпадающий список для выбора модели

Перейдем на Лист 2 и создадим там ячейку с выпадающим списком для выбора пользователем модели телефона (пусть это будет A1). Выделяем ячейку и идем на вкладку Данные – Проверка данных (Data – Data Validation). Далее в поле Тип данных (Allow) выбираем Список (List), а в качестве Источника (Source) указываем наш Фотоальбом (не забудьте перед ним добавить знак равенства).

Создадим ячейку с выпадающим списком для выбора

Кроме того, этой ячейке удобно дать имя – вкладка Формулы – Диспетчер имен (Formulas – Name Manager), создаем диапазон и далее вводим имя (например, Выбор) и ОК.

Шаг 3. Копируем фотографию

Перенесем первую фотографию из фотоальбома к выпадающему списку. Надо выделить ячейку с первой фотографией (не сам рисунок, а ячейку!) и развернуть выпадающий список под кнопкой Копировать (Copy) на Главной (Home) вкладке:

Перенесем первую фотографию из фотоальбома к выпадающему списку

Microsoft Excel 2007 больше ничего не спросит, а в Excel 2010-2016 появится еще одно дополнительное окно с выбором типа создаваемого изображения:

Нужно выбрать варианты копирования

В нем нужно выбрать варианты «как на экране» и «растровый». Копируем, переходим на Лист 2 к выпадающему списку и в любую пустую ячейку недалеко от него вставляем наш мини-скриншот ячейки с фотографией (на вкладке Главная – Правка – Вставить или обычное CTRL+V).

Шаг 4. Создаем динамическую ссылку на выбранную фотографию

Теперь необходимо сделать ссылку, которая будет указывать на ячейку с выбранной фотографией. Снова открываем Диспетчер имен (Name Manager) на вкладке Формулы (Formulas) и создаем еще один именованный диапазон:

Теперь необходимо сделать ссылку

В английском варианте: =OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1).

Технически функция ПОИСКПОЗ (MATCH) находит ячейку с нужной моделью в каталоге по названию, а функция СМЕЩ (OFFSET) затем выдает ссылку на соседнюю справа от найденного названия ячейку, т.е. ячейку с фотографией товара.

Шаг 5. Привязываем фотографию к ссылке

Осталось выделить скопированную фотографию на Листе 2 и вписать в строку формул =Фото и нажать Enter.

Привязываем фотографию к ссылке

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

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

В принципе гиперссылки можно создавать в документах Word, PDF. Но в первом случае для перехода по созданной гиперссылке требуется одновременно нажимать клавишу Ctrl, а во втором — сложно в последующем редактировать созданный документ. Всех этих недостатков лишено применение гиперссылок с использованием таблиц, созданных в программе Excel. По большому счету этот же способ применим в программах Open Office, Google документах и в подобных им.

Итак, к делу. Создаем новый документ в программе Excel, форматируем под свои потребности ширину столбцов и набираем необходимый текст. Для того, чтобы сделать его гиперссылкой, нужно зайти на вкладку «Вставка», выбрать в ленте «Гиперссылка» (нажать левой кнопкой мыши) и в открывшемся окне вставить необходимую ссылку.

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

Ну и для самых «ленивых» существует третий способ. Устанавливаем курсор в ячейку с редактируемой надписью и нажимаем сочетание клавиш «Ctrl» + «К». При этом сразу откроется окно вставки-редактирования ссылок. Это самый быстрый и удобный способ!

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

Наглядно все шаманство процесса создания гиперссылок можно посмотреть в этом небольшом видео. Кстати в нем, есть еще кое-что «вкусненькое» по гиперссылкам, о чем я не написал в статье. Так что советую посмотреть!

Убедились? Думаю, что и это далеко не все. В данном вопросе только фантазия и замысел могут ограничить Ваши возможности. Мне действительно интересно, кто на практике применяет Excel для повседневных задач. Знаю, что его плотно используют Яндекс-директологи. Вообще это очень интересная тема. Хочу еще написать несколько полезных советов по работе с  Excel в разрезе ее применения именно для блоггинга.

Жду Ваших комментариев к статье. Если что-то осталось непонятным, не стесняйтесь, спрашивайте. Собственно, эта статья так и была написана – под влиянием заданного вопроса по теме гиперссылок.

С уважением, Сергей Почечуев

ПОНРАВИЛАСЬ СТАТЬЯ? РАССКАЖИ ДРУЗЬЯМ!