Excel에서 대량 제품 데이터를 편집하는 10 수식

Excel 스프레드시트를 사용하여 제품 데이터를 수정한 적이 있는 사람, 특히 전자 상거래 관리자는 제품을 대량으로 편집하는 경우 작업이 얼마나 빨리 압도적일 수 있는지 알고 있습니다. 많은 양의 제품으로 어디서부터 시작해야 할지 막막합니다. 셀 값을 놓치거나 잘못된 내용을 입력하는 것은 너무 쉽습니다.

그러나 각 문제에 대한 올바른 공식을 사용하면 Excel 스프레드시트에서 훨씬 쉽게 관리하고 편집할 수 있습니다.

이 블로그에서는 Excel에서 대량 제품 데이터를 편집하는 데 사용할 수 있는 몇 가지 마법의 공식에 대해 설명합니다.

그럼 시작하겠습니다.

공식 1: 제품 이름 및 설명의 선행 0 제거

Excel에서 제품 번호에 선행 0을 사용하는 경우 제거해야 합니다. 불행히도 Excel은 제품의 이름과 설명에서 선행 0을 제거하는 쉬운 방법을 제공하지 않습니다.

예를 들어, 제품 목록이 00123450123456이고 처음 두 개의 0을 제거하고 123450123456으로 만들고자 합니다. 그러면 어떻게 해야 할까요?

셀에서 기존 서식을 제거하기만 하면 선행 0을 제거할 수 있습니다.

첫 번째 단계: 0으로 시작하는 정수가 포함된 셀을 선택합니다.

두 번째 단계: '홈' 탭을 클릭합니다.

세 번째 단계: 숫자 그룹에서 숫자 형식 드롭다운을 클릭합니다.

네 번째 단계: '일반' 클릭

공식 2: 단가(총 가격/수량) 및 단가(총 비용/수량) 계산

당신은 사업을 운영하고 있으며 다양한 가격, 크기 및 수량의 제품을 보유하고 있습니다. 이 시나리오에서 제품의 총 비용을 계산하려고 합니다.

Excel에서는 각 제품의 단가와 단가를 계산하는 옵션을 제공합니다. 단가는 측정 단위당 총 비용입니다. 이는 총 비용을 해당 제품의 수량으로 나누어 계산할 수 있습니다. 단가를 계산합니다. 단가는 단가에 해당 제품의 수량을 곱한 값입니다.

단가 공식 = 총 비용 / 총 생산 또는 구매 단위

단가 공식 = 총 가격/생산 단위 수.

소매업체 및 공급업체 에 대한 가격 설정 방법을 확인하십시오.

수식 3: 쉼표, 콜론, 슬래시, 대시 또는 기타 구분 기호로 셀 값 분할

Excel에서 데이터로 작업하려고 할 때 데이터는 종종 하나의 열에 저장되지만 여러 열에서 사용할 수 있도록 하려고 합니다. 예를 들어 A열에 고객 이름 목록이 저장되어 있지만 B열과 C열에는 전화번호가 필요합니다.

또는 Excel 워크시트에 두 개의 열로 나누고 싶은 일련의 단어와 숫자가 있다고 가정해 보겠습니다. 목표는 문자열의 전반부를 대시로 구분하여 별도의 셀로 나누는 것입니다.

예를 들어 다음과 같은 단어 및 숫자 문자열이 있는 경우: Shoes-123. '신발'과 '123'이라는 두 개의 열이 표시됩니다.

구문: =SPLIT(A2,”-”)

그러나 문자열이 여러 개의 대시(예: Red-long-frog)로 연결될 때 문제가 발생합니다. 이 경우 단순 분할 기능을 사용하면 작동하지 않습니다. 대신 아래 나열된 오른쪽, 왼쪽 또는 중간 분할 기능을 사용해야 합니다.

=왼쪽(B1,2)

또는

=오른쪽(B2,4)

또는
=중간(B2,5,2)

공식 4: VLOOKUP 함수를 사용하여 값 찾기

하나의 속성으로 모든 속성을 조회하려는 경우가 있습니까? 예를 들어 나는 누군가의 이메일 주소, 집 주소, 나이, 성별을 원합니다. 하나의 VLOOKUP 공식으로 할 수 있습니다. VLOOKUP 기능을 사용하여 이름을 기반으로 고객의 전화번호를 검색할 수 있습니다. 빠르게 액세스해야 하는 정보가 많을 때 유용합니다.

VLOOKUP은 'Vertical Lookup'의 약자입니다. 같은 행의 다른 열에서 값을 반환하기 위해 Excel에서 열(소위 테이블 배열')에서 특정 값을 찾도록 지시하는 함수입니다.

VLOOKUP 함수는 네 부분으로 구성됩니다.

• 당신이 찾고 있는 가치;
• 찾고 있는 값과 찾고 있는 범위
• 선택한 범위 내에서 반환 값을 전달하는 열의 번호입니다.
• 찾고 있는 값과 정확히 일치하려면 0 또는 FALSE를 사용하십시오. 가까운 일치의 경우 1 또는 TRUE를 사용합니다.

구문: VLOOKUP([값], [범위], [열 번호], [거짓 또는 참])

수식 5: 수식을 사용하여 빈 값 필터링

가장 시간이 많이 걸리는 작업 중 하나는 전자 상거래 상점에서 제품 데이터를 정리하는 것입니다. 때로는 제대로 수행되지 않으면 판매 손실로 이어질 수도 있습니다.

이 섹션에서는 수식을 사용하여 대량 제품 데이터에서 빈 값을 필터링하는 방법을 배웁니다.

"수식" 열은 값의 존재를 확인하는 간단한 수식을 계산합니다. 값이 있으면 반환합니다. 그렇지 않은 경우 공백을 반환합니다. 이렇게 하면 수식 열을 빈 값이 0으로 변환되는 필터로 사용할 수 있습니다.

"수식" 열은 값의 존재를 확인하는 간단한 수식을 계산합니다. 값이 있으면 반환합니다. 그렇지 않은 경우 0을 반환합니다.

구문: =FILTER(데이터,(rng1<>””)*(rng2<>””)*(rng3<>””))

수식 6: 수식을 사용하여 관심 있는 행만 선택

대량 제품 데이터에 대해 Excel에서 관심 있는 행만 선택하는 것은 가장 일반적인 Excel 작업 중 하나입니다. 수식을 사용하여 Excel에서 관심 있는 행만 선택하는 방법을 보여 드리겠습니다.

제품 목록이 있고 재고가 있는 각 제품의 수를 알아야 한다고 가정해 보겠습니다. 각 제품에 대해 새 열을 추가하고 싶지 않습니다. 대신 공식을 사용하여 재고가 없는 제품을 제외한 모든 제품을 선택할 수 있습니다.

마우스 또는 Ctrl + Shift + 아래쪽 화살표 키 조합을 사용하여 모든 제품 주위에 셀을 그리는 것으로 시작합니다.

그런 다음 IF 함수를 적용할 수 있습니다.

=IF(AND(A2=”점수”,A2>=90),1,””)

– 여기서 A2는 점수 목록을 포함하는 열이고 =AND(A2=”Score”,A2>=90) – 셀 값을 확인하고 'Score' 패턴이 셀에 나타나면 true 를 반환하고, 셀 값이 90 이상입니다.

수식 7: 수식을 사용하여 날짜를 텍스트 또는 숫자로 변경

대량 데이터 입력과 관련된 모든 종류의 작업에 Excel을 사용하는 경우 수식을 사용하여 날짜를 텍스트나 숫자로 변경하는 방법을 알아야 합니다. 이것이 필요한 가장 일반적인 시나리오는 CSV 파일에서 제품 데이터를 가져올 때입니다.

예를 들어 제품 데이터를 가져올 때 날짜가 포함된 일부 셀이 있는 경우가 많습니다. 그러면 Excel에서 "이 유형의 값을 변환할 수 없습니다"와 같은 오류가 표시됩니다. 이를 방지하려면 Excel에서 해당 CSV 파일을 가져오는 데 문제가 없도록 다음 수식 중 하나를 사용하여 날짜를 텍스트 또는 숫자로 변경하십시오.

구문: =VALUE(텍스트)

구문: =DATEVALUE(날짜_텍스트)

공식 8: 공식을 사용하여 대량 제품 데이터에서 중복 찾기

보고와 관련하여 문제가 될 수 있는 중복 레코드가 비즈니스 데이터에 포함되는 것은 드문 일이 아닙니다. Excel 시트에 중복 레코드가 있는지 확인하려면 수식 도구를 사용할 수 있습니다. Microsoft Excel의 필터 기능을 사용하여 중복을 제거하는 방법도 배울 수 있습니다.

그러나 수식을 사용하면 대량 제품 데이터에서 중복을 찾는 것이 항상 더 쉽습니다. Excel 스프레드시트에서 COUNTIF 수식을 사용하여 'TRUE' 또는 'FALSE' 응답을 얻어 중복 값을 감지할 수 있습니다. 여기서:

True = 중복 값

False = 고유 값

수식의 'A'를 중복을 찾고 있는 셀 열의 문자로 변경하기만 하면 됩니다.

구문: =COUNTIF(A:A,A1)>1

공식 9: 공식을 사용하여 특정 속성을 가진 제품 수 계산

특정 상품 속성을 가진 상품의 개수를 모아서 표시하고 싶습니다. 예를 들어 하나의 속성이 "할인 있음"이고 이 속성이 있는 제품 수를 출력하려고 합니다.

이 Excel 수식은 특정 속성과 일치하는 총 제품 수를 계산하는 데 도움이 됩니다.

특정 속성과 일치하는 총 제품 수를 계산하려면 Excel에서 다음 수식을 사용할 수 있습니다.

예를 들어, 가격이 $10에서 $20 사이인 파란색 제품의 총 수를 알고 싶다고 가정합니다. 이 경우 테이블은 다음과 같습니다.

'Colors' 항목을 선택하고 적용

=COUNTIF(B2:B8,"파란색",C2:C8,")

공식 10: 공식을 사용하여 제품 이름을 제품 URL로 변환

Excel에 제품 목록이 있습니다. 이제 각 제품 이름을 제품 URL로 만들고 싶습니다. 가능합니까? 예, 그렇습니다! 이것은 INDEX 및 MATCH 기능을 사용하여 달성할 수 있는 동적 셀 참조/참조로 알려져 있습니다.

Excel에서는 간단한 수식을 사용하여 제품 이름에서 URL을 생성할 수 있습니다. 일반적으로 이 접근 방식은 제품 이름을 링크 텍스트로 사용하여 제품에 대한 링크를 생성하고 수동으로 생성된 링크를 추적하고 싶지 않을 때 유용합니다.

이것은 두 가지 공식이 필요한 2단계 프로세스일 뿐입니다.

1단계: 이름에서 모든 대문자를 제거하고 모든 공백을 "-"로 대체

구문: =LOWER(SUBSTITUTE(B2," ","-"))

2단계: 제품 이름에서 모든 특수 문자 제거

구문: =REGEXREPLACE(B2,”[^A-Za-z1-9-]+”,””)

PIM을 사용하여 제품 데이터를 쉽게 대량 편집

다음과 같은 상황을 겪은 적이 있습니까?

1. Excel 시트에 많은 제품 데이터가 있습니다.

2. 그러나 귀하의 데이터는 완전하지 않으며 다른 출처의 일부 정보로 업데이트해야 합니다.

3. 많은 데이터를 하나씩 수정해야 하므로 시간이 오래 걸립니다. 또는 일부 제품을 잊어버리고 다시 찾아 누락된 정보를 모두 입력해야 합니다.

4. 상품 데이터를 업데이트 할 때 VBA 코드를 이용하여 엑셀 파일을 업데이트 해야 하는데 빨리 하기가 어렵습니다.

Shopify에서 Excel 스프레드시트(예: CSV 파일)에서 제품을 추가할 때 올바른 형식이 아닌 경우 수동으로 추가한 제품 데이터는 번역 시 손실됩니다. 예를 들어, 제품을 추가하고 잘못된 필드 이름이나 구조를 사용하는 경우 제품 정보가 손실되고 다시 시작해야 합니다.

이 짜증나고 위협적인 문제를 한 번에 제거할 수 있다고 말하면 어떻게 될까요? 위에 나열된 상황에 직면할 필요가 전혀 없을 것이라고 말하면 어떻게 됩니까? 글쎄, 방법이 있습니다.

Apimio는 전자 상거래 관리자가 제품을 대량으로 편집 및 관리하고 형식 및 데이터 손실 문제를 해결하는 데 도움이 되는 제품 정보 관리 솔루션입니다.

사실, 스프레드시트를 PIM용으로 남겨두는 데에는 여러 가지 이유가 있습니다. 운 좋게도 우리는 이미 그것을 다루었습니다. 여기에서 이에 대한 자세한 내용을 읽으십시오.

PIM용으로 스프레드시트를 남겨두는 7가지 이유에 대한 블로그를 확인하십시오.

그리고 왜 없을까요? 결국 PIM이 스프레드시트를 대체합니다. 모든 정보 관리를 쉽게 하기 위해 존재합니다.

결론

현재 Shopify 비즈니스를 담당하고 있거나 전자 상거래 회사에서 전자 상거래 관리자로 일하고 있습니까? 아마도 당신은 회사의 방향에 영향을 미치기 위해 제품 정보 관리 시스템을 도입하는 것을 고려했을 것입니다. 전자 상거래 세계에 쉽게 접근할 수 있는 방법으로 Shopify로 전환하는 것을 고려 중일 수 있습니다.

어떤 경우인지는 중요하지 않습니다. 파도가 거칠어지면 PIM은 제어를 유지하기 위한 안정적이고 매우 유용한 도구가 될 수 있습니다. PIM-Shopify 통합의 강력한 초능력을 최대한 활용하십시오.

이 게시물이 엑셀에서 대량 데이터를 편집하는 몇 가지 빠르고 쉬운 방법을 보여주는 데 도움이 되었기를 바랍니다. 자신만의 팁이 있으면 아래 댓글 섹션에 남겨주세요.