Poldet1.ru

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

Как проверить соответствие одного списка другому в Excel?

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel H&F разобрался, как предпринимателю перестать бояться формул Excel и начать считать всё, что хочется.

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel

В мире существует не так много дружественных программ, подобных Excel. Средний пользователь использует лишь 5% ее возможностей и не представляет, какие сокровища скрыты в ее недрах. H&F прочитал советы Excel-гуру и научился сравнивать прайс-листы, скрывать секретную информацию от посторонних глаз и составлять аналитические отчеты за пару кликов. (Ладно, иногда этих кликов 15.)

Импорт курса валют

/>

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

— В меню выберите вкладку «Данные».

— Нажмите на бутон «Из Интернета».

— В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

— Когда страница загрузится, на таблицах, которые Excel может импортировать, появятся черные и желтые стрелки. Щелкнув на этой стрелке, можно отметить таблицу для импорта (изображение 1).

Выберите таблицу с обменным курсом и нажмите на кнопку «Импорт».

В ячейках листа появится ставка.

Выберите опцию «Свойства диапазона» из меню правой кнопки мыши, щелкнув по любой из этих ячеек (рисунок 2).

— В появившемся окне выберите частоту обновления курсов и нажмите «OK».

Супертайный лист

Предположим, вы хотите скрыть некоторые листы в Excel от других пользователей, работающих в рабочей книге. Если сделать это классическим способом — щелкнуть правой кнопкой мыши на ярлыке листа и нажать «Скрыть» (изображение 1), то имя скрытого листа все равно будет видно кому-то другому. Чтобы сделать его полностью невидимым, выполните следующие действия:

— Вы увидите продолговатое окно слева (рисунок 2).

— В верхней части окна выберите номер листа, который вы хотите скрыть.

— В конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (изображение 3). Теперь никто, кроме вас, не будет знать об этом листе.

Читайте так же:
Как проверить правописание на всех листах или всей книге сразу в Excel?

Запрет на изменения задним числом

На рисунке 1 показана таблица с полями «Дата» и «Количество». Менеджер Вася сегодня сообщит нам, сколько моркови было продано за день. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

— Установите курсор на ячейку с датой и выберите в меню «Данные».

— Нажмите кнопку «Проверить данные». Появится таблица.

Выберите «Другой» из выпадающего списка «Тип данных».

Это записывается в столбце «Формула» как =A2=Сегодня().

— Снимите флажок «Игнорировать пустые клетки» (рисунок 2).

— Нажмите кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающее сообщение (рисунок 3).

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

Запрет на ввод дублей

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

— Выделите ячейки A1:A10, на которые будет распространяться запрет.

— Нажмите кнопку «Проверить данные» на закладке «Данные».

— На вкладке «Параметры» в раскрывающемся списке «Тип данных» выберите «Другой». (изображение 1).

— В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.

В этом же окне перейдите на вкладку «Сообщение об ошибке» и введите там сообщение, которое появится при попытке ввода дубликатов (рисунок 2).

Выборочное суммирование

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

— В ячейку G4 вносится имя клиента ANTÓN.

Читайте так же:
Как преобразовать шестнадцатеричный формат в двоичный в Excel?

— Box G5 — это название продукта Boston Crab Meat.

— Встаёте на ячейку G7, где у вас будет подсчитана сумма, и пишете для неё формулу <=СУММ((С3:С21=G4)*( B3_B21=G5)*D3:D21)>. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.

— Первый множитель (C3:C21=G4) ищет в указанном списке клиентов упоминания слова ANTON.

— Второй множитель (B3:B21=G5) делает то же самое с мясом «Бостонский краб».

— Третий множитель (D3:D21) представляет собой столбец затрат. Наконец, мы закрываем круглые скобки в конце строки.

— Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.

Сводная таблица

У вас есть таблица (рисунок 1), которая показывает, какой продукт, какому клиенту и за сколько продал тот или иной менеджер. Очень трудно выбрать отдельные данные, когда они растут. Например, вы хотите понять, сколько было продано моркови или кто из менеджеров выполнил больше всего заказов. В Excel есть сводные таблицы, которые помогут вам решить эти проблемы. Чтобы создать такую таблицу, вам нужно:

— Нажмите на кнопку «Сводная таблица» во вкладке «Вставка».

— В появившемся окне нажмите на «OK» (изображение 2).

— Появится окно, в котором можно создать новую таблицу с интересующими вас данными (рисунок 3).

Товарный чек

Чтобы подсчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а затем подсчитать сумму для этого столбца (рисунок 1). Перестаньте бояться формул; все будет элегантнее, если вы перестанете бояться.

— Выберите ячейку С7.

— Выберите зону B2:B5.

— Введите звездочку, которая в Excel является знаком умножения.

— Выберите интервал C2:C5 и закройте скобку (рисунок 2).

— Вместо Enter при написании формул в Excel нужно набирать Ctrl + Shift + Enter.

Читайте так же:
Как преобразовать yyyy-mm-dd-в стандартную дату в Excel?

Сравнение прайсов

Пользователи Excel должны использовать этот пример. Допустим, у вас есть два прайс-листа, и вы хотите сравнить их цены. На первом и втором рисунках мы показали прайс-листы от 4 и 11 мая соответственно. Некоторые товары в них не совпадают — вот как выяснить, что это за товары.

— Создайте в книге еще один лист и скопируйте на него списки товаров из первого и второго прайс-листов (изображение 3).

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

(рисунок 4) Выберите в меню «Данные» — «Фильтр» — «Дополнительный фильтр».

— В появившемся окне проверьте три вещи: a) скопировать результат в другое место; b) поместить результат в диапазон — выберите место, куда вы хотите записать результат, в примере это ячейка D4; c) установить флажок «Только уникальные записи» (изображение 5).

— Нажмите «OK» и, начиная с ячейки D4, получите список без дубликатов (см. рисунок 6).

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

— Добавьте столбцы для получения значений цен за 4 и 11 мая и столбец сравнения.

В колонку сравнения можно ввести формулу =D5-C5, которая рассчитает разницу (рисунок 7).

Осталось автоматически загрузить значение «4 мая» в столбец «11 мая». В этом случае мы будем использовать выражение: =VPR(искомое_значение; таблица; номер_столбца; интервал _просмотра).

Строка, которую мы ищем, называется «Searchable_value». Самый простой способ поиска продуктов — поиск по их названиям (рисунок 8).

— Таблица» — это матрица данных, в которой мы будем искать нужное нам значение. Он должен ссылаться на массив, содержащий цену за день 4 (изображение 9).

— Значение «Номер_столбца» — это порядковый номер столбца в диапазоне, который мы выбрали для поиска. Используя таблицу с двумя колонками, мы задали поиск. Второй содержит цену (рисунок 10).

Читайте так же:
Как ранжировать данные с несколькими ссылками в Excel?

— interval_view. Если в таблице, в которой выполняется поиск, значения расположены в порядке возрастания или убывания, поставьте TRUE; в противном случае поставьте FALSE.

— Перетащите формулу вниз и не забудьте закрепить интервалы. Для этого поставьте знак доллара перед буквой столбца и перед номером строки (это можно сделать, выделив нужный диапазон и нажав F4).

— В колонке «Итого» указана разница в цене товаров в двух прейскурантах. Если в колонке «Итого» указано #N/D, это означает, что указанный товар имеется только в одном из прайс-листов, и поэтому разница не может быть рассчитана.

Оценка инвестиций

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

— Формула в ячейке B6 рассчитывает NPV, используя финансовую функцию: =PPF($B$4;$C$3:$E$3)+B3 (Рисунок 1).

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

— В ячейке C5 результат дает формула =C3/((1+$B$4)^C2) (рисунок 2).

— В ячейке C6 тот же результат получен через формулу <=СУММ(B3:E3/((1+$B$4)^B2:E2))>(картинка 3).

Сравнение инвестиционных предложений

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

— В свободную ячейку введите формулу =npv(b3/12,A8:A12)+A7, где b3 — ставка дисконтирования, 12 — количество месяцев в году, A8:A12 — столбцы с номерами поэтапного возврата инвестиций, A7 — необходимая сумма инвестиций.

— Чистая текущая стоимость другой инвестиции рассчитывается по точно такой же формуле.

Читайте так же:
Как разрешить изменение только определенных ячеек в Excel?

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

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

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

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

Это причудливое слово для объединения данных из двух или более ячеек в одну. Это можно сделать с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если ячейка A1 содержит имя «Иван», а ячейка B1 — фамилию «Петров», их можно объединить с помощью формулы =A1&»»&B1. Результатом будет «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединенными данными был пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные выше формулы можно применить и к доскам объявлений Google.

Обучение Excel — это гораздо больше, чем просто эта статья. Чтобы извлечь пользу из программы в работе, мы рекомендуем пройти курс Microsoft Excel, который преподается профессионалом.

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